SQL Operators

SQL Tutorial

Here is a clear, complete, and practical guide to the most important SQL Operators, grouped by category with examples for all major SQL databases.

What Are SQL Operators?

SQL operators are symbols or keywords used to perform:

  • Comparisons

  • Arithmetic calculations

  • Logical evaluations

  • Pattern matching

  • Set operations

They are essential for WHERE, HAVING, SELECT, CASE, and JOIN conditions.

 1. Comparison Operators

Used to compare values.

OperatorMeaning
=Equal
<> or !=Not equal
>Greater than
<Less than
>=Greater or equal
<=Less or equal

Example:

2. Logical Operators

Combine multiple conditions.

OperatorMeaning
ANDAll conditions must be true
ORAt least one condition is true
NOTNegates a condition

Example:

 3. Arithmetic Operators

OperatorMeaning
+Addition
-Subtraction
*Multiplication
/Division
%Modulus (SQL Server, PostgreSQL)

Example:

 4. Pattern Matching Operators

LIKE

ILIKE (PostgreSQL)

Case-insensitive LIKE:

 5. NULL Operators

IS NULL / IS NOT NULL

NULL-handling functions:

  • COALESCE()

  • ISNULL() (SQL Server)

  • IFNULL() (MySQL)

  • NVL() (Oracle)

 6. Set Operators

Combine result sets.

OperatorMeaning
UNIONCombine and remove duplicates
UNION ALLCombine and keep duplicates
INTERSECTReturn common rows (not MySQL)
EXCEPT / MINUSReturn rows from first not in second

Example:

 7. Membership Operators

IN

NOT IN

(Be careful with NULL values.)

 8. Range Operators

BETWEEN

 9. Existence Operator

EXISTS / NOT EXISTS

10. Comparison With Subqueries

ANY / SOME

True if at least one value matches.

ALL

True if all values satisfy the condition.

SELECT * FROM products
WHERE price > ALL (SELECT price FROM competitor_products);

 Bonus: String Operators

Concatenation

  • PostgreSQL: ||

  • MySQL: CONCAT(a, b)

  • SQL Server: +

 Bonus: JSON Operators (PostgreSQL)

 Best Practices

  •  Use parentheses to avoid logical ambiguity
  •  Prefer IN over long chains of OR
  •  Always handle NULL explicitly
  •  Use appropriate operators for the database (e.g., ILIKE in PostgreSQL)
  •  For set operations, ensure column counts and types match

You may also like...