SELECT: Asking the Database a Question

SELECT is the question

SELECT is the only SQL statement that asks rather than changes. It returns rows; it never modifies the database. "Show me all users," "what was last month's revenue," "which orders shipped today" — every read query starts with SELECT.

The shape of every SELECT

Memorise this clause order — it shows up in every query, in this order, until you retire:

SELECT <columns or expressions>
FROM   <table or join>
WHERE  <row filter>
GROUP  BY <grouping>
HAVING <group filter>
ORDER  BY <sort>
LIMIT  <cap> OFFSET <skip>

The execution order is a different list (FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT). We'll come back to that — it explains why HAVING can reference aggregates but WHERE cannot.

Specific columns beat SELECT *

In production code, list the columns you want. SELECT * ships extra bytes over the wire, breaks subtly when columns are added or reordered, and obscures the query's intent. Use SELECT * in the psql terminal for exploration; almost never in committed code.

Code

Basic shapes·sql

-- All columns, all rows (exploratory only)
SELECT * FROM users LIMIT 10;

-- Specific columns (production-ready)
SELECT id, name, email FROM users;

-- Computed columns + aliases
SELECT id,
       UPPER(name)                AS shouting_name,
       price * quantity           AS line_total,
       AGE(now(), created_at)     AS account_age
FROM   order_items;

DISTINCT and basic functions·sql

SELECT DISTINCT category FROM products ORDER BY category;

SELECT COUNT(*) AS total,
       COUNT(DISTINCT customer_id) AS unique_customers
FROM   orders;

SELECT: Asking the Database a Question

SELECT is the question

The shape of every SELECT

Specific columns beat SELECT *

Code

External links

Exercise

Progress

Comments 0