sql joins query-optimization performance database

Advanced SQL joins and query optimization

Maria Garcia Feb 2026

2 tabs

-- INNER JOIN: Only matching rows
SELECT
  users.name,
  orders.order_number,
  orders.total
FROM users
INNER JOIN orders ON users.id = orders.user_id
WHERE orders.status = 'completed';

-- LEFT JOIN: All users, even without orders
SELECT
  users.name,
  COUNT(orders.id) as order_count,
  COALESCE(SUM(orders.total), 0) as total_spent
FROM users
LEFT JOIN orders ON users.id = orders.user_id
GROUP BY users.id, users.name;

-- Multiple joins
SELECT
  u.name as customer_name,
  o.order_number,
  p.name as product_name,
  oi.quantity,
  oi.price
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id
WHERE o.created_at >= CURRENT_DATE - INTERVAL '30 days';

-- Self join: Finding users in same city
SELECT
  u1.name as user1,
  u2.name as user2,
  u1.city
FROM users u1
INNER JOIN users u2 ON u1.city = u2.city AND u1.id < u2.id
ORDER BY u1.city, u1.name;

-- Join with aggregation
SELECT
  c.name as category,
  COUNT(p.id) as product_count,
  AVG(p.price) as avg_price,
  MIN(p.price) as min_price,
  MAX(p.price) as max_price
FROM categories c
LEFT JOIN products p ON c.id = p.category_id
GROUP BY c.id, c.name
HAVING COUNT(p.id) > 0;

-- FULL OUTER JOIN: All rows from both tables
SELECT
  COALESCE(a.name, 'No author') as author,
  COALESCE(b.title, 'No book') as book
FROM authors a
FULL OUTER JOIN books b ON a.id = b.author_id;

-- CROSS JOIN: Cartesian product (use carefully!)
SELECT
  sizes.name as size,
  colors.name as color
FROM sizes
CROSS JOIN colors
WHERE sizes.active = true AND colors.active = true;

-- Join with subquery for filtering
SELECT
  u.name,
  u.email,
  recent_orders.order_count
FROM users u
INNER JOIN (
  SELECT
    user_id,
    COUNT(*) as order_count
  FROM orders
  WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
  GROUP BY user_id
  HAVING COUNT(*) >= 3
) recent_orders ON u.id = recent_orders.user_id;

-- LATERAL join (PostgreSQL): Correlated subquery per row
SELECT
  u.name,
  latest_order.order_number,
  latest_order.total
FROM users u
CROSS JOIN LATERAL (
  SELECT order_number, total, created_at
  FROM orders
  WHERE orders.user_id = u.id
  ORDER BY created_at DESC
  LIMIT 1
) latest_order;

-- Anti-join: Find users with NO orders
SELECT u.*
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;

-- More efficient anti-join with NOT EXISTS
SELECT u.*
FROM users u
WHERE NOT EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.user_id = u.id
);

-- Join with conditional logic
SELECT
  p.name as product,
  CASE
    WHEN i.quantity > 100 THEN 'High Stock'
    WHEN i.quantity > 20 THEN 'Medium Stock'
    WHEN i.quantity > 0 THEN 'Low Stock'
    ELSE 'Out of Stock'
  END as stock_status,
  COALESCE(i.quantity, 0) as current_stock
FROM products p
LEFT JOIN inventory i ON p.id = i.product_id;

2 files · sql Explain with highlit

SQL joins combine data from multiple tables. INNER JOIN returns matching rows only. LEFT/RIGHT JOIN includes all rows from one table, nulls for non-matches. FULL OUTER JOIN combines both. I use CROSS JOIN for Cartesian products sparingly—performance killer. JOIN order matters for query optimization. Understanding execution plans reveals join strategies—nested loops, hash joins, merge joins. Always join on indexed columns. Subqueries can often be rewritten as joins for better performance. WITH clauses (CTEs) improve readability but may hinder optimization. EXISTS outperforms IN for large datasets. Joining on multiple conditions requires careful indexing. Master joins to write efficient, maintainable queries.