✏️ Explanatory Question
Level: Basic to Intermediate — Operators are the building blocks of every WHERE clause and expression.
MySQL operators are symbols or keywords used to perform operations on values in queries. They fall into five main categories.
+, -, *, /, % (modulo) — for math operations.=, != / <>, >, <, >=, <=, <=> — compare two values.AND, OR, NOT, XOR — combine multiple conditions.BETWEEN, IN, LIKE, IS NULL, EXISTS — pattern & range matching.&, |, ^, ~, <<, >> — operate on bits.<=> is the NULL-safe equality operator. Unlike =, it returns 1 (true) when comparing two NULLs, instead of NULL.
| Category | Examples | Use Case |
|---|---|---|
| Arithmetic | + - * / % | Calculations |
| Comparison | = != > < >= <= | Filtering rows |
| Logical | AND, OR, NOT | Combine conditions |
| Range / Set | BETWEEN, IN, LIKE | Pattern & range match |
| Bitwise | & | ^ ~ | Bit-level operations |
-- Arithmetic
SELECT salary * 12 AS annual_salary FROM employees;
-- Comparison + Logical
SELECT * FROM employees
WHERE salary >= 30000 AND dept_id = 10;
-- Range / Set operators
SELECT * FROM employees WHERE salary BETWEEN 20000 AND 50000;
SELECT * FROM employees WHERE dept_id IN (10, 20, 30);
SELECT * FROM employees WHERE name LIKE 'R%'; -- starts with R
-- NULL-safe comparison
SELECT NULL <=> NULL; -- returns 1 (true)
SELECT NULL = NULL; -- returns NULL<=> and the difference between BETWEEN (inclusive) and range comparisons — these details impress interviewers.