WHERE: SELECT * FROM orders WHERE amount > 500; : Filter rows based on a condition
AND / OR / NOT: SELECT * FROM users WHERE active=1 AND NOT country='IN'; : Combine multiple
filter conditions logically
Comparison Operators: SELECT * FROM items WHERE qty <> 0; : Compare column values to constants or other columns
BETWEEN: WHERE order_date BETWEEN '2025-01-01' AND '2025-01-31'; : Select values inside an inclusive range
NOT BETWEEN: WHERE age NOT BETWEEN 18 AND 30; : Exclude values that fall within a range
IN: WHERE status IN ('NEW','PENDING'); : Match against a set of literal values
NOT IN: WHERE status NOT IN ('CANCELLED'); : Exclude rows with values in a given set
LIKE: WHERE name LIKE 'A%'; : Search text using wildcard patterns
NOT LIKE: WHERE code NOT LIKE 'ERR%'; : Filter out rows matching a pattern
REGEXP / RLIKE: WHERE email REGEXP '@gmail\.com$'; : Match text with regular expressions
NOT REGEXP: WHERE phone NOT REGEXP '^\+91';: Exclude rows matching a regex pattern
IS NULL: WHERE manager_id IS NULL; : Find rows with NULL (missing) values
IS NOT NULL: WHERE manager_id IS NOT NULL; : Find rows where value exists (not NULL)
EXISTS: WHERE EXISTS (SELECT 1 FROM payments p WHERE p.order_id=o.id); : Test for existence of related rows in a subquery
NOT EXISTS: WHERE NOT EXISTS (SELECT 1 FROM blacklist b WHERE b.user_id=u.id); : Ensure no
related rows exist
ANY / SOME / ALL: WHERE salary > ALL (SELECT salary FROM staff WHERE dept='HR'); : Compare a value against a subquery result set
CASE WHEN: SELECT CASE WHEN score>=60 THEN 'Pass' ELSE 'Fail' END AS result FROM tests; : Create conditional computed columns
IF() (MySQL): SELECT IF(qty>0,'In Stock','Out') FROM products; : MySQL-specific inline conditional expression
COALESCE: SELECT COALESCE(nickname,name) AS display_name FROM users; : Return first non-NULL
argument
NULLIF: SELECT NULLIF(col1,col2) AS diff FROM t; :Return NULL if two expressions are equal
GREATEST / LEAST: SELECT GREATEST(q1,q2,q3) AS best FROM scores; : Pick the largest or smallest value from a list
INTERVAL(): SELECT INTERVAL(score,50,60,70) AS grade_band FROM exams; : Return index of range into which value falls
Row Sub-query: WHERE (a,b) IN (SELECT x,y FROM t2); : Compare multiple columns as a composite key
WITH RECURSIVE: WITH RECURSIVE nums AS (...) SELECT * FROM nums; : Generate sequences orhierarchical data via CTE
AND / OR / NOT: SELECT * FROM users WHERE active=1 AND NOT country='IN'; : Combine multiple
filter conditions logically
Comparison Operators: SELECT * FROM items WHERE qty <> 0; : Compare column values to constants or other columns
BETWEEN: WHERE order_date BETWEEN '2025-01-01' AND '2025-01-31'; : Select values inside an inclusive range
NOT BETWEEN: WHERE age NOT BETWEEN 18 AND 30; : Exclude values that fall within a range
IN: WHERE status IN ('NEW','PENDING'); : Match against a set of literal values
NOT IN: WHERE status NOT IN ('CANCELLED'); : Exclude rows with values in a given set
LIKE: WHERE name LIKE 'A%'; : Search text using wildcard patterns
NOT LIKE: WHERE code NOT LIKE 'ERR%'; : Filter out rows matching a pattern
REGEXP / RLIKE: WHERE email REGEXP '@gmail\.com$'; : Match text with regular expressions
NOT REGEXP: WHERE phone NOT REGEXP '^\+91';: Exclude rows matching a regex pattern
IS NULL: WHERE manager_id IS NULL; : Find rows with NULL (missing) values
IS NOT NULL: WHERE manager_id IS NOT NULL; : Find rows where value exists (not NULL)
EXISTS: WHERE EXISTS (SELECT 1 FROM payments p WHERE p.order_id=o.id); : Test for existence of related rows in a subquery
NOT EXISTS: WHERE NOT EXISTS (SELECT 1 FROM blacklist b WHERE b.user_id=u.id); : Ensure no
related rows exist
ANY / SOME / ALL: WHERE salary > ALL (SELECT salary FROM staff WHERE dept='HR'); : Compare a value against a subquery result set
CASE WHEN: SELECT CASE WHEN score>=60 THEN 'Pass' ELSE 'Fail' END AS result FROM tests; : Create conditional computed columns
IF() (MySQL): SELECT IF(qty>0,'In Stock','Out') FROM products; : MySQL-specific inline conditional expression
COALESCE: SELECT COALESCE(nickname,name) AS display_name FROM users; : Return first non-NULL
argument
NULLIF: SELECT NULLIF(col1,col2) AS diff FROM t; :Return NULL if two expressions are equal
GREATEST / LEAST: SELECT GREATEST(q1,q2,q3) AS best FROM scores; : Pick the largest or smallest value from a list
INTERVAL(): SELECT INTERVAL(score,50,60,70) AS grade_band FROM exams; : Return index of range into which value falls
Row Sub-query: WHERE (a,b) IN (SELECT x,y FROM t2); : Compare multiple columns as a composite key
WITH RECURSIVE: WITH RECURSIVE nums AS (...) SELECT * FROM nums; : Generate sequences orhierarchical data via CTE