Home/Learn/MySQL/SELECT Queries & Filtering

SELECT Queries & Filtering

Beginner

Fundamentals

SELECT with WHERE, LIKE, BETWEEN, IN, IS NULL, and REGEXP filters rows; column aliases, DISTINCT, and case-insensitive string comparison (COLLATION) are foundational query skills.

Overview

SELECT is the most used SQL statement. Its clauses execute in a specific logical order: FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT. Understanding this order explains why you cannot use a SELECT alias in a WHERE clause (it hasn't been evaluated yet). MySQL's WHERE clause supports rich filtering: comparison operators, LIKE wildcards, BETWEEN, IN lists, IS NULL, REGEXP, and subqueries. EXPLAIN on every important query tells you whether indexes are being used.

Filtering with WHERE

WHERE filters rows before aggregation. Combine conditions with AND, OR, NOT. Operators: =, <>, <, >, BETWEEN (inclusive), IN, IS NULL, LIKE (% = any chars, _ = one char), REGEXP for patterns.

SQL — WHERE clause filtering examples

-- Basic comparisons
SELECT id, name, price
FROM products
WHERE price BETWEEN 10.00 AND 50.00   -- inclusive
  AND category IN ('Books', 'Electronics', 'Toys')
  AND discontinued = FALSE;

-- NULL handling — always use IS NULL / IS NOT NULL, never = NULL
SELECT * FROM orders WHERE shipped_at IS NULL;        -- not yet shipped
SELECT * FROM orders WHERE shipped_at IS NOT NULL;    -- already shipped

-- LIKE — % matches zero or more chars; _ matches exactly one
SELECT * FROM customers WHERE email LIKE '%@gmail.com';   -- ends with
SELECT * FROM products WHERE sku LIKE 'PROD-___-2024';    -- 3-char middle

-- REGEXP — full regex support
SELECT * FROM products WHERE name REGEXP '^(iPhone|iPad|MacBook)';

-- Subquery in WHERE
SELECT * FROM orders
WHERE customer_id IN (
    SELECT id FROM customers WHERE tier = 'GOLD'
);

-- EXPLAIN — check if index is used
EXPLAIN SELECT * FROM orders WHERE customer_id = 42 AND status = 'PENDING';
-- key: NULL means no index → consider adding one

DISTINCT, ORDER BY, LIMIT

DISTINCT removes duplicate rows from the result. ORDER BY sorts; multiple columns sort hierarchically. LIMIT restricts row count; LIMIT offset, count paginates (but OFFSET pagination is slow on large tables — use keyset/cursor pagination instead).

SQL — DISTINCT, ORDER BY, LIMIT and keyset pagination

-- DISTINCT — deduplicate rows
SELECT DISTINCT category FROM products ORDER BY category;

-- Multiple-column sort
SELECT id, name, price
FROM products
ORDER BY category ASC, price DESC;   -- sort by category first, then price

-- LIMIT — top N
SELECT id, name, total
FROM orders
ORDER BY total DESC
LIMIT 10;                            -- top 10 largest orders

-- OFFSET pagination — simple but slow for large offsets
SELECT id, name, price
FROM products
ORDER BY id
LIMIT 20 OFFSET 200;                 -- page 11 (0-indexed)
-- Problem: OFFSET 10000 scans 10,000 rows and discards them — O(N)

-- Keyset pagination — efficient alternative
SELECT id, name, price
FROM products
WHERE id > 12345                     -- last id from previous page
ORDER BY id
LIMIT 20;                            -- O(log N) with index on id

Column Aliases & Computed Columns

AS creates an alias for the result column. Aliases are NOT available in WHERE (evaluated before SELECT) but ARE available in ORDER BY and HAVING. Use subqueries or CTEs to reference computed values in filters.

SQL — column aliases and CTEs

-- Column alias — available in ORDER BY
SELECT
    CONCAT(first_name, ' ', last_name) AS full_name,
    total * 1.18                      AS total_with_gst,
    YEAR(created_at)                  AS order_year
FROM orders
ORDER BY total_with_gst DESC;        -- alias OK in ORDER BY

-- Alias NOT available in WHERE — use the expression directly
-- ✗ WHERE total_with_gst > 1000   -- error: unknown column
-- ✓ WHERE total * 1.18 > 1000     -- repeat expression

-- CTE (Common Table Expression) — reference computed value cleanly
WITH order_totals AS (
    SELECT
        id,
        customer_id,
        total * 1.18 AS total_with_gst
    FROM orders
    WHERE status = 'DELIVERED'
)
SELECT * FROM order_totals
WHERE total_with_gst > 1000
ORDER BY total_with_gst DESC;

Key Points to Remember

1Logical execution order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT.
2Always use IS NULL / IS NOT NULL — never = NULL (always returns NULL, not true/false).
3LIKE uses % (any chars) and _ (one char); REGEXP supports full patterns.
4DISTINCT deduplicates; ORDER BY accepts multiple columns; LIMIT restricts rows.
5Column aliases are available in ORDER BY/HAVING but NOT in WHERE — use the expression.
6Keyset (cursor) pagination is far more efficient than OFFSET for large pages.

Interview Questions

Why can't you use a SELECT alias in the WHERE clause?

MediumAmazon

What is the difference between WHERE and HAVING?

EasyTCS

What is the difference between OFFSET pagination and keyset pagination?

HardLinkedIn

How do you check for NULL values in a WHERE clause?

EasyInfosys

What does EXPLAIN show and how do you use it to diagnose a slow query?

MediumFlipkart

Ask Aria about SELECT Queries & Filtering

Your personal AI tutor — ask anything about this concept

Revision Status

Personal Notes

Discussion

Loading discussion…

DML — INSERT, UPDATE, DELETE

JOINs — INNER, LEFT, RIGHT, CROSS