Querying Data with SELECT

1. Selecting All Columns

SELECT * FROM customers;

Avoid	Why
SELECT *	Schema drift, sends unneeded bytes, blocks index-only scans

SELECT id, email, full_name FROM customers WHERE status = 'active';

SELECT id AS customer_id, full_name AS name FROM customers;

Form	Notes
AS keyword	Optional but clearer
Quoted alias	`SELECT id AS "Customer ID"` preserves case/space

SELECT total_cents/100.0 AS total_dollars,
       upper(email) AS email_uc
  FROM orders;

SELECT DISTINCT status FROM orders;
SELECT DISTINCT status, customer_id FROM orders;

-- Latest order per customer
SELECT DISTINCT ON (customer_id) *
  FROM orders
 ORDER BY customer_id, placed_at DESC;

Rule	Detail
ORDER BY required	First column must match DISTINCT ON list
Returns	First row of each group per ORDER BY

SELECT * FROM orders ORDER BY placed_at DESC LIMIT 10;

SELECT * FROM orders ORDER BY id OFFSET 20 LIMIT 10;

Warning: Large OFFSET is O(N) scan; use keyset pagination WHERE id > :last_id ORDER BY id LIMIT n.

Approach	When
OFFSET/LIMIT	Small datasets, admin UIs
Keyset (cursor)	Infinite scroll, large tables
DECLARE CURSOR	Server-side cursor, low-memory paging

SELECT * FROM orders ORDER BY placed_at DESC
 FETCH FIRST 10 ROWS ONLY;
SELECT * FROM orders ORDER BY total_cents DESC
 FETCH FIRST 5 ROWS WITH TIES;

Clause	Behavior
ROWS ONLY	Strict N rows
WITH TIES	Includes peers per ORDER BY PG 13+