Skip to main content
Filtering & Logic
1 / 4
0%

AND, OR, NOT

0/3 solved3 to unlock next
Locked

Lesson 01

AND, OR, NOT

Lesson Description

Combine multiple conditions in a WHERE clause.

Easy Project

Mini project: Turn this lesson into a real-world query artifact by writing one clean business report query and validating output quality.

Why one condition is rarely enough

In the SQL Fundamentals course you used WHERE to keep rows that pass a single test, such as salary > 6000. Real questions almost never stop at one test. A shipping lead asks: "Which people in the Shipping department earn more than 6,000?" That is two facts that must both hold. A clinic manager asks: "Show me every appointment that was cancelled or missed." That is two outcomes where either one counts.

SQL gives you three logical operators to build these compound filters:

OperatorKeeps a row when...Plain English
ANDboth sides are true"this and also that"
ORat least one side is true"this, that, or both"
NOTthe condition is false"anything except this"

Each operator takes boolean expressions (things that evaluate to true or false) and combines them into a new boolean expression. WHERE then keeps only the rows for which the final result is true.

Syntax

SELECT column_list
FROM table_name
WHERE condition_a AND condition_b;

WHERE condition_a OR condition_b;

WHERE NOT condition_a;

WHERE (condition_a OR condition_b) AND NOT condition_c;

You can chain as many conditions as you like. Parentheses group them, exactly like in arithmetic.

Worked examples

Example 1: AND narrows the result

Business question: which Shipping employees (department 50) earn more than 6,000?

SELECT first_name, last_name, salary
FROM employees
WHERE department_id = 50
  AND salary > 6000;

Department 50 has 7 employees, but only 4 survive both tests: Matthew Weiss (8,000), Adam Fripp (8,200), Payam Kaufling (7,900) and Shanta Vollman (6,500). Kevin Mourgos is in Shipping but earns 5,800, so the second condition rejects him. Every AND you add can only keep the result the same size or make it smaller.

Example 2: OR widens the result

Switch to the hospital sandbox. Which appointments did not happen as planned?

SELECT id, patient_id, appointment_date, status
FROM appointments
WHERE status = 'cancelled'
   OR status = 'no-show';

This returns 3 rows: one cancelled appointment (id 4, on 2025-01-18) and two no-shows (ids 14 and 55). A row only needs to satisfy one side of an OR. Every OR you add can only keep the result the same size or make it larger.

When all the alternatives test the same column for equality, as here, the next lesson shows a shorter way to write it with IN.

Example 3: NOT excludes a group

Back in the store sandbox: how many orders still need attention, meaning they are anything other than delivered?

SELECT id, customer_id, order_date, status
FROM orders
WHERE NOT status = 'delivered';

There are 50 orders, 33 of them delivered, so this returns 17 rows: the pending, shipped and cancelled ones. NOT status = 'delivered' means the same as status <> 'delivered', and most people write the second form for simple comparisons. NOT earns its keep when you want to negate a whole group of conditions at once, which you will see below.

Example 4: Mixing AND with OR (and why parentheses matter)

Business question: which employees in IT (department 60) or Sales (department 80) earn more than 10,000?

Here is the version most beginners write first:

SELECT first_name, last_name, department_id, salary
FROM employees
WHERE department_id = 60 OR department_id = 80 AND salary > 10000;

It returns 10 rows, including Diana Lorentz from IT who earns 4,200. That is clearly wrong. The cause is operator precedence: AND binds more tightly than OR, so PostgreSQL reads the filter as:

WHERE department_id = 60 OR (department_id = 80 AND salary > 10000)

In words: "everyone in IT, plus Sales people over 10,000". All 5 IT employees come back, regardless of salary, plus 5 Sales managers.

Add parentheses to say what you mean:

SELECT first_name, last_name, department_id, salary
FROM employees
WHERE (department_id = 60 OR department_id = 80)
  AND salary > 10000;

Now you get 5 rows, all from Sales: John Russell (14,000), Karen Partners (13,500), Alberto Errazuriz (12,000), Gerald Cambrault (11,000) and Eleni Zlotkey (10,500). Nobody in IT earns more than 10,000, so IT contributes nothing. Lower the threshold to 8,000 and Alexander Hunold (9,000) from IT joins a list of 9.

