🎯SELECT queries

Build a new relation from old ones

SELECT is not “print columns”: it is a relation constructor. Every clause reshapes the working set until you have the exact tuple shape your API, chart, or downstream CTE expects.

SQL

Project specific columns (prefer explicit lists)

SELECT gene_symbol, chromosome, strand
FROM genes
WHERE species = 'Homo sapiens'
LIMIT 4;

✓Illustrative result set

gene_symbol	chromosome	strand
BRCA1	17	+
BRCA2	13	-
TP53	17	-
EGFR	7	+

SQL

Expressions + aliases = portable metrics

SELECT run_id,
       read_length_bp * read_count AS total_bp
FROM sequencing_runs
WHERE instrument = 'NovaSeq';

✓Illustrative result set

run_id	total_bp
R-901	120000000000
R-902	98000000000

📋SELECT shapes you will see in the wild

Know when * is a friend and when it is tech debt.

Form	Meaning	Production note
SELECT *	All columns from the row source.	Great in psql; risky in views, schema migrations break consumers silently.
SELECT DISTINCT …	Deduplicate entire projected rows (not per column).	Sort/hash cost grows with width, sometimes GROUP BY is clearer.
SELECT … AS alias	Rename output columns for humans and BI tools.	Avoid reserved words; quote only when forced by legacy schemas.

Push filters early

Predicates that shrink rows before joins usually win, smaller hash tables, cheaper nested loops, happier memory.

Mind NULL in projections

Outer joins introduce NULLs on optional sides, downstream aggregates may need COUNT(*) vs COUNT(col) discipline.

Pagination gotcha

Without a deterministic ORDER BY, LIMIT/OFFSET pages can shuffle between requests, users see duplicates or skips when data is mutating. Always include a stable sort key (often the primary key).

TL;DR

SELECT projects columns from the relation produced by FROM/JOIN after WHERE filtering, aliases defined in SELECT are not visible in WHERE in standard SQL (use a subquery/CTE). Expressions, literals, and star expansions are all valid projections; list only columns you need to reduce I/O and accidental data exposure.