Filtering Data

WHERE keeps the rows whose condition is true and silently drops the rest, including rows where the condition is NULL. Every example here filters one products table.

-- setup.sql
CREATE TABLE products (
    id        SERIAL PRIMARY KEY,
    name      TEXT NOT NULL,
    category  TEXT NOT NULL,
    price     NUMERIC(8, 2) NOT NULL,
    supplier  TEXT,                 -- NULL when unknown
    added_at  TIMESTAMP NOT NULL
);

INSERT INTO products (name, category, price, supplier, added_at) VALUES
    ('Hammer',       'Tools',  24.00, 'Acme',   '2024-01-05 09:00'),
    ('Screwdriver',  'Tools',  12.50, 'Acme',   '2024-01-31 15:30'),
    ('Rake',         'Garden', 18.00, NULL,     '2024-01-31 00:00'),
    ('Hose 20% off', 'Garden', 30.00, 'GreenCo','2024-02-02 11:00'),
    ('Trowel',       'Garden',  9.99, 'GreenCo','2024-02-10 08:15'),
    ('Drill',        'Tools',  89.00, NULL,     '2024-03-01 10:00');

$ psql -d sqldemo -f setup.sql
CREATE TABLE
INSERT 0 6

Comparison Operators

=, <>, <, <=, >, and >= work on numbers, text, and dates alike.

-- compare.sql
SELECT name, price FROM products WHERE price >= 24 ORDER BY price;
SELECT count(*) AS exact_case FROM products WHERE name = 'hammer';

$ psql -d sqldemo -f compare.sql
     name     | price 
--------------+-------
 Hammer       | 24.00
 Hose 20% off | 30.00
 Drill        | 89.00
(3 rows)

 exact_case 
------------
          0
(1 row)

The first query includes the 24.00 hammer because >= is inclusive. The second finds nothing: text comparison is case-sensitive, and the stored name is Hammer.

AND, OR, and Missing Parentheses

This query is meant to find cheap items (under 20) in either Tools or Garden.

-- precedence.sql
SELECT name, category, price
FROM products
WHERE category = 'Tools' OR category = 'Garden' AND price < 20
ORDER BY name;

$ psql -d sqldemo -f precedence.sql
    name     | category | price 
-------------+----------+-------
 Drill       | Tools    | 89.00
 Hammer      | Tools    | 24.00
 Rake        | Garden   | 18.00
 Screwdriver | Tools    | 12.50
 Trowel      | Garden   |  9.99
(5 rows)

The 89.00 drill and the 24.00 hammer slipped through. AND binds tighter than OR, so the condition was read as Tools OR (Garden AND price < 20): every tool matched regardless of price. Parentheses fix it.

-- precedence_fixed.sql
SELECT name, category, price
FROM products
WHERE (category = 'Tools' OR category = 'Garden') AND price < 20
ORDER BY name;

$ psql -d sqldemo -f precedence_fixed.sql
    name     | category | price 
-------------+----------+-------
 Rake        | Garden   | 18.00
 Screwdriver | Tools    | 12.50
 Trowel      | Garden   |  9.99
(3 rows)

Now only the three items under 20 remain. Whenever AND and OR share a WHERE, add the parentheses even if you think the default is right; the next reader will not have to check.

BETWEEN and the End-of-Day Trap

BETWEEN includes both ends. With timestamps, “both ends” is a single instant.

-- between.sql
SELECT name, added_at
FROM products
WHERE added_at BETWEEN '2024-01-01' AND '2024-01-31'
ORDER BY added_at;

$ psql -d sqldemo -f between.sql
  name  |      added_at       
--------+---------------------
 Hammer | 2024-01-05 09:00:00
 Rake   | 2024-01-31 00:00:00
(2 rows)

The screwdriver was added on January 31 at 15:30, yet it is missing. '2024-01-31' becomes the timestamp 2024-01-31 00:00:00, so BETWEEN stops at midnight. The rake, added exactly at midnight, squeaked in. A half-open range covers the whole month.

-- between_fixed.sql
SELECT name, added_at
FROM products
WHERE added_at >= '2024-01-01' AND added_at < '2024-02-01'
ORDER BY added_at;

$ psql -d sqldemo -f between_fixed.sql
    name     |      added_at       
-------------+---------------------
 Hammer      | 2024-01-05 09:00:00
 Rake        | 2024-01-31 00:00:00
 Screwdriver | 2024-01-31 15:30:00
(3 rows)

>= start AND < next_start is correct for any timestamp precision, and the same pattern works for weeks, months, and years.

IN and the NOT IN NULL Trap

