SQL.What are the different SQL operators?

As a senior backend engineer, you’re expected to classify operators, not just list random ones.

Short interview answer:
SQL operators are used in expressions to compare values, filter rows, perform calculations, and combine conditions. They include arithmetic, comparison, logical, set, and special operators.

1️⃣ Arithmetic operators

Used in numeric expressions.

| Operator | Meaning                      |
| -------- | ---------------------------- |
| `+`      | Addition                     |
| `-`      | Subtraction                  |
| `*`      | Multiplication               |
| `/`      | Division                     |
| `%`      | Modulo (DB-specific support) |

SELECT price * quantity AS total
FROM orders;

2️⃣ Comparison operators

Used mainly in WHERE, JOIN ON, HAVING.

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

SELECT *
FROM users
WHERE age >= 18;

3️⃣ Logical operators

Combine conditions.

SELECT *
FROM users
WHERE active = true AND deleted = false;

SELECT *
FROM users
WHERE active = true AND deleted = false;

⚠️ Operator precedence matters (NOT > AND > OR).

4️⃣ NULL-related operators (very important in interviews)

4️⃣ NULL-related operators (very important in interviews)

SELECT *
FROM users
WHERE deleted_at IS NULL;

❌ = NULL never works — classic trap.

5️⃣ Set / range operators

IN / NOT IN

SELECT *
FROM orders
WHERE status IN ('NEW', 'PAID');

⚠️ NOT IN + NULL = unexpected empty result.

BETWEEN

WHERE created_at BETWEEN '2025-01-01' AND '2025-01-31';

Inclusive on both sides.

LIKE / ILIKE (Postgres)

WHERE email LIKE '%@gmail.com';

% – any sequence

_ – single character

6️⃣ Aggregate operators

Used with aggregation functions.

Function	Meaning
COUNT	Row count
SUM	Sum
AVG	Average
MIN / MAX	Extremes

SELECT COUNT(*) 
FROM orders;

Used together with GROUP BY, filtered via HAVING.

7️⃣ Set operators (combine result sets)

Operator	Meaning
UNION	Merge, remove duplicates
UNION ALL	Merge, keep duplicates
INTERSECT	Common rows
EXCEPT	Difference

SELECT id FROM a
UNION
SELECT id FROM b;

8️⃣ Existence & subquery operators

EXISTS

SELECT *
FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);

Efficient for correlated subqueries.

ANY / ALL

WHERE price > ALL (SELECT price FROM discounts);

Rare but interview-relevant.

9️⃣ Bitwise operators (DB-specific)

Common in Postgres, SQL Server.

Operator	Meaning
&	AND
`	`
^	XOR
<<	Shift left
>>	Shift right

10️⃣ Special operators worth mentioning (Postgres)

• || – string concatenation
• :: – type cast
• -> / ->> – JSON operators
• @> – contains (arrays / JSONB)

WHERE data->>'status' = 'ACTIVE';

How to answer in an interview (clean version)

SQL operators can be grouped into arithmetic, comparison, logical, NULL-handling, set, aggregate, and existence operators.
They are mainly used in WHERE, JOIN, HAVING, and SELECT expressions to filter, compare, and combine data.

---

Typical interview pitfalls ⚠️

• Using = NULL
• Forgetting NULL behavior in NOT IN
• Ignoring operator precedence
• Confusing WHERE vs HAVING