SQL SELECT & WHERE — Filtering Rows the Right Way

The shape of every SQL query

Every SQL query you will ever write starts with SELECT. It tells the database which columns to return and, via the clauses that follow, which rows to include, how to sort them, and how many to fetch. Understanding what happens in which order matters — the database does not execute a query top to bottom.

The logical order is: FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. That means column aliases defined in SELECT are invisible to WHERE — a common source of "column does not exist" errors.

SELECT — choosing columns

-- All columns (avoid in application code)
SELECT * FROM orders;

-- Named columns with aliases
SELECT
    id,
    customer_id,
    total_amount      AS total,
    created_at        AS ordered_at
FROM orders;

-- Computed column
SELECT
    product_name,
    unit_price,
    quantity,
    unit_price * quantity AS line_total
FROM order_items
WHERE order_id = 1042;

Prefer named columns over SELECT * in application code — a schema change (adding or reordering a column) silently breaks queries that depend on position.

WHERE — filtering rows

WHERE is evaluated against the raw table rows before any aggregation. It accepts any boolean expression, including comparisons, ranges, pattern matches, and NULL checks.

-- Equality and inequality
SELECT * FROM products WHERE category = 'Electronics';
SELECT * FROM orders   WHERE status <> 'cancelled';

-- Range with BETWEEN (inclusive on both ends)
SELECT * FROM orders WHERE total_amount BETWEEN 100 AND 500;

-- Multiple conditions
SELECT * FROM users
WHERE country = 'US'
  AND account_type = 'premium'
  AND created_at >= '2026-01-01';

NULL — the value that breaks comparisons

NULL means unknown. Any comparison with NULL — = NULL, <> NULL, > NULL — returns UNKNOWN, which WHERE treats as false. The only correct way to test for NULL is IS NULL or IS NOT NULL.

-- WRONG: never returns any rows (NULL = NULL is UNKNOWN, not TRUE)
SELECT * FROM orders WHERE coupon_code = NULL;

-- CORRECT
SELECT * FROM orders WHERE coupon_code IS NULL;       -- no coupon applied
SELECT * FROM orders WHERE coupon_code IS NOT NULL;   -- coupon present

This also affects WHERE col <> 'value' — rows where col is NULL are silently excluded. If you want them included, add OR col IS NULL.

IN and NOT IN

IN is a clean shorthand for multiple OR conditions. NOT IN has a dangerous edge case: if the value list contains a NULL, NOT IN returns zero rows — because x <> NULL is always UNKNOWN.

-- IN: orders in a set of statuses
SELECT * FROM orders WHERE status IN ('pending', 'processing', 'on_hold');

-- NOT IN safe version (no NULLs in the list)
SELECT * FROM products WHERE category_id NOT IN (3, 7, 12);

-- If the list comes from a subquery that may return NULLs, use NOT EXISTS
SELECT * FROM products p
WHERE NOT EXISTS (
    SELECT 1 FROM discontinued d WHERE d.product_id = p.id
);

LIKE — pattern matching

LIKE matches text with two wildcards: % (any sequence of characters) and _ (exactly one character).

-- Products starting with 'Apple'
SELECT * FROM products WHERE product_name LIKE 'Apple%';

-- Email addresses at a specific domain
SELECT * FROM users WHERE email LIKE '%@acme.com';

-- Exactly 5-character SKU codes
SELECT * FROM products WHERE sku LIKE '_____';

A leading % (e.g., LIKE '%word') forces a full table scan — the index on that column cannot be used. For full-text search, use a dedicated full-text index or a search service instead.

DISTINCT — removing duplicates

SELECT DISTINCT removes duplicate rows from the result. It operates on the combination of all selected columns.

-- Which countries have placed orders?
SELECT DISTINCT country FROM customers WHERE created_at >= '2026-01-01';

-- Which (customer_id, product_id) pairs have repeat purchases?
SELECT DISTINCT customer_id, product_id FROM order_items;

DISTINCT is processed after all rows are gathered and can be expensive on large result sets. If you find yourself using it to hide a join that multiplies rows, fix the join instead.

Combining conditions with AND, OR, NOT

Operator precedence: NOT binds tightest, then AND, then OR. Missing parentheses cause subtle logic bugs.

-- BUG: OR binds last, so this returns ALL premium US users
-- plus ALL users with status = 'trial' regardless of country
SELECT * FROM users
WHERE country = 'US' AND account_type = 'premium' OR status = 'trial';

-- CORRECT: parentheses make the intent explicit
SELECT * FROM users
WHERE (country = 'US' AND account_type = 'premium')
   OR status = 'trial';

Recap

SELECT names the output columns; WHERE filters the rows before they reach the output. Key rules: use IS NULL / IS NOT NULL for NULL checks; avoid NOT IN when the value list might contain NULLs; prefer NOT EXISTS instead. A leading % in LIKE kills index use — reserve it for small tables or use full-text search for large ones. Parenthesise complex AND/OR conditions to make precedence explicit.

More ways to practice