SQL Execution Order, SELECT, WHERE, and Every Filtering Clause Explained with Real-Life Analogies

Every SQL tutorial starts with SELECT * FROM table. But nobody explains WHY the database ignores the order you write and executes in a completely different sequence. Nobody explains why your alias works in ORDER BY but fails in WHERE. Nobody explains why NOT IN silently returns zero rows when NULLs are present.

This post fixes that. We start with the execution order — the single concept that makes everything else in SQL click. Then we cover every filtering clause with examples, edge cases, and the traps that catch even experienced engineers.

Think of a SQL query like ordering food at a restaurant drive-through. You SPEAK in one order: “I want a Big Mac, from the lunch menu, no pickles, supersized.” But the kitchen PROCESSES in a different order: first they check the menu (FROM), then check if the item exists and apply customizations (WHERE), then prepare it (SELECT), then bag it in order (ORDER BY). Understanding the kitchen’s order is the key to understanding SQL.

Table of Contents

• The SQL Execution Order (Read This First)
• Why Execution Order Matters — Five Confusions Solved
• The Sample Data
• SELECT — Choosing What to Return
• FROM — The Starting Point
• WHERE — Filtering Individual Rows
• Comparison Operators
• Logical Operators: AND, OR, NOT
• The AND/OR Precedence Trap
• BETWEEN — Range Filtering
• IN — Matching a List of Values
• NOT IN — The NULL Trap That Catches Everyone
• LIKE — Pattern Matching with Wildcards
• IS NULL and IS NOT NULL
• EXISTS and NOT EXISTS
• ORDER BY — Sorting the Results
• DISTINCT — Removing Duplicates
• TOP and LIMIT — Restricting Row Count
• OFFSET/FETCH — Pagination
• Combining Multiple Clauses
• Common Mistakes
• Interview Questions
• Wrapping Up

The SQL Execution Order (Read This First)

You WRITE SQL in this order:

SELECT department, COUNT(*) AS emp_count    -- 5th to execute
FROM employees                              -- 1st to execute
WHERE salary > 50000                        -- 2nd to execute
GROUP BY department                         -- 3rd to execute
HAVING COUNT(*) > 5                         -- 4th to execute
ORDER BY emp_count DESC                     -- 6th to execute

But the database EXECUTES in this order:

Step 1:  FROM        → "Open the employees table — here are all the rows"
Step 2:  WHERE       → "Remove rows where salary <= 50000"
Step 3:  GROUP BY    → "Group the remaining rows by department"
Step 4:  HAVING      → "Remove groups with 5 or fewer members"
Step 5:  SELECT      → "Return only department and count columns"
Step 6:  DISTINCT    → "Remove duplicate rows (if specified)"
Step 7:  ORDER BY    → "Sort by emp_count descending"
Step 8:  TOP/LIMIT   → "Return only the first N rows"

Real-life analogy: A librarian filling your book request

You hand the librarian a request slip that says: “I want the title and author (SELECT) from the Science section (FROM), only books published after 2020 (WHERE), grouped by genre (GROUP BY), only genres with more than 10 books (HAVING), sorted by title (ORDER BY).”

The librarian does NOT read your slip top to bottom. They: 1. Go to the Science section (FROM) 2. Pull out books published after 2020 (WHERE) 3. Sort them into genre piles (GROUP BY) 4. Remove piles with 10 or fewer books (HAVING) 5. Write down the title and author from each pile (SELECT) 6. Sort the list by title (ORDER BY)

Same request. Different processing order. Understanding the librarian’s order is understanding SQL.

Why Execution Order Matters — Five Confusions Solved

Confusion 1: “Why can’t I use my alias in WHERE?”

-- ❌ FAILS
SELECT salary * 12 AS annual_salary FROM employees
WHERE annual_salary > 100000;
-- ERROR: 'annual_salary' is not recognized

-- WHY: WHERE (Step 2) runs BEFORE SELECT (Step 5)
-- The alias 'annual_salary' doesn't exist yet when WHERE executes

-- ✅ FIX: Repeat the expression
SELECT salary * 12 AS annual_salary FROM employees
WHERE salary * 12 > 100000;

Confusion 2: “Why CAN I use my alias in ORDER BY?”

