SELECT Statement in SQL
Overview
The SELECT statement is the foundational query building block in Structured Query Language. It dictates which columns and computed expressions the database returns to the client application. While developers write SQL statements starting with the SELECT keyword, understanding the Logical Query Processing Order—the actual sequence in which the database engine evaluates each clause—is the key to writing efficient queries and understanding column alias scoping rules.
What You Will Learn
In this lesson, you will master:
- Syntactic vs Logical Query Order: Why
WHEREcannot reference aliases declared inSELECT. - Column Projections & Aliasing: Projecting subsets of data with clean, readable aliases using
AS. - Computed Columns & Scalar Expressions: Performing math, string manipulations, and conditional calculations.
- The Dangers of
SELECT *: Network saturation, memory bloat, and cache thrashing in production. - Deduplication with
DISTINCT: How unique row filtering works under the hood.
Logical Query Processing Order
SQL is a declarative language, not an imperative one. You declare what data you need, and the database optimizer executes the query. The database processes clauses in a distinct order that differs fundamentally from syntax order:
Written Order (Syntactic):
1. SELECT
2. FROM
3. JOIN
4. WHERE
5. GROUP BY
6. HAVING
7. ORDER BY
8. LIMIT / OFFSET
Logical Execution Order (How the Database Actually Executes):
Step 1: FROM & JOIN (Locates physical tables, joins records into working dataset)
Step 2: WHERE (Filters out rows that do not match predicate)
Step 3: GROUP BY (Aggregates filtered rows into groups)
Step 4: HAVING (Filters groups based on aggregate conditions)
Step 5: SELECT (Evaluates expressions, projects requested columns, applies AS aliases)
Step 6: DISTINCT (Eliminates duplicate projection rows)
Step 7: ORDER BY (Sorts the final projected dataset)
Step 8: LIMIT / OFFSET(Trims output rows to requested window)
Why Aliases Fail in WHERE Clauses
Because WHERE executes in Step 2 and SELECT (where column aliases are created) executes in Step 5, you cannot reference a SELECT alias inside a WHERE clause!
-- FAILS with: column "annual_salary" does not exist!
SELECT first_name, monthly_salary * 12 AS annual_salary
FROM employees
WHERE annual_salary > 100000;
-- CORRECT: Repeat expression or wrap in CTE/subquery
SELECT first_name, monthly_salary * 12 AS annual_salary
FROM employees
WHERE (monthly_salary * 12) > 100000;
Column Projections & Scalar Expressions
The SELECT clause can project raw column values, mathematical calculations, string manipulations, and conditional branching:
SELECT
order_id,
customer_id,
-- Mathematical calculation
quantity * unit_price AS gross_amount,
(quantity * unit_price) * (1 - discount_rate) AS net_amount,
-- String manipulation
UPPER(order_status) AS status_clean,
-- Conditional CASE expression
CASE
WHEN total_amount > 1000 THEN 'VIP Order'
WHEN total_amount > 200 THEN 'Standard Order'
ELSE 'Low Value'
END AS order_tier
FROM orders;
Why You Should Never Use SELECT * in Production
While SELECT * is convenient in ad-hoc terminal exploration, using it in production code causes severe performance and reliability problems:
- Massive Network & I/O Overhead: Tables frequently contain large text descriptions, JSON payloads, or binary blobs.
SELECT *forces the database to read these gigabytes off disk and transmit them across the network, even if your frontend only displays a name. - Defeats Covering Indexes: When an index covers only the 3 columns your query actually needs, requesting
SELECT *forces the database engine to perform expensive disk lookups for every single matching row. - Application Breakage on Schema Changes: If a DBA adds a new column, modifies column ordering, or alters types, application code relying on positional mapping breaks immediately.
-- BAD: Fetches 40 columns including unneeded logs
SELECT * FROM users WHERE status = 'ACTIVE';
-- GOOD: Projects only the exact 3 required columns
SELECT user_id, email, full_name FROM users WHERE status = 'ACTIVE';
Deduplicating Output with DISTINCT
The DISTINCT clause eliminates duplicate rows from the final result set:
-- Returns unique combinations of country and state
SELECT DISTINCT country, state
FROM addresses
ORDER BY country, state;
Under the hood, the database engine enforces DISTINCT by either sorting the entire result set in memory or constructing an in-memory hash table. For massive datasets, DISTINCT can consume significant memory and trigger disk spills.
Common Mistakes & Gotchas
- Placing
DISTINCTon a Single Column: WritingSELECT DISTINCT(country), statedoes not evaluate distinctness oncountryalone. SQL parsesDISTINCTas a clause-level modifier that applies across all projected columns combined. - Selecting Non-Grouped Columns without Aggregates: In standard SQL, every column in the
SELECTclause must either appear in theGROUP BYclause or be wrapped in an aggregate function (COUNT,SUM,AVG,MAX,MIN).
Interview Perspective
SQL Interview Question: Explain the difference between syntactic order and logical query execution order in SQL.
Key Talking Points for Candidates:
- Syntactic Order: The order in which SQL queries are written (
SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY). - Logical Processing Order: The database starts with
FROM/JOINto establish the dataset, appliesWHEREfilters, groups rows (GROUP BY), filters groups (HAVING), evaluatesSELECTprojections and expressions, deduplicates (DISTINCT), and finally sorts (ORDER BY) and paginates (LIMIT). - Implications: Understanding this order explains why
WHEREcannot seeSELECTaliases, butORDER BY(which runs afterSELECT) can see and sort bySELECTaliases.
Interactive Practice Challenge
DataPlayArena Technical Editorial Board
Curated & maintained by enterprise data platform engineers with 10+ years in distributed systems.
Runtime Verification Standards: Every code example, query, and architecture diagram in this tutorial is compiled and verified against real runtimes (Apache Spark 3.5, Python 3.11, PostgreSQL, and DuckDB). If you spot any errata or want to suggest improvements, please submit feedback.