SQL SELECT Statement: Columns, DISTINCT, Aliases & Execution Order

1. The SELECT Statement: Declarative Data Retrieval

The SELECT statement is the foundational command of Data Query Language (DQL). It retrieves records from one or more tables and presents them to the caller as a two-dimensional tabular result set.

Because SQL is a declarative programming language, a SELECT query specifies what data you want to obtain rather than how the database storage engine should scan disk blocks, traverse B-Trees, or allocate memory buffers. The database query planner evaluates your request and compiles the most efficient execution plan automatically.

`Result Sets vs Physical Storage`

A SELECT statement never alters stored table records on disk:

Read-Only Operation:SELECT reads table data without acquiring exclusive row locks, allowing high-concurrency read throughput across thousands of simultaneous sessions.
Ephemeral Result Sets:The output returned by a SELECT query exists temporarily in application memory or client streams. It does not create new permanent table storage unless piped into an INSERT or CTAS statement.
Dynamic Virtual Columns:You can project calculated fields, string transformations, and conditional CASE expressions that do not exist in the physical underlying schema.

2. SELECT * (Select All Columns)

The asterisk (*) acts as a wildcard character instruction telling the query planner to return every column currently defined on the target table, in their original schema definition order.

Querying All Columns with SELECT *

SQL

Fetch all defined attributes for students using the wildcard operator

`Why SELECT * Is an Anti-Pattern in Production`

While convenient for interactive terminal exploration, avoid SELECT * in production backend code:

Unnecessary Network Bandwidth:Fetching unused TEXT, JSON, or BLOB columns consumes massive network bandwidth and increases serialization latency between database and backend.
Breaks Covering Indexes:An index cannot satisfy a query if SELECT * requests columns not included in the index leaf pages, forcing expensive disk lookups for every matching row.
Schema Fragility:If a database migration alters column order or appends a new column, application code that maps query results positionally (such as array indices) will fail or misalign data.

3. SELECT Specific Columns (Projection)

In relational algebra, selecting specific columns from a relation is called projection. You specify a comma-separated list of the exact column names you need returned.

Column Projection Example

SQL

Request only the explicit fields required by your application view

`Index-Only Scans (Covering Indexes)`

Projecting explicit columns enables database engines to bypass disk table scans entirely:

Covering Index Concept:If you query SELECT student_id, department and an index exists on (student_id, department), the database answers the query directly from RAM index memory without touching the physical table.
Memory Buffer Efficiency:Projecting two small columns instead of twenty wide columns drastically reduces memory consumption inside the database buffer pool and application server.

4. SELECT DISTINCT (Eliminating Duplicates)

By default, SQL queries return duplicate values if multiple records contain identical column values. Adding the DISTINCT keyword filters the result set to return only unique, deduplicated combinations of the selected columns.

Deduplicating Values with SELECT DISTINCT

SQL

Filter out duplicate department entries to inspect unique academic divisions

When you specify multiple columns in SELECT DISTINCT colA, colB, deduplication applies across the entire combination of the columns, not individually:

Multi-Column DISTINCT

SQL

Identify unique pairs of academic departments and student home cities

`How DISTINCT Evaluates NULL Values`

Understanding how SQL treats missing values during deduplication:

Single NULL Retained:Although NULL does not equal NULL in boolean comparisons (NULL = NULL is UNKNOWN), the DISTINCT clause considers all NULLs as duplicates and collapses them into a single row.
Sorting Overhead:Under the hood, DISTINCT typically performs a hash aggregation or sort operation on the result set. Running DISTINCT across millions of rows can increase query execution time.

5. SQL Aliases with AS (Column and Table Aliases)

The AS keyword assigns a temporary alias name to a column or table. Aliases exist solely for the duration of the query execution and do not rename underlying database objects.

Alias Type	Syntax Example	Primary Purpose
Column Alias	SELECT first_name AS given_name	Renames output columns for readability and API serialization
Expression Alias	SELECT unit_price * 1.1 AS price_with_tax	Labels computed calculations with clean, readable header names
Table Alias	FROM students AS s JOIN grades AS g	Shortens table references in complex joins and self-joins

Using Column and Table Aliases

SQL

Assign clean alias labels to output columns and qualify table names

`Identifier Quoting for Spaces and Special Characters`

When assigning alias names that include spaces or reserved keywords:

Standard Double Quotes:In ANSI SQL, PostgreSQL, and SQLite, wrap aliases containing spaces in double quotes: SELECT gpa AS "Grade Point Average".
Backticks in MySQL:MySQL uses backticks for quoted identifiers: SELECT gpa AS `Grade Point Average`.
Best Practice:Avoid spaces in aliases altogether. Use snake_case (grade_point_average) or camelCase (gradePointAverage) to prevent quoting complexity in application code.

6. SQL Expressions and Calculated Columns