-- ✅ WORKS
SELECT salary * 12 AS annual_salary FROM employees
ORDER BY annual_salary DESC;
-- ORDER BY (Step 7) runs AFTER SELECT (Step 5), so the alias exists

Confusion 3: “Why can’t I use WHERE with COUNT?”

-- ❌ FAILS
SELECT department, COUNT(*) FROM employees
WHERE COUNT(*) > 5 GROUP BY department;
-- ERROR: Cannot use aggregate in WHERE

-- WHY: WHERE (Step 2) runs BEFORE GROUP BY (Step 3)
-- COUNT doesn't exist until AFTER grouping

-- ✅ FIX: Use HAVING (runs after GROUP BY)
SELECT department, COUNT(*) AS emp_count FROM employees
GROUP BY department HAVING COUNT(*) > 5;

Confusion 4: “Why does DISTINCT make my query slow?”

SELECT DISTINCT city FROM employees;
-- DISTINCT (Step 6) runs AFTER SELECT (Step 5)
-- It must compare EVERY row in the result — expensive on large datasets

Confusion 5: “Why does my SELECT column not work with GROUP BY?”

-- ❌ FAILS
SELECT department, first_name, COUNT(*) FROM employees
GROUP BY department;
-- ERROR: first_name is not in GROUP BY or an aggregate

-- WHY: GROUP BY collapses rows. If department='Engineering' has 50 names,
-- which first_name should SQL pick? It cannot decide — hence the error.

-- ✅ FIX: Add to GROUP BY or use an aggregate
SELECT department, COUNT(*) AS emp_count FROM employees GROUP BY department;
-- Or:
SELECT department, MAX(first_name) AS sample_name, COUNT(*) FROM employees GROUP BY department;

The Sample Data

We will use this data throughout all three SQL posts:

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    department VARCHAR(50),
    city VARCHAR(50),
    country VARCHAR(50),
    salary INT,
    hire_date DATE,
    manager_id INT NULL
);

INSERT INTO employees VALUES
(1001, 'Naveen', 'Vuppula', 'naveen@email.com', 'Engineering', 'Toronto', 'Canada', 105000, '2022-01-15', NULL),
(1002, 'Shrey', 'Patil', 'shrey@email.com', 'Engineering', 'Toronto', 'Canada', 95000, '2023-03-20', 1001),
(1003, 'Vrushab', 'Sharma', 'vrushab@email.com', 'Engineering', 'Vancouver', 'Canada', 92000, '2023-06-10', 1001),
(1004, 'Vishnu', 'Kumar', 'vishnu@email.com', 'Analytics', 'Ottawa', 'Canada', 91000, '2023-09-01', NULL),
(1005, 'Ravi', 'Desai', 'ravi@email.com', 'Analytics', 'Calgary', 'Canada', 85000, '2024-01-05', 1004),
(1006, 'Priya', 'Mehta', 'priya@email.com', 'Analytics', 'Toronto', 'Canada', 85000, '2023-07-15', 1004),
(1007, 'Anita', 'Singh', 'anita@email.com', 'Sales', 'Mumbai', 'India', 78000, '2024-03-15', NULL),
(1008, 'Deepak', 'Joshi', 'deepak@email.com', 'Sales', 'Delhi', 'India', 82000, '2024-05-01', 1007),
(1009, 'Kavya', 'Reddy', NULL, 'Sales', 'Pune', 'India', 78000, '2024-02-10', 1007),
(1010, 'Manoj', 'Tiwari', 'manoj@email.com', 'Sales', NULL, 'India', 75000, '2023-11-20', 1007);

Notice the intentional issues: Kavya has NULL email, Manoj has NULL city — we will use these to demonstrate NULL handling.

SELECT — Choosing What to Return

Basic SELECT

-- Select specific columns (always preferred)
SELECT first_name, last_name, salary FROM employees;

-- Select all columns (avoid in production)
SELECT * FROM employees;

SELECT with Expressions

-- Calculated columns
SELECT first_name, salary, salary * 12 AS annual_salary FROM employees;

-- String concatenation
SELECT first_name + ' ' + last_name AS full_name FROM employees;              -- SQL Server
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;        -- All databases

-- Constants
SELECT first_name, 'Active' AS status, 2026 AS review_year FROM employees;

