The Project | About Us | Contribute | Donations | License
HOME My courses Completed
◄ Previous
◄

Filtering Data with WHERE

Next ►
►

Up to now every query of yours has returned all the rows of the table. With 20 products that's manageable; with 20 million, neither your terminal nor your patience would survive. The WHERE clause is what turns a query into a specific question: not "give me the products", but "give me the cosmetics products that cost more than 10 euros". In this lesson you'll learn to filter rows with comparisons over numbers, text and dates, to combine them with AND, OR and NOT, to handle boolean columns, and to recognise two traps that produce wrong results without raising any error: operator precedence and comparisons with null values.

Contents

  1. WHERE as a row-by-row filter
  2. Where WHERE fits in the logical execution order
  3. Comparing numbers
  4. Comparing text
  5. Comparing dates
  6. Comparing booleans
  7. Combining conditions: AND, OR, NOT and parentheses
  8. Calculated expressions inside WHERE
  9. The symptom of nulls
  10. Common Mistakes and Tips
  11. Exercises
  12. Conclusion

  1. WHERE as a row-by-row filter

WHERE is written after FROM and holds a condition: an expression that, for each row, evaluates to true, false or unknown.

SELECT id,
       name,
       price
FROM products
WHERE price > 10;
id name price
1 Extra virgin olive oil 500 ml 12.50
6 Aloe vera face cream 50 ml 18.90
8 Almond body oil 200 ml 14.25
10 Concentrated eco laundry detergent 1 L 11.20
13 Soy wax candles (pack of 2) 13.75
15 Ceremonial matcha green tea 30 g 22.00
20 Spirulina capsules 120 units 16.40

7 rows out of 20. The mental mechanism is simple: PostgreSQL walks the rows, evaluates price > 10 on each one and keeps only the ones that come out true.

And here's the key that solves half the problems with nulls: only rows whose condition is true get through. A false condition discards the row, and an unknown condition (NULL) discards it too. WHERE isn't "discard the false ones": it's "keep the true ones". We'll come back to this in section 9.

In the relational model of 01-05, this is a selection (choosing rows), as opposed to the projection that SELECT does (choosing columns). A query with SELECT and WHERE does both: it trims the table vertically and horizontally.

  1. Where WHERE fits in the logical execution order

You can now switch on step 2 of the diagram from 02-01:

flowchart LR
    A["1 · FROM<br/>products<br/>20 rows"] --> B["2 · WHERE<br/>price > 10<br/>7 rows"]
    B --> C["3 · SELECT<br/>id, name, price"]
    C --> D["4 · ORDER BY<br/>(02-05)"]
    D --> E["5 · LIMIT<br/>(02-06)"]

Three practical consequences follow from that order:

Consequence Explanation
WHERE can't use SELECT aliases When it's evaluated, the SELECT hasn't run yet (you saw it in 02-02)
WHERE can use columns you don't project WHERE cost > 5 works even if cost doesn't appear in the SELECT: the column exists in the row coming out of FROM
Filtering early is filtering cheaply The sooner you discard rows, the less work there is to do afterwards. It's the basis of module 8's optimisation

An example of the second point:

SELECT id, name, price
FROM products
WHERE cost > 8;
id name price
6 Aloe vera face cream 50 ml 18.90
15 Ceremonial matcha green tea 30 g 22.00
20 Spirulina capsules 120 units 16.40

The cost column isn't in the result, but it does take part in the filter. Perfectly valid.

  1. Comparing numbers

The comparison operators you saw in 01-03, now applied for real:

Operator Meaning Example Rows returned on products
= Equal WHERE category_id = 2 4
<> or != Not equal WHERE category_id <> 1 15
> Greater than WHERE price > 10 7
>= Greater or equal WHERE stock >= 100 8
< Less than WHERE price < 5 7
<= Less or equal WHERE stock <= 50 3 
SELECT id, name, category_id, price, stock
FROM products
WHERE category_id = 2;
id name category_id price stock
6 Aloe vera face cream 50 ml 2 18.90 60
7 Rosemary solid shampoo 80 g 2 8.40 95
8 Almond body oil 200 ml 2 14.25 45
9 Calendula lip balm 15 ml 2 4.60 130

