AND for combining conditions

Concepts covered: Filtering

Rachel's last request narrows on 2 columns at once: Engineering employees earning more than 80000. Both halves, department = 'Engineering' and salary > 80000, are conditions this lesson has already built. AND joins them into 1 test, and a row passes the joined test only when it passes both halves. Watch the 2 tests run against all 8 rows.

SELECT emp_name, department, salary
FROM employees
WHERE department = 'Engineering'
  AND salary > 80000

2 rows survive. Kim is in Engineering but earns 78000, so the row passes the first test but is dropped at the second. Ruiz earns 80000, which already fails > 80000, and works in Sales, so both tests fail. Chen and Patel pass both. The table below traces every row through the 2 tests; the only rows that reach the result are the ones with a pass in both middle columns.

Adding a condition with AND can only shrink the result or leave its size unchanged. 3 conditions pass fewer rows than 2, or the same rows; they never pass more. Append AND hire_date > '2020-01-01' and Chen, hired in 2019, drops out, leaving Patel alone. The order the conditions are written in makes no difference to which rows come back: salary > 80000 AND department = 'Engineering' returns the same 2 rows. Order is for the reader: leading with the condition that names what the report is about, here the department, puts the query's subject on its first line.

Before running it, how many rows does WHERE department = 'Engineering' AND salary > 90000 AND hire_date > '2020-01-01' return from the 8-row employees table?

• Two rows: Chen and Patel
• One row: Chen
• No rows
• One row: Patel

Each condition stands on its own

The most common mistake with AND comes from writing it the way the sentence is spoken. Salary over 80000 and under 95000 becomes salary > 80000 AND < 95000, and the database rejects it, because the text after AND has to be a complete condition, and a complete condition starts with its own column. The correct form repeats the column: salary > 80000 AND salary < 95000, which returns Patel at 88000. The same rule allows any mix of operators on either side: department IN ('Marketing', 'Sales') AND salary >= 70000 pairs a list test with a threshold and returns Osei and Ruiz.

SELECT emp_name, department, salary
FROM employees
WHERE department = 'Engineering'
  AND salary > 80000

Make each of these edits to the original query, restoring it between them. Change the second condition to salary > 60000 and Kim rejoins, 3 rows. Replace the first with department != 'Engineering' and the result is empty, because the only person outside Engineering near the line is Ruiz, who earns exactly 80000 and fails >. Remove the AND line and the query is back to the first section's 3 rows. Each edit shows the same property: a condition added through AND moves the row count in 1 direction, downward.

Assemble the query that lists the name, department and salary of every Sales employee earning at least 60000. The pieces are out of order, and 2 of them do not belong in the query.

The order that holds the query together is SELECT, then FROM, then WHERE, then each further condition on its own AND. The tile AND >= 60000 fails because it names no column, and WHERE employees fails because the table name belongs to FROM. The assembled query returns Ruiz at 80000; Adams at 59000 misses the salary line by 1000 and stays out.

Do

• Write the WHERE condition before the column list, so a query on a big table never runs without one
• Check a text literal's spelling and case against real stored values when a filter returns 0 rows
• Decide whether the boundary row belongs in the result before choosing > or >=
• Reach for IN as soon as a column is compared against 2 or more values
• Repeat the column name in every condition joined by AND

Avoid

• Treat a row count smaller than expected as lost data before reading the WHERE clause
• Wrap a text value in double quotes; most databases read that as a column name
• Compare a numeric threshold against a column that stores its numbers as text
• Write salary > 80000 AND < 95000; the second half needs its own column
• Assume reordering AND conditions changes which rows come back

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.