SQL · Theory

WHERE

Theory

40/439

WHERE

WHERE filters rows before grouping. SELECT name FROM students WHERE marks < 40; only fail rows. Conditions use =, <, >, <>, LIKE, IN, BETWEEN, IS NULL, AND, OR.

AND: both must be true. OR: either. Use brackets: WHERE (city = 'Pune' OR city = 'Goa') AND marks < 40. Without brackets, AND binds tighter than OR — easy viva trap.

WHERE marks = NULL is never true. Use IS NULL. Strings in quotes: WHERE name = 'Asha'. Numbers without quotes.

Trap: filtering in PHP after SELECT *. Trap: WHERE on an expression that kills the index (WHERE YEAR(dob) = 2004) — prefer dob range.

On the example next to this theory — WHERE combines predicates with AND/OR; LIKE does pattern match.

Diagram

  FROM students
        │
        ▼
  WHERE city = 'Pune'
        │
        ▼
  SELECT name, marks

Exam tip

WHERE marks < 40. AND/OR with brackets. IS NULL.

WHERE — sample query

CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, price REAL);
INSERT INTO products VALUES (1, 'Pen', 10), (2, 'Book', 200), (3, 'Bag', 50);
SELECT * FROM products
WHERE price BETWEEN 20 AND 150 AND name LIKE 'B%';

WHERE combines predicates with AND/OR; LIKE does pattern match.

Short notes

DefWHERE keeps rows that match a condition.
RuleIS NULL not = NULL. Quote strings. Bracket OR + AND.
TrapWHERE marks = NULL.

SQL · Theory

WHERE

← All stacks

Theory

40/439

WHERE

WHERE filters rows before grouping. SELECT name FROM students WHERE marks < 40; only fail rows. Conditions use =, <, >, <>, LIKE, IN, BETWEEN, IS NULL, AND, OR.

AND: both must be true. OR: either. Use brackets: WHERE (city = 'Pune' OR city = 'Goa') AND marks < 40. Without brackets, AND binds tighter than OR — easy viva trap.

WHERE marks = NULL is never true. Use IS NULL. Strings in quotes: WHERE name = 'Asha'. Numbers without quotes.

Trap: filtering in PHP after SELECT *. Trap: WHERE on an expression that kills the index (WHERE YEAR(dob) = 2004) — prefer dob range.

On the example next to this theory — WHERE combines predicates with AND/OR; LIKE does pattern match.

Diagram

  FROM students
        │
        ▼
  WHERE city = 'Pune'
        │
        ▼
  SELECT name, marks

Exam tip

WHERE marks < 40. AND/OR with brackets. IS NULL.

WHERE — sample query

CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, price REAL);
INSERT INTO products VALUES (1, 'Pen', 10), (2, 'Book', 200), (3, 'Bag', 50);
SELECT * FROM products
WHERE price BETWEEN 20 AND 150 AND name LIKE 'B%';

WHERE combines predicates with AND/OR; LIKE does pattern match.

Short notes

DefWHERE keeps rows that match a condition.
RuleIS NULL not = NULL. Quote strings. Bracket OR + AND.
TrapWHERE marks = NULL.

WHERE

WHERE — sample query

Short notes

Questions

WHERE

WHERE — sample query

Short notes

Questions