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.

Operator Meaning
= Equal
<> or != Not equal
> Greater than
< Less than
>= Greater or equal
<= Less or equal

Example:


🔹 2. Logical Operators

Combine multiple conditions.

Operator Meaning
AND All conditions must be true
OR At least one condition is true
NOT Negates a condition

Example:


🔹 3. Arithmetic Operators

Operator Meaning
+ 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.

Operator Meaning
UNION Combine and remove duplicates
UNION ALL Combine and keep duplicates
INTERSECT Return common rows (not MySQL)
EXCEPT / MINUS Return 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: +

SELECT first_name || ' ' || last_name AS fullname
FROM users;

🔥 Bonus: JSON Operators (PostgreSQL)

SELECT data->>'name' AS name
FROM users_json;

🧠 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...