SQL AND, OR and NOT

AND, OR and NOT are words that combine or flip tests in a WHERE clause. A row passes when the combined test is true.

Both sides with AND

AND needs both sides to be true. Only customers in Austin in the USA pass.

SELECT name, city FROM customers WHERE city = 'Austin' AND country = 'USA' ORDER BY name;

One side with OR

OR needs one side to be true. A customer in Lisbon or in Berlin passes.

SELECT name, city FROM customers WHERE city = 'Lisbon' OR city = 'Berlin' ORDER BY name;

Flip a test with NOT

NOT flips the test that follows it. Customers outside the USA pass here.

SELECT name, country FROM customers WHERE NOT country = 'USA' ORDER BY name;

Example

SELECT name, city, country FROM customers WHERE (city = 'Austin' OR city = 'Toronto') AND country = 'Canada' ORDER BY name;

Key points

AND: a row passes only when both sides are true
OR: a row passes when at least one side is true
NOT: a word that flips a test from true to false, and from false to true
Use parentheses to set the order a combined test is read in.
AND, OR and NOT join or flip tests, so one WHERE clause can check several facts about a row.

In this example, the parentheses group the two city tests first. A row must match one of the two cities. The row must also have Canada as its country. Chen Wei is the only row that passes both parts.

Try it Yourself

Edit the code, then press Run.

Running is simulated · DB Fiddle

SELECT name, city, country FROM customers WHERE (city = 'Austin' OR city = 'Toronto') AND country = 'Canada' ORDER BY name;

Exercise

When does a test joined by AND pass?

Only when both sides are true When at least one side is true When both sides are false When the first side is false