Most production APIs need to join multiple tables. JOINs can be chained, so it is important to understand both join types and join order.

SELECT  a.col, b.col, c.col
FROM       table_a a             -- Starting table
INNER JOIN table_b b             -- Join only rows that match on both sides
  ON a.b_id = b.id
LEFT JOIN  table_c c             -- Keep a rows even without a match in c (fill with NULL)
  ON b.c_id = c.id;

Join the three tables order_items (order line items), products (products), and categories (categories), and attach product and category names to each order item. Include products without a category and show category_name as 'Uncategorized'.

Expected output (products without a category are shown as 'Uncategorized'):

SELECT
  oi.item_id,
  oi.order_id,
  p.name         AS product_name,
  COALESCE(c.name, 'Uncategorized') AS category_name, -- Uncategorized = a product with no LEFT JOIN match
  oi.qty,
  oi.price * oi.qty AS subtotal        -- Subtotal = unit price × quantity

FROM       order_items oi
INNER JOIN products   p             -- Products are required, so use INNER JOIN
  ON oi.product_id = p.product_id
LEFT JOIN  categories c             -- Keep products without categories (LEFT JOIN)
  ON p.category_id = c.category_id

ORDER BY oi.item_id;

/*
  Logical execution order (three-table JOIN query):
  1. FROM order_items oi          → Read rows
  2. INNER JOIN products p ON ... → Join (matching rows only)
  3. LEFT JOIN categories c ON ...→ Join (keep every row from the left table)
  4. SELECT                       → Evaluate columns (subtotal)
  5. ORDER BY oi.item_id          → Sort and return
*/

UNION combines the results of multiple SELECT statements vertically. UNION ALL combines every row, including duplicates, while UNION without ALL removes duplicate rows.

SELECT col1, col2 FROM table_a     -- upper SELECT
UNION ALL                           -- Combine all rows vertically, including duplicates (faster than UNION)
SELECT col1, col2 FROM table_b;    -- Lower SELECT (match its column count and types to the upper SELECT)

SELECT col1, col2 FROM table_a
UNION                               -- Combine rows vertically after removing duplicates (DISTINCT runs internally)
SELECT col1, col2 FROM table_b;

Use JOIN to place different tables side by side (add columns), and UNION to stack them vertically (add rows).

The system has two notification tables: email_notifications and push_notifications. Combine notifications from both tables into one result, return the list in descending sent_at order, and add a notification type ('email'/'push') to each row.

Expected output (Combine both tables, ordered by sent_at descending):

SELECT
  'email'  AS notification_type,  -- Add the type as a fixed value
  user_id,
  subject  AS content,            -- Normalize the column name to content
  sent_at
FROM  email_notifications

UNION ALL                         -- Stack vertically without deduplication (fast)
SELECT
  'push'   AS notification_type,  -- Add the type as a fixed value
  user_id,
  message  AS content,            -- Match the column name used by email
  sent_at
FROM  push_notifications

ORDER BY sent_at DESC;  -- Sort the entire UNION result newest first

/*
  Logical execution order (UNION ALL query):
  1. Upper SELECT from email_notifications → Read rows
  2. Lower SELECT from push_notifications  → Read rows
  3. UNION ALL                      → combine the sets
  4. ORDER BY sent_at DESC          → Sort and return
*/

A transaction treats multiple SQL statements as one unit of work. It starts with BEGIN, commits with COMMIT when everything succeeds, and rolls back all changes with ROLLBACK if an error occurs (atomicity).

BEGIN;                              -- Start the transaction (START TRANSACTION also works)

  UPDATE table_a SET col = val;    -- operation1
  INSERT INTO table_b VALUES (...); -- operation2
  -- If an error occurs here ↓

COMMIT;                             -- Commit all operations (persist them in the database)
-- Or --
ROLLBACK;                           -- Cancel all operations (return to the pre-BEGIN state)

