SQL filters and sorting: WHERE, IN, BETWEEN, LIKE and NULL

A WHERE clause keeps only the rows for which its condition is true. Almost every filtering question in a viva or a placement test comes down to the operators below.

The operators worth knowing by heart

• AND / OR: combine conditions. AND binds tighter than OR, so use brackets when you mix them.
• IN (a, b, c): shorter than many ORs.
• BETWEEN x AND y: includes both ends.
• LIKE 'Ra%': % matches any run of characters, _ matches exactly one. ILIKE ignores case (PostgreSQL only).

CREATE TABLE students (roll_no int, name text, branch text, city text);
INSERT INTO students VALUES
  (1, 'Ravi', 'CSE', 'Pune'), (2, 'Rani', 'ECE', NULL), (3, 'Kiran', 'CSE', 'Delhi'), (4, 'Raju', 'MECH', 'Pune');
SELECT name FROM students WHERE branch IN ('CSE', 'ECE') AND name LIKE 'Ra%' ORDER BY name;

Rani
Ravi

The NULL trap

NULL means unknown, so NULL = NULL is not true, and neither is NULL <> 'Pune'. Use IS NULL and IS NOT NULL.

CREATE TABLE s (name text, city text);
INSERT INTO s VALUES ('Ravi', 'Pune'), ('Rani', NULL), ('Kiran', 'Delhi');
SELECT count(*) FROM s WHERE city <> 'Pune';
SELECT count(*) FROM s WHERE city <> 'Pune' OR city IS NULL;

1
2

The first query skips Rani because her city is unknown. Interviewers love this one.

Sorting and paging

ORDER BY sorts (ASC is the default), and LIMIT keeps the first n rows of that order. Without ORDER BY, the order of rows is not guaranteed, so LIMIT alone gives you any n rows.

CREATE TABLE scores (name text, marks int);
INSERT INTO scores VALUES ('A', 70), ('B', 92), ('C', 85), ('D', 92);
SELECT name, marks FROM scores ORDER BY marks DESC, name LIMIT 3;

B|92
D|92
C|85

The second sort key (name) breaks the tie between B and D, so the answer is the same every time.