SELECT and WHERE: asking for rows
A query names the columns, the table, and the test each row must pass.
The shape of a query
SELECT names the columns you want, FROM names the table, and WHERE is the test each row must pass to be included. SELECT name, city FROM students WHERE city = 'Pune' reads exactly as it looks: two columns, one table, one condition.
SELECT * means every column. It is fine while you are exploring and a poor habit in code that has to keep working: a column added later silently joins your result, and one renamed breaks it in a way that is hard to trace.
Strings use single quotes. Double quotes mean something else in standard SQL — they quote an identifier such as a column name — so 'Pune' is a value and "Pune" is a column that probably does not exist.
NULL is not a value, and that changes everything
NULL means "no value recorded", not zero and not an empty string. It is the single most common source of a query that returns fewer rows than expected.
Because NULL is unknown, comparing to it is never true. WHERE city = NULL matches nothing at all — not even rows where city is NULL — because "is this unknown thing equal to that unknown thing?" has no answer. The test you want is WHERE city IS NULL.
The same catches you the other way round. WHERE city <> 'Pune' excludes rows where city is NULL, because that comparison is unknown rather than true. If you want them, ask for them: WHERE city <> 'Pune' OR city IS NULL.
Matching text, and the cost of it
LIKE matches a pattern: % stands for any run of characters and _ for exactly one. WHERE name LIKE 'A%' finds names starting with A. Case sensitivity depends on the database and its collation, which is a real portability trap — the same query can behave differently on MySQL and PostgreSQL.
A pattern beginning with % — '%sharma' — cannot use an index on that column, because an index is ordered by the start of the value. On a large table that is the difference between instant and a full scan, and it is worth knowing before you write it rather than after.
Worked examples
Rows with no city recorded
/SELECT name FROM students WHERE city IS NULL/Against:
students(name, city)WHERE city = NULL would return nothing, silently.
Everyone not in Pune, including unknowns
/SELECT name FROM students WHERE city <> 'Pune' OR city IS NULL/Against:
students(name, city)Without the OR, rows with a NULL city vanish from a result that plainly ought to contain them.
Check yourself
Practice questions written for this lesson — not past exam papers.
Practise every question in this subject
What does WHERE city = NULL match?
Why can a LIKE pattern starting with % be slow?