Why NOT to Use SELECT *

-- ❌ BAD: Reads every column (50 columns when you need 3)
SELECT * FROM employees WHERE department = 'Engineering';

-- ✅ GOOD: Reads only what you need (3 columns)
SELECT first_name, last_name, salary FROM employees WHERE department = 'Engineering';

SELECT * wastes I/O, transfers unnecessary data over the network, and breaks when someone adds or renames a column. In production, always list your columns.

Real-life analogy: SELECT * is like asking the librarian for “everything in the Science section” when you only need physics books. You get 500 books when you needed 20. List what you need.

FROM — The Starting Point

FROM is always the FIRST thing the database processes. It determines which table (or tables) to read.

-- Single table
SELECT * FROM employees;

-- With schema
SELECT * FROM dbo.employees;

-- With alias (shorter reference name)
SELECT e.first_name, e.salary FROM employees AS e;
SELECT e.first_name, e.salary FROM employees e;    -- AS is optional

WHERE — Filtering Individual Rows

WHERE removes rows that do not match the condition. It runs BEFORE GROUP BY, so it filters individual rows, not groups.

-- Filter by string
SELECT * FROM employees WHERE department = 'Engineering';

-- Filter by number
SELECT * FROM employees WHERE salary > 90000;

-- Filter by date
SELECT * FROM employees WHERE hire_date >= '2024-01-01';

-- Combined filters
SELECT * FROM employees WHERE department = 'Engineering' AND salary > 90000;

Real-life analogy: WHERE is like airport security. Every passenger (row) goes through the checkpoint. If your ID matches (condition is true), you pass through. If not, you are turned away. Only passengers who pass the checkpoint board the plane (appear in the result).

Comparison Operators

Operator	Meaning	Example	Result
=	Equal	WHERE city = 'Toronto'	Toronto employees
!= or <>	Not equal	WHERE city != 'Toronto'	Non-Toronto employees
>	Greater than	WHERE salary > 90000	Earning over 90K
<	Less than	WHERE salary < 80000	Earning under 80K
>=	Greater than or equal	WHERE salary >= 90000	90K and above
<=	Less than or equal	WHERE hire_date <= '2023-12-31'	Hired before 2024

Logical Operators: AND, OR, NOT

AND — Both Conditions Must Be True

-- Engineers in Toronto
SELECT * FROM employees
WHERE department = 'Engineering' AND city = 'Toronto';
-- Result: Naveen (Engineering + Toronto) and Shrey (Engineering + Toronto)
-- Vrushab is excluded (Engineering but Vancouver)

Real-life analogy: AND is like a job requirement that says “Must have a degree AND 5 years experience.” You need BOTH. Missing either one and you are out.

OR — At Least One Condition Must Be True

-- Engineering OR Analytics employees
SELECT * FROM employees
WHERE department = 'Engineering' OR department = 'Analytics';
-- Result: All 6 employees in either department

Real-life analogy: OR is like a restaurant that accepts “cash OR card.” Either payment method works. You do not need both.

NOT — Negate the Condition

-- Everyone except Sales
SELECT * FROM employees WHERE NOT department = 'Sales';
-- Result: Engineering (3) + Analytics (3) = 6 employees

-- Same as:
SELECT * FROM employees WHERE department != 'Sales';

The AND/OR Precedence Trap

This is the #1 logical operator mistake. AND has HIGHER precedence than OR:

-- ❌ WRONG: This does NOT mean "Engineers or Analysts earning over 90K"
SELECT * FROM employees
WHERE department = 'Engineering' OR department = 'Analytics' AND salary > 90000;

-- What it ACTUALLY means (AND binds first):
-- department = 'Engineering' OR (department = 'Analytics' AND salary > 90000)
-- Result: ALL Engineers (regardless of salary) + Analytics earning > 90K only
-- Shrey (Engineering, 95K) ✓, Vrushab (Engineering, 92K) ✓, Naveen ✓
-- Vishnu (Analytics, 91K) ✓ — but Priya (Analytics, 85K) ✗ and Ravi (Analytics, 85K) ✗

