Writing SQL · Chapter 7

Adding conditions

11 min read · 12 questions

So far our queries returned every row of a table. In practice, you almost always want a subset: one customer's sales, the products of one category, the orders of one month. Filtering rows makes the result readable, and the query faster, since the database processes less data.

After SELECT, FROM, DISTINCT and AS, here is the WHERE clause.

WHERE

WHERE keeps only the rows that meet a condition. It is written after FROM:

SELECT CustomerID, ProductID, "NOS (COPA)", Date
FROM perform21
WHERE Date = '2021-08-01'

This query displays the sales of August 2021 (in the perform tables, each month is dated by its first day).

Comparison operators

-- Question 7.1: write your query here
SELECT 

SELECT *
FROM perform24
WHERE "NOS (COPA)" < 0

LIKE: searching text

LIKE compares a value with a pattern, a bit like Ctrl + F in a browser. Two special characters make up the pattern:

• % stands for any sequence of characters, including none;
• _ stands for exactly one character.

Take a fictitious users table:

SELECT *
FROM users
WHERE Name LIKE 'A%'

This query returns Alice and Amaury, the two names starting with A.

On our data, this query finds the customers whose name ends with S:

SELECT CustomerID, CustomerName, StrategicCustomer
FROM customer
WHERE CustomerName LIKE '%S'

SELECT ProductID, Designation
FROM product
WHERE Designation LIKE '%Décaféiné%'

-- Question 7.3: write your query here
SELECT 

SELECT CustomerID, CustomerName
FROM customer
WHERE CustomerName LIKE '%E'

SELECT CustomerName
FROM customer
WHERE CustomerName LIKE "D%"

-- Displays the names of customers starting with the letter D
SELECT CustomerName
FROM customer
WHERE CustomerName LIKE 'D%'

BETWEEN … AND …

BETWEEN selects the values between two bounds, bounds included. It works with numbers and dates; dates are written as 'YYYY-MM-DD'.

SELECT CustomerID, ProductID, "VolKilo (COPA)", "NOS (COPA)", Date
FROM perform21
WHERE Date BETWEEN '2021-09-01' AND '2021-12-01'

This query returns the sales from September to December 2021: both months named are part of the result.

-- Question 7.5: write your query here
SELECT 

SELECT CustomerID, ProductID, Date, "GP (COPA)"
FROM perform25
WHERE Date BETWEEN '2025-01-01' AND '2025-03-01'
  AND "GP (COPA)" BETWEEN 50000 AND 100000

IN: a list of values

IN saves you from chaining equalities. These two filters are equivalent ('Hors domicile' means food service):

SELECT CustomerID, CustomerName, Channel
FROM customer
WHERE Channel IN ('E-commerce', 'Hors domicile')

SELECT CustomerID, CustomerName, Channel
FROM customer
WHERE Channel = 'E-commerce' OR Channel = 'Hors domicile'

-- Question 7.6: write your query here
SELECT 

SELECT CustomerName, KeyAccount
FROM customer
WHERE KeyAccount IN ('Paul', 'Ali', 'Inès')

IS NULL: missing values

NULL means "unknown value". It is neither zero nor empty text, and you do not compare it with =: WHERE SubCategory = NULL never returns anything. Write IS NULL or IS NOT NULL.

SELECT *
FROM customer
WHERE CustomerName IS NULL

Customers 17 and 18 have no information at all: these rows should be fixed at the source.

AND, OR and NOT

AND requires all conditions to be true. OR is satisfied by just one. NOT negates a condition.

SELECT *
FROM customer
WHERE CustomerName LIKE 'D%' AND StrategicCustomer = 'Yes'

SELECT *
FROM customer
WHERE CustomerName LIKE 'D%' OR StrategicCustomer = 'Yes'

-- Question 7.8: write your query here
SELECT 

SELECT *
FROM product
WHERE SubCategory IS NULL
  AND Category = 'Others'

-- Question 7.9: write your query here
SELECT 

SELECT ProductID, Category, SubCategory, Brand
FROM product
WHERE SubCategory IS NULL
   OR Category = 'Others'

Brackets and precedence

When you mix AND and OR, the DBMS evaluates the ANDs first, like multiplication before addition. Brackets impose another order, exactly as in arithmetic.

-- Strong capsules OR organic products (of any category): 80 rows
SELECT ProductID, Category, Intensity, IsOrganic
FROM product
WHERE Category = 'Capsules' AND Intensity >= 10 OR IsOrganic = 'Yes'

-- Capsules that are strong OR organic: 25 rows
SELECT ProductID, Category, Intensity, IsOrganic
FROM product
WHERE Category = 'Capsules' AND (Intensity >= 10 OR IsOrganic = 'Yes')

-- Question 7.10: write your query here
SELECT 

SELECT *
FROM perform21
WHERE Date BETWEEN '2021-09-01' AND '2021-12-01'
  AND "GP (COPA)" / "NOS (COPA)" > 0.3

-- Question 7.11: write your query here
SELECT 

SELECT ProductID, Designation, Intensity
FROM product
WHERE Category = 'Capsules'
  AND Status = 'Active'
  AND Intensity >= 10

Writing order and execution order

You write the clauses in this order:

1. SELECT: the columns to display;
2. FROM: the source table;
3. WHERE: the row filter.

But the DBMS executes them in a different order:

1. FROM: it identifies the table;
2. WHERE: it keeps the rows that meet the condition;
3. SELECT: it keeps only the requested columns.

This difference explains a classic mistake: an alias created in the SELECT (with AS) does not exist yet when WHERE is evaluated. In most DBMSs, you therefore cannot filter on an alias.

-- In PostgreSQL or SQL Server this query fails: margin does not exist yet during the WHERE.
-- DuckDB accepts it for convenience, but better not to get used to it.
SELECT *, "GP (COPA)" / "NOS (COPA)" AS margin
FROM perform21
WHERE margin > 0.3

-- Question 7.12: write your query here
SELECT 

SELECT CustomerID, ProductID, Date, "VolKilo (COPA)", "NOS (COPA)"
FROM perform25
WHERE CustomerID = 12
  AND "NOS (COPA)" < 0