Example 5: Three conditions and a negation

The hospital wants to invite older patients from its two largest cities to a screening program. Which patients live in Seattle or Portland and were born before 1970?

SELECT first_name, last_name, city, date_of_birth
FROM patients
WHERE (city = 'Seattle' OR city = 'Portland')
  AND date_of_birth < '1970-01-01';

This returns 7 patients: five from Seattle (for example Charles Young, born 1944) and two from Portland (Barbara Harris and Lisa Green). Drop the parentheses and you get 12 rows instead, because every Seattle patient comes back no matter their age, including Joseph Robinson, born in 2015.

You can also negate a grouped condition. "Doctors hired since 2018 who are not emergency physicians" reads naturally with NOT:

SELECT first_name, last_name, specialty, hire_date
FROM doctors
WHERE NOT specialty = 'Emergency Physician'
  AND hire_date >= '2018-01-01';

That gives 6 doctors, from Emily Carter (hired 2018-03-05) to Frank O'Brien (hired 2022-04-18). Marcus Wright was hired in 2019 but is an emergency physician, so he is excluded.

How PostgreSQL evaluates it

Precedence

When there are no parentheses, PostgreSQL applies the operators in this order:

  1. Comparisons such as =, <, >=
  2. NOT
  3. AND
  4. OR

So a OR b AND NOT c means a OR (b AND (NOT c)). You do not need to memorize this if you follow one habit: whenever a filter contains both AND and OR, wrap the OR part in parentheses. It costs nothing and removes all doubt for the next reader.

De Morgan's laws

Negating a group flips the operators inside it:

ThisIs the same as
NOT (a OR b)NOT a AND NOT b
NOT (a AND b)NOT a OR NOT b

For example, "employees in neither Purchasing nor Shipping" can be written either way, and both return 27 rows:

SELECT first_name, last_name, department_id
FROM employees
WHERE NOT (department_id = 30 OR department_id = 50);

-- equivalent
SELECT first_name, last_name, department_id
FROM employees
WHERE department_id <> 30 AND department_id <> 50;

Three-valued logic: true, false and unknown

SQL conditions do not just produce true or false. If a column is NULL, a comparison against it produces a third value, unknown. WHERE keeps a row only when the final result is true, so unknown rows are dropped just like false ones.

The HR locations table has 10 rows, but only 4 have a state value (TX, CA, WA, ON). Watch what happens with NOT:

SELECT city, state
FROM locations
WHERE NOT state = 'WA';

You might expect 9 rows, every location except Seattle. You get 3: Southlake, South San Francisco and Toronto. For the 6 European and Mexican offices, state = 'WA' is unknown, and NOT unknown is still unknown, so those rows are dropped. The truth tables in short:

ExpressionResult
true AND unknownunknown
false AND unknownfalse
true OR unknowntrue
false OR unknownunknown
NOT unknownunknown

The NULL lesson later in this course covers how to handle this properly with IS NULL.

Short-circuiting is not guaranteed

In many programming languages, a AND b never evaluates b if a is false. PostgreSQL makes no such promise in SQL. The planner is free to reorder conditions for speed, so do not write a filter that depends on one condition protecting another from an error. Use CASE when evaluation order truly matters.

Common mistakes

Mistake 1: shortening OR the way you would say it out loud.

SELECT first_name FROM employees
WHERE department_id = 60 OR 80;

PostgreSQL rejects this with ERROR: argument of OR must be type boolean, not type integer. Each side of OR must be a full condition. Fix: WHERE department_id = 60 OR department_id = 80, or WHERE department_id IN (60, 80).

Mistake 2: forgetting parentheses when mixing AND and OR. As Example 4 showed, dept = 60 OR dept = 80 AND salary > 10000 silently returns 10 rows instead of 5. There is no error, which makes this bug dangerous. Fix: parenthesize the OR group.

Mistake 3: using AND when the column can only hold one value.

SELECT id FROM orders
WHERE status = 'pending' AND status = 'shipped';