-- ✅ CORRECT: Use parentheses to force your intent
SELECT * FROM employees
WHERE (department = 'Engineering' OR department = 'Analytics') AND salary > 90000;
-- Now: (Engineers or Analysts) who earn > 90K
-- Naveen (105K) ✓, Shrey (95K) ✓, Vrushab (92K) ✓, Vishnu (91K) ✓
-- Priya (85K) ✗, Ravi (85K) ✗

Real-life analogy: “I want chocolate cake or vanilla cake and coffee” — does this mean “(chocolate or vanilla) cake, plus coffee” or “chocolate cake, or (vanilla cake with coffee)”? English is ambiguous. SQL is not — AND always binds first. Use parentheses to be explicit.

Rule: ALWAYS use parentheses when mixing AND and OR. Even if you know the precedence, the next person reading your query might not.

BETWEEN — Range Filtering

-- Salary between 80K and 95K (inclusive on BOTH ends)
SELECT first_name, salary FROM employees
WHERE salary BETWEEN 80000 AND 95000;
-- Returns: Shrey(95K), Vrushab(92K), Vishnu(91K), Ravi(85K), Priya(85K), Deepak(82K)
-- 80K and 95K are INCLUDED

-- Equivalent to:
SELECT first_name, salary FROM employees
WHERE salary >= 80000 AND salary <= 95000;

-- Date range — all 2023 hires
SELECT first_name, hire_date FROM employees
WHERE hire_date BETWEEN '2023-01-01' AND '2023-12-31';

-- NOT BETWEEN
SELECT first_name, salary FROM employees
WHERE salary NOT BETWEEN 80000 AND 95000;
-- Returns: Naveen(105K — above range), Anita(78K), Kavya(78K), Manoj(75K — below range)

Important: BETWEEN includes BOTH endpoints. BETWEEN 80000 AND 95000 includes 80000 and 95000. For dates, be careful — BETWEEN '2023-01-01' AND '2023-12-31' includes midnight on Dec 31 but NOT 2023-12-31 23:59:59 if the column is a DATETIME.

Real-life analogy: BETWEEN is like a nightclub with an age policy: “Allowed BETWEEN 21 AND 65.” If you are 21, you are in. If you are 65, you are in. If you are 20 or 66, you are out. Both boundaries are inclusive.

IN — Matching a List of Values

Basic IN

-- Employees in Toronto, Mumbai, or Delhi
SELECT first_name, city FROM employees
WHERE city IN ('Toronto', 'Mumbai', 'Delhi');

-- Equivalent to (but much cleaner):
SELECT first_name, city FROM employees
WHERE city = 'Toronto' OR city = 'Mumbai' OR city = 'Delhi';

IN with Numbers

SELECT * FROM employees WHERE emp_id IN (1001, 1004, 1007);

IN with Subquery

-- Employees in departments that have a budget over 500K
SELECT first_name, department FROM employees
WHERE department IN (
    SELECT department_name FROM departments WHERE budget > 500000
);

The subquery runs ONCE, builds a list, and IN checks each row against that list.

Real-life analogy: IN is like a bouncer with a VIP list. “Is your name IN this list? Toronto, Mumbai, Delhi? Yes? Come in. No? Sorry.”

NOT IN — The NULL Trap That Catches Everyone

Basic NOT IN

-- Employees NOT in Toronto or Mumbai
SELECT first_name, city FROM employees
WHERE city NOT IN ('Toronto', 'Mumbai');
-- Returns: Vrushab(Vancouver), Vishnu(Ottawa), Ravi(Calgary), Deepak(Delhi), Kavya(Pune)
-- Manoj is MISSING — his city is NULL!

The Trap: NOT IN with NULLs

-- Find employees whose manager_id is NOT in a list
-- But what if the subquery returns a NULL?
SELECT first_name FROM employees
WHERE emp_id NOT IN (SELECT manager_id FROM employees);
-- Returns: NOTHING! Zero rows!

-- WHY? The manager_id column contains NULL (for employees with no subordinates)
-- NOT IN checks: emp_id != 1001 AND emp_id != 1004 AND emp_id != 1007 AND emp_id != NULL
-- emp_id != NULL evaluates to UNKNOWN
-- UNKNOWN AND anything = UNKNOWN
-- The entire NOT IN becomes UNKNOWN → no rows returned

The Fix: Always Use NOT EXISTS Instead

