SQL AND, OR and NOT

AND, OR and NOT let you combine several conditions in one WHERE clause.

The AND Operator

AND keeps a row only when all conditions are true.

SELECT name, city, marks
FROM students
WHERE city = 'Karachi' AND marks > 70;

Three students live in Karachi, but only Ali also has more than 70 marks. Hamza has exactly 70, which is not more than 70.

You can join more than two conditions:

SELECT name
FROM students
WHERE city = 'Lahore' AND age > 20 AND marks > 86;

The OR Operator

OR keeps a row when at least one condition is true.

SELECT name, city
FROM students
WHERE city = 'Lahore' OR city = 'Islamabad';

The NOT Operator

NOT turns a condition around. It keeps rows where the condition is false.

SELECT name, city
FROM students
WHERE NOT city = 'Karachi';

NOT is also used in phrases you will meet later: NOT LIKE, NOT IN, NOT BETWEEN and IS NOT NULL.

How They Work

Mixing AND and OR: Use Brackets

AND is checked before OR, just like multiplication comes before addition in maths. This can give surprising results. Suppose you want students aged 21 or more who live in Karachi or Lahore:

SELECT name, city, age
FROM students
WHERE (city = 'Karachi' OR city = 'Lahore') AND age >= 21;

Without the brackets, the database would read it as city = 'Karachi' OR (city = 'Lahore' AND age >= 21), and every Karachi student would be returned, whatever their age.

Exercise