DEV Community

Angelika Jolly
Angelika Jolly

Posted on

First Steps in SQL Operators: A Beginner's Guide

Sure, let's dive into the basics of SQL operators. SQL (Structured Query Language) is used for managing and manipulating databases. Here are the fundamental types of SQL operators and how they are used:

  1. Arithmetic Operators

Arithmetic operators perform mathematical operations. Common ones include:

  • + : Addition
  • - : Subtraction
  • `` : Multiplication
  • / : Division
  • % : Modulus

Example:
`sql
SELECT 10 + 5; -- Returns 15
SELECT 10 - 5; -- Returns 5
SELECT 10 5; -- Returns 50
SELECT 10 / 5; -- Returns 2
SELECT 10 % 3; -- Returns 1
`

  1. Comparison Operators

Comparison operators compare two values and return a boolean result (true or false).

  • = : Equal to
  • <> or != : Not equal to
  • > : Greater than
  • < : Less than
  • >= : Greater than or equal to
  • <= : Less than or equal to

Example:
`sql
SELECT 10 = 5; -- Returns false
SELECT 10 <> 5; -- Returns true
SELECT 10 > 5; -- Returns true
SELECT 10 < 5; -- Returns false
SELECT 10 >= 5; -- Returns true
SELECT 10 <= 5; -- Returns false
`

  1. Logical Operators

Logical operators are used to combine multiple conditions.

  • AND : All conditions must be true
  • OR : At least one condition must be true
  • NOT : Reverses the truth value

Example:
`sql
SELECT 10 > 5 AND 10 < 15; -- Returns true
SELECT 10 > 5 OR 10 < 5; -- Returns true
SELECT NOT 10 > 5; -- Returns false
`

  1. String Operators

String operators are used to manipulate string values.

  • || or + : Concatenate two strings (varies by SQL dialect)
  • LIKE : Pattern matching using wildcards
  • % : Zero or more characters (used with LIKE)
  • _ : A single character (used with LIKE)

Example:
`sql
SELECT 'Hello' || ' World'; -- Returns 'Hello World' (PostgreSQL, SQLite)
SELECT 'Hello' + ' World'; -- Returns 'Hello World' (SQL Server)

SELECT 'Hello' LIKE 'H%'; -- Returns true
SELECT 'Hello' LIKE '_e%'; -- Returns true
`

  1. Bitwise Operators

Bitwise operators perform operations on bits and are used in some advanced queries.

  • & : Bitwise AND
  • | : Bitwise OR
  • ^ : Bitwise XOR
  • ~ : Bitwise NOT
  • << : Bitwise left shift
  • >> : Bitwise right shift

Example:
`sql
SELECT 5 & 3; -- Returns 1 (0101 & 0011 = 0001)
SELECT 5 | 3; -- Returns 7 (0101 | 0011 = 0111)
SELECT 5 ^ 3; -- Returns 6 (0101 ^ 0011 = 0110)
SELECT ~5; -- Returns -6 (inverts the bits)
SELECT 5 << 1; -- Returns 10 (0101 << 1 = 1010)
SELECT 5 >> 1; -- Returns 2 (0101 >> 1 = 0010)
`

  1. Other Operators
  • IN : Checks if a value is within a set of values
  • BETWEEN : Checks if a value is within a range
  • IS NULL : Checks if a value is null
  • IS NOT NULL : Checks if a value is not null

Example:
`sql
SELECT 10 IN (5, 10, 15); -- Returns true
SELECT 10 BETWEEN 5 AND 15; -- Returns true
SELECT NULL IS NULL; -- Returns true
SELECT 10 IS NOT NULL; -- Returns true
`

Practical Example with a Database Table

Let's consider a simple table employees:

id name age salary
1 Alice 30 5000
2 Bob 35 6000
3 Charlie 40 7000

Example Queries:

  • Select employees with salary greater than 5000:
    `sql
    SELECT FROM employees WHERE salary > 5000;
    `

  • Select employees whose name starts with 'A':
    `sql
    SELECT FROM employees WHERE name LIKE 'A%';
    `

  • Select employees with age between 30 and 40:
    `sql
    SELECT FROM employees WHERE age BETWEEN 30 AND 40;
    `

These are the basics of SQL operators. They form the foundation for writing more complex SQL queries as you progress.

https://www.youtube.com/watch?v=3tCym9ZkEdk

Image of Timescale

🚀 pgai Vectorizer: SQLAlchemy and LiteLLM Make Vector Search Simple

We built pgai Vectorizer to simplify embedding management for AI applications—without needing a separate database or complex infrastructure. Since launch, developers have created over 3,000 vectorizers on Timescale Cloud, with many more self-hosted.

Read more →

Top comments (0)

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more

đź‘‹ Kindness is contagious

Immerse yourself in a wealth of knowledge with this piece, supported by the inclusive DEV Community—every developer, no matter where they are in their journey, is invited to contribute to our collective wisdom.

A simple “thank you” goes a long way—express your gratitude below in the comments!

Gathering insights enriches our journey on DEV and fortifies our community ties. Did you find this article valuable? Taking a moment to thank the author can have a significant impact.

Okay