-- ✅ SAFE: NOT EXISTS handles NULLs correctly
SELECT e.first_name FROM employees e
WHERE NOT EXISTS (
    SELECT 1 FROM employees e2 WHERE e2.manager_id = e.emp_id
);
-- Returns employees who are NOT managers (nobody reports to them)

Or Filter NULLs from the Subquery

-- ✅ Also safe: Remove NULLs from the list
SELECT first_name FROM employees
WHERE emp_id NOT IN (SELECT manager_id FROM employees WHERE manager_id IS NOT NULL);

This is the most important SQL trap you will face. Memorize it: NOT IN + NULLs = empty result. Use NOT EXISTS instead.

Real-life analogy: NOT IN with NULLs is like asking “Is John’s name NOT on this guest list?” But one entry on the list is smudged and unreadable (NULL). You cannot confirm John is NOT on the list because you do not know what the smudged entry says. So the bouncer says “I cannot let you in” — even though John is clearly not any of the readable names.

LIKE — Pattern Matching with Wildcards

The Two Wildcards

Wildcard	Matches	Example	Matches
%	Zero or more characters	'Nav%'	Nav, Naveen, Navya, Navigation
_	Exactly one character	'_av'	Nav, Rav, Kav (but NOT Naveen — too long)

Examples

-- Starts with 'Na'
SELECT * FROM employees WHERE first_name LIKE 'Na%';
-- Naveen ✓

-- Ends with 'ya'
SELECT * FROM employees WHERE first_name LIKE '%ya';
-- Priya ✓, Kavya ✓

-- Contains 'sh' anywhere
SELECT * FROM employees WHERE first_name LIKE '%sh%';
-- Vrushab ✓ (vruSHab), Vishnu ✓ (viSHnu)

-- Second character is 'a'
SELECT * FROM employees WHERE first_name LIKE '_a%';
-- Naveen ✓ (N-a-veen), Kavya ✓ (K-a-vya), Manoj ✓ (M-a-noj), Ravi ✓ (R-a-vi)

-- Exactly 5 characters
SELECT * FROM employees WHERE first_name LIKE '_____';
-- Shrey ✓, Priya ✓, Anita ✓, Manoj ✓, Kavya ✓ (all 5 letters)

-- Email from gmail
SELECT * FROM employees WHERE email LIKE '%@gmail.com';

-- NOT LIKE
SELECT * FROM employees WHERE email NOT LIKE '%@email.com';

Performance Warning

-- ✅ FAST: Trailing wildcard can use an index
SELECT * FROM employees WHERE last_name LIKE 'Vup%';

-- ❌ SLOW: Leading wildcard forces full table scan
SELECT * FROM employees WHERE last_name LIKE '%ula';
-- Database must check EVERY row — cannot use index

Real-life analogy: LIKE with % is like searching your phone contacts. 'Nav%' is like typing “Nav” — your phone instantly finds Naveen, Navya (trailing wildcard uses the index, fast). '%een' is like asking “who has a name ending in een?” — your phone must check every single contact (leading wildcard, full scan, slow).

IS NULL and IS NOT NULL

The NULL Rule

NULL is not a value — it means “unknown.” You cannot compare it with =:

-- ❌ WRONG: = NULL always returns FALSE (nothing matches)
SELECT * FROM employees WHERE city = NULL;       -- Returns NOTHING
SELECT * FROM employees WHERE city != NULL;      -- Returns NOTHING

-- ✅ CORRECT: Use IS NULL / IS NOT NULL
SELECT * FROM employees WHERE city IS NULL;      -- Manoj (NULL city)
SELECT * FROM employees WHERE email IS NULL;     -- Kavya (NULL email)
SELECT * FROM employees WHERE city IS NOT NULL;  -- Everyone except Manoj

Counting NULLs

-- COUNT(*) counts all rows, COUNT(column) counts non-NULL values
SELECT
    COUNT(*) AS total_rows,
    COUNT(email) AS has_email,
    COUNT(*) - COUNT(email) AS missing_email
FROM employees;
-- total_rows: 10, has_email: 9, missing_email: 1 (Kavya)

