beginner

SELECT Statement in SQL

By DataPlayArena Technical Review BoardUpdated 2026-07-208 min readRuntime Verified

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 WHERE cannot reference aliases declared in SELECT.
  • 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!

sql
-- 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:

sql
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:

  1. 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.
  2. 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.
  3. 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.
sql
-- 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:

sql
-- 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 DISTINCT on a Single Column: Writing SELECT DISTINCT(country), state does not evaluate distinctness on country alone. SQL parses DISTINCT as a clause-level modifier that applies across all projected columns combined.
  • Selecting Non-Grouped Columns without Aggregates: In standard SQL, every column in the SELECT clause must either appear in the GROUP BY clause 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:

  1. Syntactic Order: The order in which SQL queries are written (SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY).
  2. Logical Processing Order: The database starts with FROM/JOIN to establish the dataset, applies WHERE filters, groups rows (GROUP BY), filters groups (HAVING), evaluates SELECT projections and expressions, deduplicates (DISTINCT), and finally sorts (ORDER BY) and paginates (LIMIT).
  3. Implications: Understanding this order explains why WHERE cannot see SELECT aliases, but ORDER BY (which runs after SELECT) can see and sort by SELECT aliases.

Interactive Practice Challenge

Challenge: Compute Net Margin Projection

Write a query on the sales table that selects product_id, calculates net_profit as revenue - cost, and categorizes profitability as 'High' if net_profit > 500, otherwise 'Normal'. Sort by net_profit in descending order.

DPA

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.