WHERE: filtering rows

Part of the free LogicWiz SQL & Databases course, Module 2: Your first queries. The queries and practice run in the app on real Postgres.

The scholarship is for CS or Electronics students with CGPA above 8. Kabir writes the query, and Arjun, with 7.6, makes the list. Every word in Kabir's WHERE was right. Only the brackets were missing.

You'll learn to

• Filter with =, <>, <, >, <=, >=
• Combine conditions with AND, OR, NOT, and get the precedence right
• Use BETWEEN, IN, LIKE and ILIKE

Learn

Comparisons

SELECT name, cgpa
FROM students
WHERE cgpa >= 9;

WHERE checks each row and keeps the ones where the condition is true. The operators are =, <> (or !=) for "not equal", <, >, <= and >=. Text and dates go in single quotes: WHERE city = 'Pune', WHERE joined_on >= '2024-01-01'.

AND and OR

SELECT name, city, year
FROM students
WHERE city = 'Delhi' AND year = 2;

SELECT name, city
FROM students
WHERE city = 'Pune' OR city = 'Kochi';

AND needs both sides true. OR needs at least one. Rohan is the only second-year from Delhi; three students are from Pune or Kochi.

NOT, then AND, then OR

Like × before + in maths, SQL has an order: NOT first, then AND, then OR.

So a OR b AND c means a OR (b AND c), not (a OR b) AND c.

Kabir wrote:

dept_id = 1 OR dept_id = 2 AND cgpa > 8

Postgres read it as "every CS student, or Electronics students above 8". The CGPA rule never touched CS students. The fix is brackets: (dept_id = 1 OR dept_id = 2) AND cgpa > 8. When you mix AND and OR, always write the brackets.

Kabir's query, then the fix

SELECT name, dept_id, cgpa
FROM students
WHERE dept_id = 1 OR dept_id = 2 AND cgpa > 8;

SELECT name, dept_id, cgpa
FROM students
WHERE (dept_id = 1 OR dept_id = 2) AND cgpa > 8;

The first answer has 9 rows, including Arjun (7.6) and even Aditya, who has no CGPA. The second has the 7 students who really qualify.

BETWEEN and IN

SELECT name, cgpa
FROM students
WHERE cgpa BETWEEN 8 AND 9;

SELECT name, city
FROM students
WHERE city IN ('Delhi', 'Mumbai', 'Kolkata');

BETWEEN 8 AND 9 means cgpa >= 8 AND cgpa <= 9: both ends included, so Diya's 9.0 is in. IN (…) is a short way to write many ORs on one column. Both have NOT forms: NOT BETWEEN, NOT IN.

LIKE and ILIKE: patterns in text

SELECT name
FROM students
WHERE name LIKE 'A%';

SELECT name
FROM students
WHERE name ILIKE '%sh%';

In a LIKE pattern, % matches any run of characters (even none) and _ matches exactly one. 'A%' means "starts with A". LIKE cares about case; ILIKE (Postgres only) ignores it, so '%sh%' finds Sharma, Shah and Manish alike.

Two classic slips:

• Mixing AND and OR without brackets (Kabir's bug).
• BETWEEN 9 AND 8 returns nothing. BETWEEN needs the smaller value first, because it means >= 9 AND <= 8.

MySQL has no ILIKE. With its usual settings, LIKE there is already case-insensitive, so LIKE '%sh%' finds "Shah". The same query in Postgres would miss it. In Postgres use ILIKE, or LOWER(name) LIKE '%sh%', which works everywhere.

Interviews love "Is BETWEEN inclusive?" (yes, both ends) and "What is the precedence of AND and OR?" (AND first). A follow-up: with dates and times, BETWEEN '2025-07-01' AND '2025-07-31' misses orders at 2 pm on 31 July, because '2025-07-31' means midnight. Write >= '2025-07-01' AND < '2025-08-01' instead.

In one line

WHERE keeps rows where the condition is true; AND binds before OR, so bracket them; BETWEEN includes both ends; ILIKE ignores case.