Real-life analogy: NULL is like an empty parking spot. You cannot say the spot “equals” empty — it is just… nothing is there. Asking “is this spot = empty?” does not make sense. Asking “IS this spot empty?” (IS NULL) does.

EXISTS and NOT EXISTS

EXISTS — Check if Related Rows Exist

EXISTS returns TRUE the moment it finds ONE matching row. It does not need to count all matches.

-- Employees who manage at least one person
SELECT e.first_name, e.department FROM employees e
WHERE EXISTS (
    SELECT 1 FROM employees e2 WHERE e2.manager_id = e.emp_id
);
-- Naveen (manages Shrey, Vrushab), Vishnu (manages Ravi, Priya), Anita (manages Deepak, Kavya, Manoj)

NOT EXISTS — Check if No Related Rows Exist

-- Employees who do NOT manage anyone
SELECT e.first_name FROM employees e
WHERE NOT EXISTS (
    SELECT 1 FROM employees e2 WHERE e2.manager_id = e.emp_id
);
-- Shrey, Vrushab, Ravi, Priya, Deepak, Kavya, Manoj

Why EXISTS is Better Than IN for Large Tables

-- IN: Builds a list of ALL manager_ids, then checks each employee against it
SELECT * FROM employees WHERE emp_id IN (SELECT manager_id FROM employees);
-- Builds list: [1001, 1004, 1007, NULL, NULL, NULL, ...]

-- EXISTS: For each employee, checks if ANY subordinate exists — stops at first match
SELECT * FROM employees e
WHERE EXISTS (SELECT 1 FROM employees e2 WHERE e2.manager_id = e.emp_id);
-- For Naveen: finds Shrey → stops (TRUE). Does not check Vrushab.
-- Much faster when there are many matches.

Real-life analogy: IN is like reading an entire phone book to build a list of all doctors, then checking if your name is on the list. EXISTS is like calling ONE hospital and asking “Is Dr. Smith here?” If they say yes, you stop. No need to call every hospital. EXISTS is faster because it stops at the first match.

ORDER BY — Sorting the Results

ORDER BY runs LAST (except for TOP/LIMIT), so it can use aliases from SELECT:

-- Sort ascending (default)
SELECT first_name, salary FROM employees ORDER BY salary;          -- 75K → 105K

-- Sort descending
SELECT first_name, salary FROM employees ORDER BY salary DESC;     -- 105K → 75K

-- Multiple sort columns
SELECT first_name, department, salary FROM employees
ORDER BY department ASC, salary DESC;
-- First sort by department A-Z, then within each department by salary highest first

-- Sort by alias (works because ORDER BY runs after SELECT)
SELECT first_name, salary * 12 AS annual FROM employees ORDER BY annual DESC;

-- Sort by column position (works but not recommended)
SELECT first_name, salary FROM employees ORDER BY 2 DESC;   -- 2 = salary column

-- NULLs in sorting
SELECT first_name, city FROM employees ORDER BY city ASC;
-- SQL Server: NULLs appear FIRST in ASC
-- PostgreSQL: NULLs appear LAST in ASC (or use NULLS FIRST / NULLS LAST)

DISTINCT — Removing Duplicates

-- Unique departments
SELECT DISTINCT department FROM employees;
-- Engineering, Analytics, Sales

-- Unique city + country combinations
SELECT DISTINCT city, country FROM employees;
-- Toronto/Canada, Vancouver/Canada, Ottawa/Canada, Calgary/Canada,
-- Mumbai/India, Delhi/India, Pune/India, NULL/India

-- Count distinct
SELECT COUNT(DISTINCT department) AS unique_departments FROM employees;
-- 3

-- Count distinct cities (NULLs excluded by COUNT)
SELECT COUNT(DISTINCT city) AS unique_cities FROM employees;
-- 7 (NULL is not counted)

Performance note: DISTINCT must compare every row in the result set. On a table with millions of rows, it is expensive. Consider whether GROUP BY or EXISTS achieves the same result more efficiently.

TOP and LIMIT — Restricting Row Count

-- SQL Server: TOP
SELECT TOP 3 first_name, salary FROM employees ORDER BY salary DESC;
-- Naveen(105K), Shrey(95K), Vrushab(92K)

-- Top N percent
SELECT TOP 20 PERCENT first_name, salary FROM employees ORDER BY salary DESC;
-- Top 20% = 2 rows: Naveen, Shrey