Implement the e-commerce order flow (① INSERT an order row into orders, ② INSERT a line item into order_items, and ③ UPDATE inventory in products) as one atomic transaction. If any one of the three operations fails, make sure all of them are rolled back.

State of each table after a successful transaction (after COMMIT):

If an error occurs partway through, ROLLBACK returns every table to its pre-BEGIN state

BEGIN;  -- Start the transaction (uncommitted until COMMIT)

-- ① Add the order header to orders
INSERT INTO orders (order_id, user_id, total_amount, status)
VALUES (1001, 'U01', 1500, 'pending');

-- ② Add the order line item to order_items
INSERT INTO order_items (order_id, product_id, qty, price)
VALUES (1001, 'P01', 3, 500);

-- ③ Decrease products inventory by the purchased quantity
UPDATE products
SET   stock = stock - 3    -- Decrease stock by 3 (relative update)
WHERE product_id = 'P01';  -- WHERE is required (without it, every row is updated)

COMMIT;  -- All operations succeeded → commit permanently (visible to other sessions)

/*
  Logical execution order (success path):
  1. BEGIN               → Start the transaction
  2. INSERT orders       → Insert rows (uncommitted)
  3. INSERT order_items  → Insert rows (uncommitted)
  4. UPDATE products     → Update the row (uncommitted)
  5. COMMIT              → Commit the changes permanently
  */

UPSERT (INSERT + UPDATE) performs “INSERT if absent, UPDATE if present” in one statement. In PostgreSQL, use INSERT ... ON CONFLICT.

INSERT INTO table_name (col1, col2)
VALUES ('val1', 'val2')
ON CONFLICT (unique_col)            -- Specify the column whose unique constraint may be violated
DO UPDATE SET                       -- The UPDATE operation to run on conflict
  col2 = EXCLUDED.col2;            -- EXCLUDED = the new value that was about to be inserted

-- When nothing should be done on conflict:
ON CONFLICT (unique_col) DO NOTHING; -- Ignore the error and leave the existing row unchanged

Synchronize profile data obtained from an external API into the user_profiles table. INSERT a new row when user_id is absent; when it exists, update only name and updated_at (do not change email).

user_profiles after UPSERT:

U01: existing row → name updated and email retained / U02: absent from sync data, so unchanged / U03: new row → INSERT

-- Sync U01: existing row → update name and updated_at (email is unchanged)
INSERT INTO user_profiles (user_id, name, email, updated_at)
VALUES (
  'U01',
  'Taro Tanaka (renamed)',
  'tanaka@example.com',
  NOW()                               -- Set the sync timestamp
)
ON CONFLICT (user_id)               -- What to do when user_id conflicts (an existing row is present)
DO UPDATE SET                       -- Run UPDATE when a conflict occurs
  name       = EXCLUDED.name,      -- EXCLUDED: keyword for referencing the new value that was about to be inserted
  updated_at = EXCLUDED.updated_at; -- Leaving email out of SET preserves its original value

-- Sync U03: new row → INSERT (no ON CONFLICT occurs)
INSERT INTO user_profiles (user_id, name, email, updated_at)
VALUES (
  'U03',
  'Ichiro Suzuki',
  'suzuki@example.com',
  NOW()
)
ON CONFLICT (user_id)               -- Same UPSERT syntax, but no conflict → normal INSERT
DO UPDATE SET
  name       = EXCLUDED.name,
  updated_at = EXCLUDED.updated_at;

/*
  Logical execution order (UPSERT / U01 case):
  1. INSERT INTO user_profiles → Check the unique constraint
  2. Detect a unique-constraint violation → Conflict with the existing row
  3. ON CONFLICT DO UPDATE     → Update the existing row

  Logical execution order (UPSERT / U03 case):
  1. INSERT INTO user_profiles → Check the unique constraint
  2. No conflict                  → No constraint is violated
  3. normal INSERT             → Insert rows
*/