SQL · Lesson 2 of 7

SELECT and WHERE

Read columns and filter rows with conditions, patterns and NULL checks.

• Beginner
• 14 min read
• 3 objectives

Before this lessonLesson 1: Databases and SQL Basics

SELECT chooses columns; WHERE chooses rows. Combining them lets you pull exactly the data you need, and nothing else.

The two most-used clauses

If you learn only two things in SQL, learn these. SELECT chooses which columns you see. WHERE chooses which rows you see. Almost every real query, from a small report to a complex dashboard, is built by adding to this pair.

Choosing columns

SELECT * FROM customers;               -- every column
SELECT name, age FROM customers;        -- only some
SELECT name AS customer FROM customers; -- rename with an alias
SELECT DISTINCT country FROM customers; -- remove duplicates

Avoid SELECT * in application code. It fetches more than you need and breaks when columns are added or reordered.

Filtering with WHERE

SELECT name FROM customers
WHERE age >= 30;

name
Ada
Grace

Comparison operators: = <> < > <= >= (note that equality is a single =, and not-equal is <> or !=). Combine tests with AND, OR and NOT, and use parentheses to be explicit, since AND binds tighter than OR.

SELECT * FROM customers
WHERE (country = 'UK' OR country = 'US')
  AND age > 30;

IN, BETWEEN and LIKE

SELECT * FROM customers WHERE country IN ('UK', 'US', 'FI');
SELECT * FROM orders    WHERE total BETWEEN 50 AND 150;   -- inclusive
SELECT * FROM customers WHERE name LIKE 'G%';             -- starts with G
SELECT * FROM customers WHERE name LIKE '_da';            -- one char then 'da'

In LIKE, % matches any run of characters and _ matches exactly one. Text values use single quotes; double quotes are for identifiers in standard SQL.

NULL is not a value

NULL means "unknown or missing". Any comparison with NULL is unknown, so WHERE phone = NULL matches nothing. Use IS NULL and IS NOT NULL.

SELECT * FROM customers WHERE phone IS NULL;
SELECT name, COALESCE(phone, 'n/a') AS phone FROM customers;

Comparison operators

SELECT name, age FROM customers WHERE age >= 30 AND country <> 'FI'

name  | age
------+----
Ada   | 36
Grace | 45

The operators are =, <> (or !=), <, >, <=, >=. Note that SQL uses a single = to compare, unlike most programming languages. Text values go in single quotes; numbers do not.

AND, OR and parentheses

AND needs both sides true, OR needs one. When you mix them, use parentheses. Without them AND is evaluated first, which often is not what you meant.

SELECT name, country, age FROM customers WHERE (country = 'UK' OR country = 'US') AND age > 40

name  | country | age
------+---------+----
Grace | US      | 45

Patterns with LIKE

LIKE searches text. % matches any number of characters, and _ matches exactly one. Use it for "starts with", "ends with" and "contains" searches.

SELECT name FROM customers WHERE name LIKE 'G%'

name
-----
Grace

SELECT name FROM customers WHERE name LIKE '%n%'

name
-----
Linus

Lists and ranges: IN and BETWEEN

SELECT name, country FROM customers WHERE country IN ('UK', 'FI')

name  | country
------+--------
Ada   | UK
Linus | FI

SELECT name, age FROM customers WHERE age BETWEEN 30 AND 40

name | age
-----+----
Ada  | 36

BETWEEN includes both ends. IN is a tidy way to write several ORs on the same column.

Missing values: IS NULL

NULL means "unknown", not zero and not empty text. You cannot test it with =; you must ask IS NULL. Here we add a customer with no age to see the difference.

SELECT name FROM people WHERE age IS NULL

name
----
Zed

SELECT name FROM people WHERE age = NULL

name
----

The second query returns nothing at all: age = NULL is never true, because "unknown equals unknown" is itself unknown. This is one of the most common SQL bugs.

Key takeaways

• SELECT picks columns; WHERE picks rows.
• Combine conditions with AND/OR and use parentheses when mixing them.
• LIKE with % and _ searches text; IN and BETWEEN shorten lists and ranges.
• Test for missing values with IS NULL, never = NULL.

-- Write your solution here