SELECT and Filtering
/tldr: The most important command: Retrieving specific data from tables.
THE CORE IDEA: DQL and SELECT
The SELECT statement is the workhorse of SQL. It allows you to **query** (read) data from a database. Almost every SQL interaction begins with this command.
Selecting Columns
SELECT * (The Wildcard)
Retrieves ALL columns from the specified table. Convenient, but slow in production.
SELECT * FROM products;
SELECT column1, column2
Retrieves only the named columns. This is faster and best practice.
SELECT product_name, price FROM products;
SELECT DISTINCT
Removes duplicate rows from the result set. Useful for finding unique values.
SELECT DISTINCT category_id FROM products;
Aliasing (AS)
Renames a column or table in the result set for readability.
SELECT price AS retail_cost FROM products;
Filtering with WHERE (The Row Selector)
The WHERE clause applies criteria to rows, deciding which ones make it into your final result.
-
Simple Comparisons
Uses standard operators:
=,>,<,!=(or<>).WHERE price > 100 AND stock_level < 5;
-
The RANGE: BETWEEN
Selects values within a specified, inclusive range (Start and End are included).
WHERE order_date BETWEEN '2025-01-01' AND '2025-03-31';
-
Handling NULL Values
You must use
IS NULLorIS NOT NULL, not the equals operator.WHERE manager_id IS NULL;
-
Pattern Matching: LIKE
Used for fuzzy text searches using wildcards:
%(0 or more characters) and_(exactly 1 character).WHERE name LIKE 'A%'; -- Starts with 'A'
-
The List Check: IN
Used to specify multiple possible values for a column.
WHERE state IN ('CA', 'NY', 'TX'); -
Logical Operators: AND / OR / NOT
Combine multiple conditions. Use parentheses for clarity.
WHERE NOT (country = 'US' OR country = 'CA');
Comprehensive Filtering Example
-- Goal: Find unique active customers who joined in the last 6 months,
-- live in a specific set of regions, and have an email on file.
SELECT DISTINCT
customer_id,
first_name,
email
FROM
customers
WHERE
join_date BETWEEN DATE('now', '-6 months') AND DATE('now', 'localtime') -- Range check (SQLite syntax for example)
AND region_id IN (1, 3, 5, 8) -- List check
AND email IS NOT NULL -- NULL check
AND last_name LIKE 'S%n' -- Pattern check: Starts with 'S' and ends with 'n'
ORDER BY
first_name;
The order of clauses is always fixed: SELECT, FROM, WHERE, then optional clauses like ORDER BY.
You're ready to query.