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

Top comments (0)