Filtering Data Retrieved

Often you want only the rows that meet a certain condition rather than the whole table. The WHERE clause filters the results of a SELECT query, returning only the rows that match the condition you specify.

Filtering with a condition

Add WHERE after the table name, followed by a condition. Text values are enclosed in single quotes:

SELECT first_name, last_name
FROM customers
WHERE state = 'TX';

This returns only customers in Texas.

Comparison operators

Conditions can use the usual comparisons: = (equal), <> or != (not equal), and <, >, <=, >= for ranges of numbers or dates.

Combining conditions

Use AND, OR, and NOT to build more specific filters:

SELECT first_name, last_name
FROM customers
WHERE state = 'TX' AND city = 'Austin';

Matching patterns and lists

Other useful conditions include:

• LIKE for pattern matching, where % stands for any sequence of characters and _ for a single character: WHERE last_name LIKE 'K%' finds names starting with K.
• IN to match any value in a list: WHERE state IN ('TX','CO').
• BETWEEN to match a range: WHERE id BETWEEN 2 AND 4.

You can combine WHERE with ORDER BY; the filter is applied first, then the surviving rows are sorted.