WHERE clause for filtering rows

Concepts covered: Filtering

Tom wrote the headcount query 6 weeks ago, when the request was a roster of the Engineering team. It has 3 clauses. The first names the columns to show, the second names the table to read, and the third decides who appears. Watch it run over all 8 employees, row by row.

SELECT emp_name, department, salary
FROM employees
WHERE department = 'Engineering'

The database reads the query in a different order from the one it is written in. FROM employees runs first and loads every row, all 8 of them. Then WHERE takes the rows one at a time and asks a single question of each: does the value in department equal the text 'Engineering'? Chen and Patel answer yes and continue, and so does Kim. Osei, Tanaka, Ruiz, Adams and Novak go no further. Only then does SELECT choose the 3 columns to print, so 8 rows go in and 3 come out, each 3 columns wide.

A condition is a question with a yes or no answer

Everything after the word WHERE is one condition: here the column department, compared by the operator = against the value 'Engineering'. The database evaluates the condition once per row and gets back true or false. True forwards the row to the result; false drops it. The condition never looks at 2 rows at once and never remembers a previous row, which is what makes it predictable: the fate of Osei's row depends on Osei's department and nothing else.

SELECT picks columns, WHERE picks rows

A result set has a width and a height. SELECT sets the width: list 2 columns and the result is 2 columns wide, but no choice of columns ever changes how many rows come back. WHERE sets the height: it is the only clause in this query that can turn 8 rows into 3. Tom's report has 3 columns because SELECT named 3, and 3 rows because WHERE let 3 through. The 2 numbers match by coincidence, and that coincidence sent Rachel looking for a lost-data incident.

SELECT

• Chooses which columns appear
• Listing fewer columns narrows the result sideways
• Never changes the number of rows
• SELECT * prints every column of every row that survives

WHERE

• Chooses which rows appear
• A tighter condition narrows the result downward
• Never changes which columns appear
• Leaving it out keeps every row the table holds

The condition is a test, and the test never writes anything. WHERE reads each row, decides whether it passes, and either forwards it to the result or leaves it out of the result. The table on disk holds the same 8 rows whether the query has run or not. IT was right that every record exists, and the report was right that 3 rows match 'Engineering'. Both facts hold at once. The 5 people Rachel could not find were sitting in the table the entire time, failing a test nobody told her about.

SELECT emp_name, department, salary
FROM employees
WHERE department = 'Engineering'

Run it as written and the 3 Engineering rows come back. Delete the whole WHERE line and run again: all 8 appear, because the clause is optional and a query without one returns the table in full. Then put the line back with 'Sales' in place of 'Engineering' and the result is Ruiz and Adams. Only one value changed between those runs, and a different 2 people passed the test.

Rachel's report shows 3 of 8 employees, IT confirms the table holds all 8, and nobody has left the company. What explains the gap?

• 5 records were deleted and later restored, so IT's check happened after the damage was undone.
• SELECT listed 3 columns, which capped the result at 3 rows.
• WHERE department = 'Engineering' tested all 8 rows and forwarded the 3 that matched.
• FROM employees loaded only the Engineering rows because the table is sorted by department.

About this lesson

This section is part of the Filtering: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.