SELECT Fundamentals

Concept

SELECT retrieves data. It is the most-used SQL statement and the foundation of reporting and analysis. A SELECT returns a result set: a temporary table of rows and columns.

Why It Matters

Every report, dashboard, API response, and test verification is built on SELECT. Understanding its logical processing order explains many "why doesn't this work?" errors (for example, why a column alias cannot be used in WHERE).

Syntax

Logical Query Processing Order You write SELECT first, but the database logically evaluates the clauses in this order:

A column alias defined in SELECT cannot be used in WHERE (WHERE runs earlier).

A column alias can be used in ORDER BY (ORDER BY runs later). MySQL, PostgreSQL, and SQLite also allow aliases in GROUP BY/HAVING as an extension; SQL Server and Oracle do not.

Aggregate functions cannot appear in WHERE (groups do not exist yet); use HAVING.

This is the logical model; the optimizer may physically execute steps in a different order as long as the result is the same.

SELECT [DISTINCT] column_or_expression [AS alias], ...
FROM table_name [AS table_alias]
[WHERE condition]
[GROUP BY columns]
[HAVING group_condition]
[ORDER BY columns [ASC|DESC]]
[LIMIT n [OFFSET m]];      -- or TOP / FETCH depending on DBMS

Written order              Logical evaluation order
-------------              ------------------------
1. SELECT                  1. FROM (and JOINs)        → build the source rows
2. FROM                    2. WHERE                   → filter rows
3. WHERE                   3. GROUP BY                → form groups
4. GROUP BY                4. HAVING                  → filter groups
5. HAVING                  5. SELECT                  → compute expressions, aliases, window functions
6. ORDER BY                6. DISTINCT                → remove duplicates
7. LIMIT / OFFSET          7. ORDER BY                → sort
                           8. LIMIT / OFFSET / FETCH  → cut the result

Example

7.1 SELECT * (all columns) SELECT * is fine for exploration but should be avoided in application code (see Common Mistakes).

7.2 Selecting specific columns 7.3 Column aliases Use double quotes (standard) for aliases with spaces or mixed case: AS "Unit Price". MySQL also accepts backticks; SQL Server also accepts [Unit Price].

Table aliases: FROM products AS p (Oracle does not allow AS before a table alias: FROM products p).

DISTINCT applies to the entire row of selected columns, not just the first column:

DISTINCT treats NULLs as equal to each other (all NULLs collapse into one row).

7.5 Expressions and calculated columns (The number of decimal places displayed depends on the DBMS; use ROUND(..., 2) to control it.) String concatenation in expressions:

Integer division caution:

7.6 Literal values A literal in a query is repeated for every row:

7.7 NULL values in SELECT Any arithmetic or concatenation with NULL yields NULL:

Email: [email protected] Exceptions: MySQL's CONCAT() returns NULL if any argument is NULL, but SQL Server's CONCAT() and PostgreSQL's CONCAT() treat NULL as an empty string. Oracle treats an empty string as NULL and 'a' || NULL returns 'a'.

Fix with COALESCE:

SELECT * FROM departments;

SELECT product_name, price FROM products;

SELECT product_name AS "Product",
       price        AS unit_price,
       stock        units_in_stock      -- AS is optional for column aliases
FROM products;

SELECT DISTINCT category FROM products;

SELECT DISTINCT country, city FROM customers;   -- distinct (country, city) pairs

SELECT product_name,
       price,
       stock,
       price * stock            AS inventory_value,
       price * 1.18             AS price_with_gst,
       price - price * 0.10     AS discounted_price
FROM products;

-- Standard / PostgreSQL / Oracle / SQLite
SELECT first_name || ' ' || last_name AS full_name FROM employees;
-- MySQL, SQL Server 2012+, PostgreSQL, Oracle (2 args only)
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;
-- SQL Server
SELECT first_name + ' ' + last_name AS full_name FROM employees;

SELECT 7 / 2 AS result;
-- PostgreSQL, SQL Server, SQLite: 3   (integer division)
-- MySQL, Oracle:                  3.5
SELECT 7 / 2.0 AS result;   -- 3.5 everywhere
SELECT CAST(7 AS DECIMAL(10,2)) / 2;   -- explicit

SELECT 'Active' AS status_label, 42 AS answer, CURRENT_DATE AS today;
-- PostgreSQL, MySQL, SQL Server, SQLite allow SELECT without FROM.
-- Oracle (pre-23ai) requires FROM dual:
SELECT 'Active' AS status_label FROM dual;

SELECT name, 'Customer' AS party_type FROM customers;

Expected Result

(Note: in this sample, salary represents a monthly amount for the purpose of the example.)

SELECT first_name || ' ' || last_name AS full_name,
       salary * 12 AS annual_ctc
FROM employees
WHERE dept_id = 3;

Common Mistakes

Using SELECT * in production code: it returns unneeded columns, breaks when columns are added, prevents covering-index optimizations, and increases network traffic.

Using a SELECT alias in WHERE: SELECT price * 1.18 AS gross FROM products WHERE gross > 1000; fails. Repeat the expression or use a subquery/CTE.

Writing SELECT DISTINCT(col1), col2 and thinking DISTINCT applies only to col1.

Using DISTINCT to hide a join that produces duplicates instead of fixing the join.

Forgetting integer division.

Expecting 'text' || NULL to return 'text'.

Best Practices

List explicit columns.

Give calculated columns clear aliases.

Use table aliases in multi-table queries.

Format one column per line for long SELECT lists.

Round and format at the presentation layer or at the last step.

Interview Relevance

High: "What is the logical order of SQL execution?" is one of the most common SQL interview questions, along with "Why can't I use an alias in WHERE?" and "Why avoid SELECT *?".

Practice Questions

Select each employee's full name and job title.

Show each product's name and its price rounded to the nearest 1000 (hint: ROUND(price, -3)).

List the distinct countries of customers.

Show each order with a 5% discount column.

Explain why SELECT salary*12 AS annual FROM employees WHERE annual > 1000000; fails and rewrite it correctly.