iwantcoding.com
🔥 Daily 👥 Rooms 🏆 Top Log in Sign up

SELECT

By · Reviewed & last updated · Part of PostgreSQL Tutorial

SELECT is the everyday query. Master the order — FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT — and the rest of SQL falls into place.

The clauses you actually use

EXAMPLE
-- Basic
SELECT id, email FROM users WHERE active = true;

-- Joins + aliases
SELECT u.email, count(p.id) AS post_count
FROM   users u
LEFT   JOIN posts p ON p.user_id = u.id
WHERE  u.created_at >= now() - INTERVAL '7 days'
GROUP  BY u.id, u.email
HAVING count(p.id) > 0
ORDER  BY post_count DESC
LIMIT  20;

-- DISTINCT — first row per partition
SELECT DISTINCT ON (country) country, city, population
FROM   cities
ORDER  BY country, population DESC;

-- CTE for readability
WITH active_users AS (
    SELECT * FROM users WHERE last_login >= now() - INTERVAL '30 days'
),
post_counts AS (
    SELECT user_id, count(*) AS n FROM posts GROUP BY user_id
)
SELECT u.email, COALESCE(c.n, 0) AS posts
FROM   active_users u
LEFT   JOIN post_counts c ON c.user_id = u.id
ORDER  BY posts DESC;

Why it matters

Run EXPLAIN ANALYZE on slow queries. The plan tree shows you exactly where the time goes — usually a sequential scan that wants an index, or a Hash Join that’s spilling to disk.

Tip: Tweak the snippet with Try it Yourself », then sit the quiz at the bottom of the page.

Example

Example
SELECT id, name, created_at
FROM users
WHERE email LIKE '%@example.com'
ORDER BY created_at DESC
LIMIT 20;
Try it Yourself »

Exercise

All rows from the users table.

SELECT FROM users;

Discussion

Loading…