The whole "Natural cosmetics" category. Notice you had to write 2 and not 'Natural cosmetics': products only holds the identifier. Translating the 2 into its name requires the categories table and therefore a JOIN (module 3).

A typical operational filter: products out of stock.

SELECT id, name, stock, active
FROM products
WHERE stock = 0;
id name stock active
13 Soy wax candles (pack of 2) 0 true

Only product 13, exactly as fixed when the dataset was designed.

Numbers without quotes. WHERE price > '10' works in PostgreSQL because the engine converts the string, but it's a bad habit: in more complex comparisons it can stop an index being used and, in other engines, it produces alphabetical comparisons where '9' > '10'. Write WHERE price > 10.

  1. Comparing text

Strings are compared with the same operators, in single quotes:

SELECT id, name, last_name, city, country
FROM customers
WHERE country = 'Portugal';
id name last_name city country
7 Sofia Moreira Costa Lisbon Portugal
8 Tiago Almeida Nunes Porto Portugal 
SELECT id, name, last_name, city
FROM customers
WHERE country = 'France';
id name last_name city
9 Camille Dubois Lyon
10 Julien Moreau Paris

4.1. Text comparisons are case-sensitive

This is trap number one with text data:

SELECT id, name, country FROM customers WHERE country = 'spain';
(0 rows)

It returns 0 rows. There's no error, there's no warning: there simply is no customer whose country is exactly the lowercase string 'spain'. The 11 Spanish customers have 'Spain'.

SELECT id, name, last_name, city
FROM customers
WHERE country = 'Spain';
id name last_name city
1 Lucía Martínez Soler Valencia
2 Carlos Ferrer Ibáñez Valencia
3 Marta Sanchis Gil Castellón
4 Javier Ortega Ruiz Madrid
5 Ana Belmonte Roca Barcelona
6 Pau Llorens Vidal Valencia
11 Elena Navarro Puig Alicante
12 Diego Ramos Herrera Seville
13 Núria Bosch Ferrer Barcelona
14 Hugo Iglesias Pardo Zaragoza
15 Inés Carrasco Vega Valencia

11 rows, which with the 2 from Portugal and the 2 from France add up to the 15 customers.

Remember the distinction from 01-03: SQL's syntax is case-insensitive (select = SELECT), but the data isn't. The robust way to compare while ignoring case uses string functions or the ILIKE operator, and both belong to modules 4 and 6:

-- Previews, we won't develop them here
WHERE LOWER(country) = 'spain'    -- string function, lesson 06-01
WHERE country ILIKE 'spain'       -- case-insensitive comparison, lesson 04-01

