A
AiTechWorlds
AiTechWorlds
INNER/LEFT/RIGHT/FULL JOIN with visual diagrams, correlated subqueries, CTEs, and EXISTS vs IN performance tips.
Returns only rows with matching values in both tables.
SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id;
-- Result: Alice (50), Alice (75)
-- Bob, Carol, Dave not in orders → excluded
-- Order 103 (user_id=4) → Dave not in users wait... Dave IS id=4
-- Actually: Dave (20)Returns all rows from the left table, matched rows from right (NULL if no match).
SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;
-- Result:
-- Alice 50.00
-- Alice 75.00
-- Bob NULL ← Bob has no orders
-- Carol NULL ← Carol has no orders
-- Dave 20.00Returns all rows from the right table, matched rows from left.
SELECT u.name, o.total
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;
-- Rarely used; swap table order and use LEFT JOIN insteadReturns all rows from both tables, NULL where no match.
SELECT u.name, o.total
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;
-- Shows all users AND all orders, NULLs for no-matches
-- Note: MySQL doesn't support FULL OUTER JOIN; emulate with UNIONReturns the Cartesian product — every row from A × every row from B.
SELECT colors.name, sizes.label
FROM colors CROSS JOIN sizes;
-- 3 colors × 4 sizes = 12 rowsJoin a table to itself — useful for hierarchical data.
-- employees table: id, name, manager_id
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;| JOIN Type | Includes non-matches from left? | From right? |
|---|---|---|
| INNER | No | No |
| LEFT | Yes (NULL right) | No |
| RIGHT | No | Yes (NULL left) |
| FULL OUTER | Yes | Yes |
| CROSS | N/A (all combinations) | N/A |
-- Find users who have placed orders over $100
SELECT name
FROM users
WHERE id IN (
SELECT user_id
FROM orders
WHERE total > 100
);Runs once per row of the outer query — can be slow on large tables.
-- Find users whose total spending > average order total
SELECT name
FROM users u
WHERE (
SELECT SUM(total)
FROM orders o
WHERE o.user_id = u.id -- references outer query's u.id
) > (SELECT AVG(total) FROM orders);SELECT dept, avg_salary
FROM (
SELECT department AS dept, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
) AS dept_stats
WHERE avg_salary > 60000;SELECT
name,
(SELECT COUNT(*) FROM orders WHERE orders.user_id = users.id) AS order_count
FROM users;More readable than nested subqueries:
WITH high_value_orders AS (
SELECT user_id, SUM(total) AS lifetime_value
FROM orders
GROUP BY user_id
HAVING SUM(total) > 500
),
vip_users AS (
SELECT u.name, hvo.lifetime_value
FROM users u
JOIN high_value_orders hvo ON u.id = hvo.user_id
)
SELECT * FROM vip_users ORDER BY lifetime_value DESC;WITH RECURSIVE org_chart AS (
-- Base case: top-level manager
SELECT id, name, manager_id, 0 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- Recursive case: join with children
SELECT e.id, e.name, e.manager_id, oc.level + 1
FROM employees e
JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart ORDER BY level;| Situation | Prefer |
|---|---|
| Filter based on existence | EXISTS / IN subquery |
| Need columns from both tables | JOIN |
| Complex multi-step logic | CTE |
| Performance-critical, indexed FK | JOIN (usually faster) |
| Count/aggregate in SELECT | Scalar subquery or window function |
-- EXISTS: stops at first match — more efficient for large sets
SELECT name FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);
-- IN: loads all values — can be slow with large subquery results
SELECT name FROM users
WHERE id IN (SELECT user_id FROM orders);SELECT * with JOINs — ambiguous column names and unnecessary data transferDownload SQL JOINs & Subqueries: Visual Reference
Get this note + 100s more free on Telegram
Join AiTechWorlds on Telegram and get daily AI tips, prompt engineering templates, coding resources, and exclusive content — 100% free!
No spam. Leave anytime.