The SELECT list is not limited to existing table columns. You can execute arithmetic expressions, statistical transformations, and conditional calculations dynamically during query evaluation.

Arithmetic Calculations in SELECT

SQL

Perform mathematical scaling and round calculated numeric expressions

You can also incorporate conditional logic directly in the SELECT clause using CASE expressions:

Conditional Projections with CASE

SQL

Classify student records into academic honor categories based on GPA

`Integer Division vs Float Division`

Be cautious with integer division across different relational engines:

Truncation in SQL Server / PostgreSQL:Dividing two integers (such as 3 / 4) truncates decimal results to 0. Always cast or include decimals: 3.0 / 4 to obtain 0.75.
SQLite and MySQL Division:SQLite and MySQL evaluate 3 / 4 as floating-point 0.75 automatically.

7. Concatenating Columns

String concatenation joins multiple text columns and literal strings into a single formatted output column (such as combining first and last names into a full name). Different SQL engines utilize different concatenation operators:

Engine	Concatenation Operator / Function	Example
PostgreSQL, SQLite, Oracle	Double Pipe (\|\|)	first_name \|\| ' ' \|\| last_name
MySQL, MariaDB	CONCAT(str1, str2, ...)	CONCAT(first_name, ' ', last_name)
SQL Server	Plus Operator (+) or CONCAT()	first_name + ' ' + last_name

String Concatenation in SQL

SQL

Join string fields, literal labels, and numeric IDs into unified formatted text

`NULL Poisoning in String Concatenation`

How NULL values interact with concatenation across SQL engines:

Pipe (||) in Standard SQL / SQLite / Postgres:Concatenating any string with NULL produces NULL: 'Hello ' || NULL produces NULL.
COALESCE Protection:Use COALESCE to replace potential NULLs with empty strings: first_name || ' ' || COALESCE(middle_name || ' ', '') || last_name.
CONCAT() in MySQL & SQL Server:The CONCAT() function automatically treats NULL as an empty string, preventing entire output strings from converting to NULL.

8. SELECT without FROM (Scalar Expressions)

In modern SQL engines (such as MySQL, PostgreSQL, and SQLite), a SELECT statement does not require a FROM clause if you are querying scalar constant values, evaluating mathematical expressions, or inspecting system functions.

Querying Scalar Values without a Table

SQL

Test arithmetic evaluations, string functions, and system dates independently

`The Oracle DUAL Table Requirement`

Different database engines enforce varying rules regarding the FROM clause:

Oracle Database:Oracle requires a FROM clause on every query. To evaluate scalar calculations, Oracle provides a special dummy single-row table named DUAL: SELECT 25 * 4 FROM DUAL;.
PostgreSQL & SQLite:Permit SELECT without FROM completely. PostgreSQL also supports SELECT version(); to inspect database server versions.
SQL Server:Fully supports SELECT 1 + 1; without a FROM clause.

9. SQL Order of Execution (Lexical vs Logical Order)

One of the most common sources of confusion for SQL developers is the difference between lexical order (the written order in code) and logical order of execution (the sequence in which the database query engine actually evaluates the clauses).

Step	Clause	What the Engine Does at This Step
1	FROM & JOIN	Identifies the target tables and builds the working Cartesian or joined dataset.
2	WHERE	Filters individual row records based on boolean conditions.
3	GROUP BY	Collapses remaining rows into groups based on common key values.
4	HAVING	Filters grouped aggregates (such as COUNT > 5).
5	SELECT	Projects requested columns, calculates expressions, and applies aliases.
6	DISTINCT	Deduplicates identical rows in the projected result set.
7	ORDER BY	Sorts the final result set in ascending or descending sequence.
8	LIMIT / OFFSET	Restricts the final returned row count for client pagination.

Execution Order in Practice: Aliases in ORDER BY

SQL

Observe how ORDER BY successfully references column aliases created in SELECT

`Why Aliases Fail in the WHERE Clause`

The most common mistake caused by query execution order:

The Failure:Writing SELECT salary * 1.2 AS new_salary FROM staff WHERE new_salary > 80000; triggers an 'unknown column new_salary' error.
The Cause:Because WHERE executes at Step 2 and SELECT executes at Step 5, the alias new_salary does not exist yet when the WHERE filter is evaluated!
The Solution:Repeat the raw expression in the WHERE clause: WHERE salary * 1.2 > 80000, or wrap the query inside a Common Table Expression (CTE) or subquery.

Knowledge Check: SQL SELECT Statement

1. Why should production queries avoid SELECT * in backend code?

2. How does SELECT DISTINCT treat NULL values across result rows?

3. Why can you NOT use a column alias in a WHERE clause?

4. What is the first clause evaluated in SQL logical execution order?

5. Which operator concatenates strings in PostgreSQL, SQLite, and Oracle?

6. In MySQL or SQLite, what does SELECT 50 * 2; return without a FROM clause?