4.2. Spaces count (and they're invisible)

SELECT id, name FROM customers WHERE country = 'Spain ';

0 rows. There's a space at the end of the literal and 'Spain' ≠ 'Spain '. This error is especially treacherous because you can't see it when reading the query, and it shows up constantly when data arrives from a CSV or from a badly sanitised web form.

Two nuances worth knowing:

  • Leading spaces count too: ' Spain' doesn't match either.
  • With the CHAR(n) type (fixed length) the SQL standard pads with spaces and ignores them when comparing, so 'Spain' and 'Spain ' would indeed be equal. It's one of the reasons GreenStore uses VARCHAR and TEXT, never CHAR.

If you suspect stray spaces, a quick trick to see them:

SELECT DISTINCT '[' || country || ']' AS country_delimited FROM customers;
country_delimited
[Spain]
[Portugal]
[France]

The brackets would give away any parasitic space. (DISTINCT is the subject of the next lesson; here it just stops the same three countries being repeated fifteen times.) The definitive cleanup of spaces is done with TRIM, in module 6.

4.3. Ordering comparisons over text

The < and > operators also work with strings and use the alphabetical order of the database's collation:

SELECT id, name, last_name
FROM customers
WHERE last_name < 'C';
id name last_name
8 Tiago Almeida Nunes
5 Ana Belmonte Roca
13 Núria Bosch Ferrer

The three customers whose surnames start with A or B. It's an uncommon filter in practice —for prefix searches you use LIKE, in lesson 04-01— but it's worth knowing it exists. The detail of how collation affects order is covered in lesson 02-05.

  1. Comparing dates

Dates are written as text literals in ISO format ('YYYY-MM-DD') and PostgreSQL converts them to the column's DATE type:

SELECT id, customer_id, order_date, status
FROM orders
WHERE order_date >= '2026-01-01';
id customer_id order_date status
17 7 2026-01-13 shipped
18 5 2026-01-27 paid
19 6 2026-02-09 paid
20 9 2026-02-21 pending

The four orders from 2026. A range is built with two comparisons joined by AND:

SELECT id, customer_id, order_date, status, shipping_cost
FROM orders
WHERE order_date >= '2025-06-01'
  AND order_date <  '2025-09-01';
id customer_id order_date status shipping_cost
7 6 2025-06-11 delivered 6.50
8 7 2025-06-28 delivered 9.90
9 8 2025-07-15 delivered 9.90
10 9 2025-08-03 delivered 12.50

The orders from the summer of 2025: June, July and August.

Notice the >= start AND < end pattern, with the upper bound excluded. It's the professional way of writing date ranges, for two reasons:

  1. You don't have to know whether the month has 28, 30 or 31 days: you put day 1 of the next month.
  2. If one day those columns went from DATE to TIMESTAMP, <= '2025-08-31' would leave out everything that happened that day after midnight, whereas < '2025-09-01' would still be correct.

In module 4 you'll see BETWEEN, which writes ranges more compactly but always includes both bounds, with that same trap for dates with a time.

Typical mistakes with dates, already flagged in 01-03:

How it's written What happens
'2026-01-01' Correct, ISO 8601, unambiguous
'03/04/2026' Depends on the server's DateStyle setting: it can be 3 April or 4 March... or fail
'2026-13-01' ERROR: date/time field value out of range
2026-01-01 (no quotes) Read as the subtraction 2026 - 1 - 1 = 2024

That last one deserves a look:

SELECT id FROM orders WHERE order_date >= 2026-01-01;
ERROR:  operator does not exist: date >= integer

Just as well it errors: if the column were numeric, you'd have filtered by 2024 without noticing.

  1. Comparing booleans

products.active and suppliers.active are of type BOOLEAN. A boolean condition already is a condition: there's no need to compare it with anything.

-- These three queries are equivalent
SELECT id, name FROM products WHERE active;
SELECT id, name FROM products WHERE active = TRUE;
SELECT id, name FROM products WHERE active IS TRUE;

All three return 19 rows: the 20 products minus number 20, which is discontinued.

And for the opposite case:

SELECT id, name, price, active
FROM products
WHERE NOT active;
id name price active
20 Spirulina capsules 120 units 16.40 false 
SELECT id, name, country, active
FROM suppliers
WHERE active = FALSE;
id name country active
5 EcoNordic Supplies Germany false

Which one should you write? A comparison:

Form Readability Behaviour with NULL Recommendation
WHERE active Very high, reads like English The NULL row doesn't get through Preferred when the column is NOT NULL
WHERE active = TRUE High, explicit The NULL row doesn't get through Acceptable; useful if the reader isn't fluent in SQL
WHERE active IS TRUE Medium The NULL row doesn't get through (it returns false, not NULL) Only if the column allows nulls and you want a condition that never gives NULL
WHERE NOT active High The NULL row doesn't get through Preferred for the negative case
WHERE active = FALSE High The same as the previous one Acceptable

In GreenStore both active columns are NOT NULL, so the differences are theoretical. In real databases, a boolean column that allows nulls has three possible states (true, false, NULL) and there WHERE NOT active and WHERE active IS NOT TRUE stop being the same thing.

Dialect note: MySQL and SQLite have no real boolean type: they store 1 and 0. WHERE active works the same because any value other than 0 is true, but WHERE active = TRUE there literally means active = 1. SQL Server doesn't accept a bare WHERE column: it demands WHERE column = 1 over a BIT.

  1. Combining conditions: AND, OR, NOT and parentheses

7.1. AND: all of them must hold

SELECT id, name, city, country
FROM customers
WHERE country = 'Spain'
  AND city = 'Valencia';
id name city country
1 Lucía Valencia Spain
2 Carlos Valencia Spain
6 Pau Valencia Spain
15 Inés Valencia Spain

Four customers from Valencia. Each extra AND reduces or maintains the number of rows, never increases it.

7.2. OR: one is enough

SELECT id, customer_id, order_date, status
FROM orders
WHERE status = 'paid'
   OR status = 'pending';
id customer_id order_date status
18 5 2026-01-27 paid
19 6 2026-02-09 paid
20 9 2026-02-21 pending

The three orders not yet shipped. Each extra OR increases or maintains the number of rows.

(In 04-02 you'll write this same condition as status IN ('paid', 'pending'), which is shorter and more readable when the list grows.)

7.3. NOT: negates a condition

SELECT id, customer_id, order_date, status
FROM orders
WHERE NOT status = 'delivered';
id customer_id order_date status
6 5 2025-05-23 cancelled
16 4 2025-12-19 shipped
17 7 2026-01-13 shipped
18 5 2026-01-27 paid
19 6 2026-02-09 paid
20 9 2026-02-21 pending

6 rows, which with the 14 delivered add up to the 20 orders. WHERE status <> 'delivered' gives exactly the same result and usually reads better; NOT shines when what you're negating is a compound condition: WHERE NOT (a AND b).

7.4. Parentheses: the error that isn't an error

AND has higher priority than OR. You already saw it in the precedence table of 01-03; now you're going to see the damage it does with real data.

The business question: "give me the products from categories 1 (Food) or 2 (Natural cosmetics) that cost more than €10".

Written naively, without parentheses:

-- ⚠️ INCORRECT
SELECT id, name, category_id, price
FROM products
WHERE category_id = 1
   OR category_id = 2 AND price > 10;
id name category_id price
1 Extra virgin olive oil 500 ml 1 12.50
2 Organic brown rice 1 kg 1 3.90
3 Raw orange blossom honey 500 g 1 9.75
4 Spelt pasta 500 g 1 2.80
5 Organic crushed tomato 400 g 1 1.95
6 Aloe vera face cream 50 ml 2 18.90
8 Almond body oil 200 ml 2 14.25

7 rows, and four of them cost less than €3. The engine read:

category_id = 1  OR  (category_id = 2 AND price > 10)

That is: all of category 1, regardless of price, plus the expensive products of category 2.

With parentheses:

-- ✅ CORRECT
SELECT id, name, category_id, price
FROM products
WHERE (category_id = 1 OR category_id = 2)
  AND price > 10;
id name category_id price
1 Extra virgin olive oil 500 ml 1 12.50
6 Aloe vera face cream 50 ml 2 18.90
8 Almond body oil 200 ml 2 14.25

3 rows. This is the correct answer.

Compare the impact:

Version Rows Does it answer the question?
Without parentheses 7 No: it includes 4 cheap products
With parentheses 3 Yes

Neither of the two raises an error. The first returns a report with incorrect data that nobody notices until somebody asks why the €2.80 spelt pasta appears in the listing of products over €10.

Course rule: whenever AND and OR live together in a WHERE, add parentheses. Even if you know the precedence by heart, the parenthesis documents your intent for whoever reads the query in six months' time (which will probably be you).

7.5. A three-condition filter

SELECT id, name, category_id, price, stock
FROM products
WHERE active
  AND price < 10
  AND stock > 100;
id name category_id price stock
2 Organic brown rice 1 kg 1 3.90 200
4 Spelt pasta 500 g 1 2.80 150
5 Organic crushed tomato 400 g 1 1.95 300
9 Calendula lip balm 15 ml 2 4.60 130
11 Loofah scrubber (pack of 3) 3 5.50 110
14 Organic chamomile tea 20 bags 4 3.25 180
18 Bamboo toothbrush 5 3.50 240

Seven products that are cheap, active and with plenty of stock: perfect candidates for a promotion.

  1. Calculated expressions inside WHERE

You can use in WHERE the same expressions you learned in 02-02, writing them out in full (remember: the alias doesn't exist yet):

SELECT id,
       name,
       price,
       cost,
       price - cost AS margin
FROM products
WHERE price - cost > 5;
id name price cost margin
6 Aloe vera face cream 50 ml 18.90 9.50 9.40
8 Almond body oil 200 ml 14.25 7.10 7.15
10 Concentrated eco laundry detergent 1 L 11.20 6.00 5.20
12 Reusable cotton bags (pack of 5) 9.90 4.30 5.60
13 Soy wax candles (pack of 2) 13.75 6.90 6.85
15 Ceremonial matcha green tea 30 g 22.00 12.50 9.50
20 Spirulina capsules 120 units 16.40 8.70 7.70

The seven products that leave more than €5 of margin per unit.

And on order_lines, the sales over €30:

SELECT id,
       order_id,
       product_id,
       quantity,
       ROUND(quantity * unit_price * (1 - discount), 2) AS amount
FROM order_lines
WHERE quantity * unit_price * (1 - discount) > 30;
id order_id product_id quantity amount
18 8 1 3 35.63
24 10 6 2 34.02
28 12 15 2 44.00

Only three lines of the 47 go over €30. Note the duplication of the expression between SELECT and WHERE: it's ugly but necessary. The ways to avoid it —subqueries or CTEs— arrive in modules 7 and 10.

Performance warning: a filter like WHERE price - cost > 5 applies an operation to every row and therefore can't take advantage of an ordinary index on price or on cost. With 20 rows it makes no difference; with 20 million it does. The solution (indexes on expressions) belongs to module 8.

  1. The symptom of nulls

Ten of GreenStore's twenty orders came in through the web and have no sales rep assigned: their employee_id is NULL. Let's see what happens when you filter by that column.

Attempt 1: find the orders with no sales rep.

SELECT id, customer_id, employee_id
FROM orders
WHERE employee_id = NULL;
(0 rows)

Zero rows, when we know there are ten. No error, no warning.

Attempt 2: the orders NOT handled by sales rep 4.

Sales rep 4 (Óscar Peris) handled orders 2, 6, 10 and 16. You'd expect the "not his" ones to be 20 − 4 = 16.

SELECT id, customer_id, employee_id
FROM orders
WHERE employee_id <> 4;
id customer_id employee_id
4 4 5
8 7 5
12 10 5
14 12 6
18 5 5
20 9 6

6 rows, not 16. The ten orders with a null employee_id have vanished.

Why it happens. Remember the rule from section 1: WHERE keeps the rows whose condition is true. And any comparison with NULL gives neither true nor false: it gives unknown.

Row Condition Evaluates to Does it pass the filter?
Order 2 (employee_id = 4) 4 <> 4 false No
Order 4 (employee_id = 5) 5 <> 4 true Yes
Order 1 (employee_id = NULL) NULL <> 4 NULL (unknown) No

"I don't know who handled order 1" doesn't let you conclude it was somebody other than sales rep 4. SQL, with sound logic, would rather not claim it, and the row drops out of the result.

How to recognise the symptom. Suspect nulls when:

  • A query returns fewer rows than you expected and the filter's column can be null.
  • A filter and its negation don't add up to the total: here employee_id = 4 gives 4 rows and employee_id <> 4 gives 6; 4 + 6 = 10, not 20. The missing ten are the null ones.

That check —"do the filter and its opposite add up to the total?"— is one of the best habits you can pick up.

How it's fixed. With the IS NULL / IS NOT NULL operator, which already appeared in 01-03:

SELECT id, customer_id, employee_id
FROM orders
WHERE employee_id IS NULL;

It returns the 10 rows expected: orders 1, 3, 5, 7, 9, 11, 13, 15, 17 and 19.

We won't develop it further here: three-valued logic, IS DISTINCT FROM, how nulls behave inside AND/OR and the functions for handling them are the full content of lesson 04-03. For now hold on to two things:

  1. = NULL and <> NULL never return rows. If you write them, you've made a mistake.
  2. If a column in the WHERE allows nulls, ask yourself what you want to happen to those rows before calling the query done.

Common Mistakes and Tips

  • Using a SELECT alias in the WHERE. column "..." does not exist. Repeat the full expression: WHERE runs before SELECT.
  • Mixing AND and OR without parentheses. It raises no error; it gives a wrong result. It's the most expensive mistake in this lesson.
  • Comparing text without respecting case or with stray spaces. Zero rows and no clue. Check the real values before blaming the query.
  • Writing dates in local format. '01/03/2026' is ambiguous; '2026-03-01' never is.
  • Writing dates without quotes. WHERE order_date >= 2026-01-01 compares against the integer 2024.
  • Using <= for the end of a date range. Correct today with DATE, dangerous the day the column becomes a TIMESTAMP. Use >= start AND < end.
  • Comparing with = NULL. Always zero rows. IS NULL (lesson 04-03).
  • Forgetting that a <> filter excludes nulls. Check that the filter and its negation add up to the total number of rows.
  • Tip: build the WHERE in layers. Start with one condition, look at how many rows come out, add the next. If a jump doesn't add up, you already know which condition to review.
  • Tip: always count the rows. psql's (N rows) is your best detector of logical errors.
  • Tip: write one condition per line, with AND/OR at the start of the line and indented under WHERE. Adding, removing or commenting out a condition becomes trivial.

Exercises

Exercise 1

The purchasing team wants to review the catalogue of expensive supplies. Write a query that returns id, name, price and stock of the active products whose price is above €9 and whose stock is below 100 units. How many rows do you expect and how many come out?

Exercise 2

Management asks for "the 2025 orders that are delivered or cancelled and whose shipping costs are above €5". Write the correct query, and also write the version without parentheses, explaining how many extra rows it would return and why.

Exercise 3

On employees, write a query that returns the employees whose manager is not employee 1 (Rosa Alcázar Vives), showing id, name, last_name and manager_id. Then check whether the number of rows fits the table's total and explain what happened.

Solutions

Solution 1

SELECT id,
       name,
       price,
       stock
FROM products
WHERE active
  AND price > 9
  AND stock < 100;
id name price stock
3 Raw orange blossom honey 500 g 9.75 80
6 Aloe vera face cream 50 ml 18.90 60
8 Almond body oil 200 ml 14.25 45
10 Concentrated eco laundry detergent 1 L 11.20 70
12 Reusable cotton bags (pack of 5) 9.90 85
13 Soy wax candles (pack of 2) 13.75 0
15 Ceremonial matcha green tea 30 g 22.00 40

7 rows. The reasoning, condition by condition, over the nine products that cost more than €9:

id price > 9 stock < 100 active Passes?
1 12.50 ✓ 120 ✗ ✓ No
3 9.75 ✓ 80 ✓ ✓ Yes
6 18.90 ✓ 60 ✓ ✓ Yes
8 14.25 ✓ 45 ✓ ✓ Yes
10 11.20 ✓ 70 ✓ ✓ Yes
12 9.90 ✓ 85 ✓ ✓ Yes
13 13.75 ✓ 0 ✓ ✓ Yes
15 22.00 ✓ 40 ✓ ✓ Yes
20 16.40 ✓ 55 ✓ ✗ No

The lesson of the exercise is twofold. First, product 13 slips in because "low stock" includes "no stock at all": it has 0 units and still satisfies stock < 100. If what you're after is products worth restocking, the right filter would be stock > 0 AND stock < 100. Second, product 20 is only kept out thanks to the active condition: it satisfies the other two, and if you'd forgotten that filter you'd have proposed restocking a discontinued item.

Notice too that product 3 gets in with €9.75: price > 9 is strict, but 9.75 is greater than 9. If you'd wanted "from €10 upwards" you'd have had to write price >= 10, and then 3 and 12 would drop out.

Solution 2

The correct version:

SELECT id,
       customer_id,
       order_date,
       status,
       shipping_cost
FROM orders
WHERE order_date >= '2025-01-01'
  AND order_date <  '2026-01-01'
  AND (status = 'delivered' OR status = 'cancelled')
  AND shipping_cost > 5;
id customer_id order_date status shipping_cost
7 6 2025-06-11 delivered 6.50
8 7 2025-06-28 delivered 9.90
9 8 2025-07-15 delivered 9.90
10 9 2025-08-03 delivered 12.50
12 10 2025-10-01 delivered 12.50

5 rows. Order 6 (cancelled, €4.95 of shipping) doesn't reach €5, and the orders with €4.95 shipping or free shipping fall outside.

The version without parentheses:

-- ⚠️ INCORRECT
WHERE order_date >= '2025-01-01'
  AND order_date <  '2026-01-01'
  AND status = 'delivered' OR status = 'cancelled'
  AND shipping_cost > 5;

Since AND is evaluated before OR, the engine groups it like this:

(date >= '2025-01-01' AND date < '2026-01-01' AND status = 'delivered')
OR
(status = 'cancelled' AND shipping_cost > 5)

That is: every delivered order from 2025 regardless of shipping (14 of the 14 delivered ones are in 2025), plus the cancelled ones with shipping above €5 (none, because the only cancelled one has €4.95). Total: 14 rows instead of 5. Nine extra rows, all of them with shipping costs that don't meet the requirement. And, once again, with no error message.

Solution 3

SELECT id,
       name,
       last_name,
       manager_id
FROM employees
WHERE manager_id <> 1;
id name last_name manager_id
4 Óscar Peris Blasco 2
5 Laia Puig Sanchis 2
6 Marc Estévez Roig 2
7 Irene Salvador Mira 3

4 rows. Let's check that it adds up: the table has 8 employees; with manager_id = 1 there are 3 (Andrés, Beatriz and Daniel); with manager_id <> 1 there are 4. And 3 + 4 = 7, not 8.

One row is missing: Rosa Alcázar Vives, the general manager, whose manager_id is NULL. Her condition NULL <> 1 evaluates to unknown, not to true, so WHERE discards her. It's exactly the symptom from section 9.

If what you wanted was "everybody except those who report directly to Rosa", Rosa herself included, the correct query uses IS NULL (lesson 04-03):

SELECT id, name, last_name, manager_id
FROM employees
WHERE manager_id <> 1
   OR manager_id IS NULL;

And that one does return 5 rows: the previous four plus Rosa. The proof that the logic closes: 3 (report to Rosa) + 5 (everybody else) = 8 employees.

Conclusion

You now know how to ask GreenStore specific questions:

  • WHERE filters row by row and keeps only those whose condition is true; the false and the unknown ones are discarded alike.
  • It occupies step 2 of the logical order, before SELECT: it can use any column of the table, but never an alias from the projection.
  • You compare numbers without quotes, text in single quotes and respecting case and spaces, and dates in ISO format with the >= start AND < end pattern.
  • With booleans, WHERE active and WHERE NOT active are enough; comparing with = TRUE is correct but redundant.
  • You combine conditions with AND, OR and NOT, and you know that mixing AND and OR without parentheses produces incorrect results without raising any error.
  • You can filter by calculated expressions, repeating them in full because the alias doesn't exist yet.
  • You recognise the symptom of nulls: fewer rows than expected, and a filter and its negation that don't add up to the total. The cure, IS NULL, arrives in lesson 04-03.

In the next lesson, DISTINCT and Removing Duplicates, you'll tackle a different problem: when you project only a few columns, repeated rows appear that weren't repeated in the original table. You'll see how to remove them, why DISTINCT acts on the complete combination of the SELECT's columns (and not on the first one, as many people believe) and exactly where it sits in the logical execution order.

◄ Previous
◄
Next ►
►

SQL Course

Module 1: Introduction to SQL

Module 2: Basic SQL queries

Module 3: Working with multiple tables

Module 4: Advanced data filtering

Module 5: Data manipulation

Module 6: Advanced SQL functions

Module 7: Subqueries and nested queries

Module 8: Indexes and performance tuning

Module 9: Transactions and concurrency

Module 10: Advanced topics

Module 11: SQL in practice

Module 12: Final project

The Project | About Us | Contribute | Donations | License
© Copyright 2026. All rights reserved