🏠 Home / Hub

🐘 PostgreSQL Lesson 03 — Queries & JOINs

← Back to PostgreSQL Menu  |  🏠 Hub

1. SELECT Basics

-- Basic SELECT
SELECT * FROM users;
SELECT id, name, email FROM users;
SELECT name AS username, email AS contact FROM users;

-- WHERE conditions
SELECT * FROM users WHERE active = true;
SELECT * FROM users WHERE role = 'admin' AND active = true;
SELECT * FROM users WHERE role IN ('admin', 'editor');
SELECT * FROM users WHERE name LIKE 'Ko%';        -- starts with Ko
SELECT * FROM users WHERE name ILIKE '%ko%';       -- case-insensitive
SELECT * FROM users WHERE created_at > '2024-01-01';
SELECT * FROM users WHERE age BETWEEN 20 AND 30;
SELECT * FROM users WHERE phone IS NULL;
SELECT * FROM users WHERE phone IS NOT NULL;

-- ORDER BY
SELECT * FROM users ORDER BY name ASC;
SELECT * FROM users ORDER BY created_at DESC, name ASC;

-- LIMIT & OFFSET (pagination)
SELECT * FROM posts ORDER BY created_at DESC LIMIT 10;
SELECT * FROM posts ORDER BY created_at DESC LIMIT 10 OFFSET 20;
-- Page 3, 10 items each: OFFSET = (3-1) * 10 = 20

2. JOINs

-- Sample data:
-- users: id, name
-- posts: id, user_id, title
-- comments: id, post_id, user_id, body

-- INNER JOIN — both sides match
SELECT u.name, p.title
FROM posts p
INNER JOIN users u ON p.user_id = u.id;

-- LEFT JOIN — all posts, even without user
SELECT p.title, u.name AS author
FROM posts p
LEFT JOIN users u ON p.user_id = u.id;

-- Multi-table JOIN
SELECT
  u.name         AS author,
  p.title        AS post_title,
  COUNT(c.id)    AS comment_count
FROM posts p
JOIN users u    ON p.user_id = u.id
LEFT JOIN comments c ON c.post_id = p.id
GROUP BY u.name, p.title
ORDER BY comment_count DESC;

-- Self JOIN (manager-employee)
SELECT
  e.name  AS employee,
  m.name  AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

3. Subqueries

-- Subquery in WHERE
SELECT name FROM users
WHERE id IN (SELECT user_id FROM posts WHERE published = true);

-- Subquery as column (scalar)
SELECT
  name,
  (SELECT COUNT(*) FROM posts WHERE user_id = u.id) AS post_count
FROM users u;

-- EXISTS
SELECT * FROM users u
WHERE EXISTS (
  SELECT 1 FROM posts p WHERE p.user_id = u.id AND p.published = true
);

-- WITH (CTE — Common Table Expression) — more readable
WITH active_users AS (
  SELECT id, name FROM users WHERE active = true
),
user_posts AS (
  SELECT user_id, COUNT(*) AS post_count FROM posts GROUP BY user_id
)
SELECT
  au.name,
  COALESCE(up.post_count, 0) AS posts
FROM active_users au
LEFT JOIN user_posts up ON up.user_id = au.id
ORDER BY posts DESC;
💡 CTE (WITH clause) = complex query ကို readable sub-steps ခွဲဖတ် — PostgreSQL ကောင်းကောင်း optimize

4. PostgreSQL-specific SELECT Features

-- DISTINCT ON (PG unique!)
SELECT DISTINCT ON (user_id) user_id, title, created_at
FROM posts
ORDER BY user_id, created_at DESC;
-- Each user ရဲ့ latest post ပဲ ပြ

-- RETURNING — INSERT/UPDATE result ပြန်ပေး
INSERT INTO users (name, email) VALUES ('Ko', 'ko@test.com')
RETURNING id, name, created_at;   -- ← inserted row ကို immediately return

UPDATE users SET active = false WHERE id = 5
RETURNING id, name, active;

DELETE FROM posts WHERE id = 10
RETURNING id, title;

-- ILIKE — case-insensitive search (PG only)
SELECT * FROM users WHERE email ILIKE '%@gmail.com';

-- Regular expression
SELECT * FROM users WHERE phone ~ '^\+95';    -- starts with +95
SELECT * FROM posts WHERE title ~* 'vue|react';  -- case-insensitive regex

-- String functions
SELECT
  UPPER(name),
  LOWER(email),
  LENGTH(name),
  TRIM(name),
  LEFT(name, 3),
  SPLIT_PART(email, '@', 2)   -- domain part
FROM users;

← PostgreSQL 02  |  Next: PostgreSQL 04 → Functions →

📌 Study Checklist