Chapter 111: Selecting and Querying Data

The SELECT statement is the heart of SQL. It retrieves and shapes data from one or more tables and can include expressions, filters, sorting, and limits. Every analytic, report, and application query builds on its structure. This chapter covers its syntax, derived columns, and key modifiers for distinctness, limits, and ordering.

SELECT structure and clause order

The typical SELECT query has a predictable layout. Not every clause is required, but the order is fixed by the SQL standard:

SELECT [DISTINCT] column_list
FROM table_name
  [JOIN ...]
WHERE condition
GROUP BY grouping_columns
HAVING group_condition
ORDER BY sort_columns
LIMIT number OFFSET start;

Logical evaluation order

Although you write SELECT first, the database engine logically processes the clauses in this sequence:

Basic example

SELECT first_name, last_name, department, salary
FROM employees
WHERE salary > 80000
ORDER BY department, salary DESC;

Column aliases, expressions, and derived columns

Aliases rename columns in the output or simplify references to computed expressions. Use AS for clarity, though it is optional in most engines.

Using aliases

SELECT 
  first_name AS given,
  last_name AS family,
  salary * 1.1 AS adjusted_salary
FROM employees;

Aliased columns can make results easier to read and can be reused in the ORDER BY clause.

SELECT 
  id, 
  department_id,
  salary * 1.1 AS adjusted_salary
FROM employees
ORDER BY adjusted_salary DESC;

Expressions and derived data

SQL expressions allow arithmetic, string concatenation, conditional logic, and type conversion directly in the query.

SELECT 
  first_name || ' ' || last_name AS full_name,
  EXTRACT(YEAR FROM hire_date) AS hire_year,
  CASE 
    WHEN salary >= 100000 THEN 'High'
    WHEN salary >= 70000 THEN 'Medium'
    ELSE 'Low'
  END AS pay_band
FROM employees;

• Use arithmetic for numeric calculations (price * quantity).
• Use functions for transformations (UPPER(name), SUBSTR(email, 1, 10)).
• Use CASE for conditional derivations.

Table aliases and joins

Short aliases for tables make multi-table queries readable. Use clear, short forms like e for employees.

SELECT e.first_name, e.last_name, d.name AS department
FROM employees e
JOIN departments d ON e.department_id = d.id;

You can even reuse derived data in subqueries or common table expressions (CTEs).

WITH high_earners AS (
  SELECT id, first_name, last_name, salary
  FROM employees
  WHERE salary > 100000
)
SELECT first_name, last_name
FROM high_earners
ORDER BY last_name;

The following modifiers control result uniqueness, size, and ordering. Their use and syntax vary slightly between engines.

DISTINCT

DISTINCT removes duplicate rows from the result set. It applies to the combination of all selected columns.

SELECT DISTINCT department
FROM employees;

SELECT DISTINCT department, role
FROM employees;

TOP, LIMIT, and OFFSET

These clauses restrict the number of rows returned. Syntax differs among engines.

-- PostgreSQL / MySQL
SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 5;

-- SQL Server
SELECT TOP (5) first_name, last_name, salary
FROM employees
ORDER BY salary DESC;

ORDER BY

ORDER BY defines the sort order of the result set. Without it, row order is not guaranteed, even if it appears consistent in testing.

SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC, last_name ASC;

• Specify ascending (ASC) or descending (DESC) per column.
• Use column positions (ORDER BY 3 DESC) only for quick tests, not production code.
• Sort by expressions or aliases (ORDER BY adjusted_salary DESC).

By mastering SELECT structure, aliases, and output controls such as DISTINCT, LIMIT, and ORDER BY, you can build efficient, readable queries that form the backbone of all SQL usage.