This returns 0 rows, because no single row can have two statuses at once. When someone says "pending and shipped orders" they mean the union of both groups, which is OR (14 rows here).

Mistake 4: expecting NOT to include NULL rows. WHERE NOT state = 'WA' skips rows with no state. If you want them, say so: WHERE state <> 'WA' OR state IS NULL returns all 9 non-Seattle locations.

Other databases

AND, OR, NOT and their precedence are standard SQL and work the same way in MySQL, SQL Server, SQLite and Oracle. MySQL additionally accepts &&, || and ! as synonyms, but in PostgreSQL || is string concatenation, so stick to the keywords for portable code.

Practice

The exercises below this lesson ask you to:

  • Find IT or Sales employees above a salary threshold. Think about which part needs parentheses.
  • List employees who are in the executive department or are very high earners. Notice that this one is a pure OR.
  • Return store customers from outside one country, sorted by two columns. Before you submit, run a quick SELECT DISTINCT country FROM customers to see exactly how countries are spelled in the data.

Key takeaways

  • AND requires every condition to be true and shrinks the result; OR requires at least one and grows it.
  • AND is evaluated before OR. Always put parentheses around OR groups when you mix the two.
  • NOT (a OR b) equals NOT a AND NOT b, and NOT (a AND b) equals NOT a OR NOT b.
  • Comparisons with NULL produce unknown, and WHERE drops unknown rows, even under NOT.
  • Each side of AND or OR must be a complete condition: dept = 60 OR dept = 80, never dept = 60 OR 80.

AND, OR, NOT: in depth

Real questions rarely have a single condition: "sales staff earning over 8,000 or anyone hired before 2010". AND, OR and NOT combine conditions, and parentheses decide how they group. Getting the grouping wrong is one of the most common silent bugs in SQL.

Worked examples

Parentheses change the answer

SELECT first_name, department_id, salary
FROM employees
WHERE (department_id = 80 OR department_id = 90)
  AND salary > 10000;

Without the parentheses, AND binds first, and the query would return every employee in department 80 plus only the high earners in 90.

Excluding a group

SELECT id, status, appointment_date
FROM appointments
WHERE NOT (status = 'cancelled' OR status = 'no-show');

NOT applied to the grouped condition removes both statuses. Rows with a NULL status are also excluded, because NOT unknown is unknown.

Common mistakes

  • Relying on operator precedence. AND is evaluated before OR, so always add parentheses when you mix them.
  • Writing department_id = 80 OR 90. The right side is not compared to anything; spell out both comparisons or use IN.
  • Forgetting NULLs in negative filters. WHERE status <> 'cancelled' also drops rows where status is NULL.

Other databases

  • The AND-before-OR precedence is the same in every SQL database.
  • PostgreSQL offers IS DISTINCT FROM as a NULL-safe <>; MySQL uses the NULL-safe equality operator <=>.
Check yourself: Rewrite WHERE NOT (a = 1 AND b = 2) without the outer NOT.

WHERE a <> 1 OR b <> 2 (De Morgan's law). It still drops rows where a or b is NULL.

Concept check

Quick check: AND, OR, NOT

3 quick questions. One at a time. Instant score at the end.

Exercise 1

IT or Sales employees earning > $6000

Enterprise Workforce AnalyticseasyPostgreSQL

Return first_name, last_name, and salary for employees in IT (department 60) or Sales (department 80) earning more than 6000.

Autocomplete: start typing table/column namesPaste supported

No results yet

Run a query to see results here

Exercise 2

Exec department or very high earners

Enterprise Workforce AnalyticseasyPostgreSQL

Return first_name, last_name, department_id, and salary for employees who are either in department 90 or earn more than 12000.

Autocomplete: start typing table/column namesPaste supported

No results yet

Run a query to see results here

Exercise 3

Customers NOT from the USA

Commerce Operations IntelligenceeasyPostgreSQL

From the store sandbox, return first_name, last_name, and country for customers whose country is not 'USA', ordered by country, then last_name.

Autocomplete: start typing table/column namesPaste supported

No results yet

Run a query to see results here

Filtering & Logic
1 / 4
0%

AND, OR, NOT

0/3 solved3 to unlock next
Locked