SELECT, projection, DISTINCT & sorting

A SELECT query produces a result table. The result does not have to mirror the source table: you can choose columns, calculate expressions, rename output fields, remove duplicate result rows, and control ordering.

Selecting columns instead of SELECT star

SELECT * is useful while exploring an unfamiliar table, but explicit columns are better in production queries because they document intent and avoid silently changing the result shape when the schema changes.

SELECT id, first_name, last_name, role
FROM users;

Run the query to see the result.

Prefer explicit columns in API queries, reports, migrations, and assertions. Use SELECT * mainly for temporary inspection.

Column aliases and expressions

Aliases make calculated fields readable and are essential when two joined tables contain columns with the same name.

SELECT id,
       first_name || ' ' || last_name AS full_name,
       role AS job_role
FROM users
ORDER BY id;

Run the query to see the result.

The concatenation operator shown above works in SQLite and PostgreSQL. Other engines may use different functions or operators, which is a common dialect difference.

DISTINCT removes duplicate result rows

DISTINCT applies to the complete selected row, not independently to each column.

SELECT DISTINCT role, status
FROM users
ORDER BY role, status;

Run the query to see the result.

If you need one row per entity according to a business rule such as “latest event per user,” DISTINCT is usually not enough. That problem is handled later with grouping or window functions.

ORDER BY and deterministic output

Without ORDER BY, SQL does not promise a stable row order. Even if a query appears to return rows in primary-key order today, a different execution plan, index, engine version, or data distribution can change it.

SELECT id, name, salary
FROM employees
ORDER BY salary DESC, id ASC;

Run the query to see the result.

When values can tie, add a stable tie-breaker such as the primary key. This is especially important in automated tests and pagination.

LIMIT and pagination basics

SQLite and PostgreSQL support LIMIT and OFFSET. SQL Server commonly uses TOP or OFFSET/FETCH. Large OFFSET values become inefficient and can produce unstable pages while rows are changing; keyset pagination is introduced later.

SELECT id, email, created_at
FROM users
ORDER BY created_at DESC, id DESC
LIMIT 3;

Run the query to see the result.

Practice: return the three highest-paid employees, resolving salary ties by ascending employee id.