IN is shorthand for several = tests joined by OR.

-- in_list.sql
SELECT name, supplier FROM products
WHERE supplier IN ('Acme', 'GreenCo')
ORDER BY name;

$ psql -d sqldemo -f in_list.sql
     name     | supplier 
--------------+----------
 Hammer       | Acme
 Hose 20% off | GreenCo
 Screwdriver  | Acme
 Trowel       | GreenCo
(4 rows)

The rake and the drill have no supplier, so neither 'Acme' nor 'GreenCo' matches them. Now flip it: which products come from a supplier that is not in a list? Here the list comes from a subquery that happens to contain a NULL.

-- not_in_null.sql
SELECT count(*) AS not_in_result
FROM products
WHERE name NOT IN (SELECT supplier FROM products);

SELECT count(*) AS not_exists_result
FROM products p
WHERE NOT EXISTS (SELECT 1 FROM products s WHERE s.supplier = p.name);

$ psql -d sqldemo -f not_in_null.sql
 not_in_result 
---------------
             0
(1 row)

 not_exists_result 
-------------------
                 6
(1 row)

No product name is also a supplier name, so the honest answer is 6, and NOT EXISTS gives it. NOT IN returns 0. The subquery’s list contains NULL, so every row’s test includes name <> NULL, which is unknown, and the whole condition can never be true. Prefer NOT EXISTS whenever the list comes from a nullable column.

LIKE and ILIKE

% matches any run of characters and _ matches exactly one.

-- like.sql
SELECT name FROM products WHERE name LIKE '%er' ORDER BY name;
SELECT name FROM products WHERE name ILIKE 'd%' ORDER BY name;
SELECT name FROM products WHERE name LIKE '%20\%%' ORDER BY name;

$ psql -d sqldemo -f like.sql
    name     
-------------
 Hammer
 Screwdriver
(2 rows)

 name  
-------
 Drill
(1 row)

     name     
--------------
 Hose 20% off
(1 row)

'%er' anchors at the end, so it finds names ending in “er”. ILIKE 'd%' ignores case and matches Drill. To match a literal percent sign, escape it: \% means “a real %”, so the third query finds only the hose.

IS NULL, COALESCE, and NULLIF

IS NULL finds missing values; COALESCE replaces them; NULLIF creates them on purpose.

-- null_funcs.sql
SELECT name, COALESCE(supplier, '(unknown)') AS supplier
FROM products WHERE supplier IS NULL ORDER BY name;

SELECT 10 / NULLIF(0, 0) AS safe_div;
SELECT 10 / 0 AS unsafe_div;

$ psql -d sqldemo -f null_funcs.sql
 name  | supplier  
-------+-----------
 Drill | (unknown)
 Rake  | (unknown)
(2 rows)

 safe_div 
----------
         
(1 row)

psql:null_funcs.sql:6: ERROR:  division by zero

COALESCE returns its first non-NULL argument, so reports can show a label instead of a blank. NULLIF(a, b) returns NULL when a = b, which turns a zero divisor into NULL and makes the division NULL instead of an error. The last query shows the error it avoided.

Practice

1. List Garden products that cost between 10 and 20, inclusive.

-- practice_garden_range.sql
SELECT name, price FROM products
WHERE category = 'Garden' AND price BETWEEN 10 AND 20;

$ psql -d sqldemo -f practice_garden_range.sql
 name | price 
------+-------
 Rake | 18.00
(1 row)

2. Find products added in February 2024 using a half-open range.

-- practice_february.sql
SELECT name, added_at FROM products
WHERE added_at >= '2024-02-01' AND added_at < '2024-03-01'
ORDER BY added_at;

$ psql -d sqldemo -f practice_february.sql
     name     |      added_at       
--------------+---------------------
 Hose 20% off | 2024-02-02 11:00:00
 Trowel       | 2024-02-10 08:15:00
(2 rows)

3. Count products whose supplier is not Acme, counting unknown suppliers too.

-- practice_not_acme.sql
SELECT count(*) FILTER (WHERE supplier <> 'Acme')               AS naive,
       count(*) FILTER (WHERE supplier IS DISTINCT FROM 'Acme') AS correct
FROM products;

$ psql -d sqldemo -f practice_not_acme.sql
 naive | correct 
-------+---------
     2 |       4
(1 row)

4. Find every product whose name has exactly five characters.

-- practice_five_chars.sql
SELECT name FROM products WHERE name LIKE '_____' ORDER BY name;

$ psql -d sqldemo -f practice_five_chars.sql
 name  
-------
 Drill
(1 row)