-- MySQL / PostgreSQL: LIMIT
SELECT first_name, salary FROM employees ORDER BY salary DESC LIMIT 3;

-- WITH TIES (include rows tied with the last row)
SELECT TOP 3 WITH TIES first_name, salary FROM employees ORDER BY salary DESC;
-- If 3rd and 4th have same salary, both are included

OFFSET/FETCH — Pagination

-- Page 1: rows 1-5
SELECT first_name, salary FROM employees
ORDER BY salary DESC
OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY;

-- Page 2: rows 6-10
SELECT first_name, salary FROM employees
ORDER BY salary DESC
OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY;

-- Page 3: rows 11-15
SELECT first_name, salary FROM employees
ORDER BY salary DESC
OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;

Real-life analogy: Pagination is like reading a book. OFFSET is the page number you skip to. FETCH is how many pages you read. OFFSET 10 FETCH 5 means “skip to page 10, read 5 pages.”

Combining Multiple Clauses

-- "Find the top 3 highest-paid employees in Canada,
--  excluding Sales, hired after 2023, sorted by salary"

SELECT first_name, department, city, salary, hire_date
FROM employees
WHERE country = 'Canada'                    -- Only Canadian employees
  AND department != 'Sales'                  -- Exclude Sales
  AND hire_date >= '2023-01-01'              -- Hired 2023 or later
ORDER BY salary DESC                         -- Highest first
OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY;       -- Top 3

-- Execution: FROM → WHERE (3 filters) → SELECT → ORDER BY → FETCH

Common Mistakes

1. Using = NULL instead of IS NULL — = NULL always returns unknown (no rows). Use IS NULL.

2. NOT IN with NULLs — returns zero rows. Use NOT EXISTS instead.

3. Missing parentheses with AND/OR — AND binds before OR. Always parenthesize mixed conditions.

4. Using alias in WHERE — WHERE runs before SELECT. Repeat the expression or use a CTE.

5. BETWEEN with DATETIME — BETWEEN '2023-01-01' AND '2023-12-31' misses times after midnight on Dec 31. Use < '2024-01-01' instead.

6. Leading wildcard in LIKE — LIKE '%smith' forces full table scan. Restructure if possible.

7. SELECT * in production — reads all columns, wastes resources, breaks on schema changes.

Interview Questions

Q: What is the SQL execution order? A: FROM → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → TOP/LIMIT. This is why aliases work in ORDER BY but not in WHERE, and why aggregate functions work in HAVING but not in WHERE.

Q: What is the difference between IN and EXISTS? A: IN builds a complete list from the subquery and checks each row. EXISTS checks row-by-row and stops at the first match. EXISTS is faster for large subqueries and handles NULLs correctly. Always use NOT EXISTS instead of NOT IN to avoid the NULL trap.

Q: Why is NOT IN dangerous with NULLs? A: If the subquery returns any NULL, NOT IN evaluates to UNKNOWN for every row, returning zero results. This happens because comparing any value to NULL produces UNKNOWN, and UNKNOWN in a NOT IN check causes the entire condition to fail.

Q: What does BETWEEN include? A: Both endpoints. BETWEEN 80000 AND 100000 includes 80000 and 100000. Be careful with datetime columns — use < next_day instead of BETWEEN for date ranges.

Wrapping Up

The execution order is the master key that unlocks SQL. Once you understand that WHERE runs before SELECT, that HAVING runs after GROUP BY, and that ORDER BY runs last — every SQL behavior makes sense.

The WHERE clause is your primary tool for filtering. Master its operators (=, !=, >, <), its clauses (BETWEEN, IN, LIKE, EXISTS, IS NULL), and its traps (NOT IN + NULLs, AND/OR precedence, leading wildcards). These are not edge cases — they appear in production queries daily.

Next post: GROUP BY, Aggregations, HAVING, CASE WHEN, and Null Handling

Related posts: – SQL Joins – SQL Window Functions – CTEs and Subqueries

---

Naveen Vuppula is a Senior Data Engineering Consultant and app developer based in Ontario, Canada. He writes about Python, SQL, AWS, Azure, and everything data engineering at DriveDataScience.com.