WHERE: picking the rows you need

Usually you do not need every row of a table. “Only 2nd-year students”, “products cheaper than 100,000” — conditions like these are written with WHERE.

The pattern

SELECT columns FROM table WHERE condition;

The database checks every row: if the condition is true, the row goes into the result; if not, it is left out.

SELECT first_name, last_name, course FROM students WHERE course = 2;

Comparison operators

SELECT first_name, last_name, salary FROM teachers WHERE salary > 12000000;

Text and dates go in quotes

Numbers are written without quotes; text and dates go in single quotes:

SELECT first_name, last_name FROM students WHERE city = 'Samarqand';

Forget the quotes and the database takes Samarqand for a column name and complains that there is no such column. Dates are written as 'year-month-day':

SELECT first_name, last_name, hire_date FROM teachers WHERE hire_date >= '2020-01-01';

Several conditions: AND, OR, NOT

• AND — both conditions must hold;
• OR — at least one must hold;
• NOT — turns a condition around.

SELECT first_name, last_name, city, course
FROM students
WHERE course = 1 AND city = 'Toshkent';

When you mix AND and OR, use brackets — otherwise AND is applied first and the result may not be what you expect:

SELECT first_name, city, course
FROM students
WHERE course = 4 AND (city = 'Buxoro' OR city = 'Navoiy');

Remember

• WHERE picks rows by a condition.
• Text and dates go 'in single quotes', numbers go without.
• Mixing AND and OR? Use brackets.