PostgreSQL
Queries & Joins
Queries & Joins Basic SELECT -- Basic query SELECT id, name, email FROM users WHERE active = true ORDER BY name LIMIT 10 OFFSET 20; -- Aliases and expressions S…
Queries & Joins
Basic SELECT
-- Basic query
SELECT id, name, email FROM users WHERE active = true ORDER BY name LIMIT 10 OFFSET 20;
-- Aliases and expressions
SELECT
first_name || ' ' || last_name AS full_name,
EXTRACT(YEAR FROM created_at) AS year,
COALESCE(phone, 'N/A') AS phone
FROM users;
-- Distinct
SELECT DISTINCT department FROM employees;
-- Aggregate functions
SELECT
department,
COUNT(*) AS total,
AVG(salary) AS avg_salary,
MAX(salary) AS max_salary
FROM employees
GROUP BY department
HAVING COUNT(*) > 5
ORDER BY avg_salary DESC;JOIN Types
-- INNER JOIN — only matching rows
SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id;
-- LEFT JOIN — all from left, NULLs for unmatched right
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;
-- RIGHT JOIN — all from right
SELECT u.name, o.total
FROM orders o
RIGHT JOIN users u ON o.user_id = u.id;
-- FULL OUTER JOIN — all rows from both
SELECT u.name, o.total
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;
-- CROSS JOIN — cartesian product
SELECT p.name, c.name AS color
FROM products p
CROSS JOIN colors c;
-- Self join
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;Subqueries
-- Subquery in WHERE
SELECT name FROM users
WHERE id IN (SELECT user_id FROM orders WHERE total > 100);
-- Correlated subquery
SELECT name, salary
FROM employees e
WHERE salary > (
SELECT AVG(salary) FROM employees WHERE department = e.department
);
-- EXISTS
SELECT name FROM users u
WHERE EXISTS (
SELECT 1 FROM orders WHERE user_id = u.id AND status = 'pending'
);
-- Subquery in FROM (derived table)
SELECT dept, avg_salary
FROM (
SELECT department AS dept, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
) sub
WHERE avg_salary > 50000;CTEs (Common Table Expressions)
-- Basic CTE
WITH recent_orders AS (
SELECT user_id, COUNT(*) AS cnt
FROM orders
WHERE created_at > NOW() - INTERVAL '30 days'
GROUP BY user_id
)
SELECT u.name, ro.cnt
FROM users u
JOIN recent_orders ro ON u.id = ro.user_id;
-- Multiple CTEs
WITH
active_users AS (SELECT id FROM users WHERE active = true),
user_orders AS (SELECT user_id, SUM(total) AS revenue FROM orders GROUP BY user_id)
SELECT u.id, uo.revenue
FROM active_users u
JOIN user_orders uo ON u.id = uo.user_id;
-- Recursive CTE — org chart / tree traversal
WITH RECURSIVE org_tree AS (
-- Base case: top-level employees
SELECT id, name, manager_id, 0 AS depth
FROM employees WHERE manager_id IS NULL
UNION ALL
-- Recursive case
SELECT e.id, e.name, e.manager_id, ot.depth + 1
FROM employees e
JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT * FROM org_tree ORDER BY depth, name;Useful Functions
-- String functions
SELECT
UPPER(name), LOWER(email),
LENGTH(name),
SUBSTRING(email FROM 1 FOR 5),
TRIM(' hello '),
REPLACE(name, 'Ltd', 'Limited'),
CONCAT(first_name, ' ', last_name),
SPLIT_PART(email, '@', 2) AS domain
FROM users;
-- Date functions
SELECT
NOW(), CURRENT_DATE, CURRENT_TIME,
AGE(NOW(), created_at) AS account_age,
DATE_TRUNC('month', created_at) AS month,
TO_CHAR(created_at, 'YYYY-MM-DD') AS formatted
FROM users;
-- Conditional
SELECT
name,
CASE
WHEN salary > 100000 THEN 'senior'
WHEN salary > 60000 THEN 'mid'
ELSE 'junior'
END AS level,
NULLIF(phone, '') AS phone, -- returns NULL if empty string
GREATEST(a, b, c),
LEAST(a, b, c)
FROM employees;