Joins and Multi-Table Queries

Introduction

Real apps store related data in multiple tables. JOIN combines rows based on keys—authors with posts, orders with customers. This chapter covers INNER, LEFT, RIGHT, and CROSS joins, self-joins, UNION, and why implicit comma joins are a bad habit.

Prerequisites

• SELECT Queries Basics
• Inserting Data sample users and posts

Setup Extra Sample (Optional)

Categories and post mapping:

Insert links after you know posts.id values.

INNER JOIN

Rows where join condition matches both sides:

SELECT
  p.id,
  p.title,
  u.display_name AS author
FROM posts p
INNER JOIN users u ON u.id = p.user_id;

Code explanation:

• INNER JOIN drops posts with invalid user_id and users with zero posts (for this query shape)
• ON defines match—usually foreign key = primary key

Only published posts:

SELECT p.title, u.email
FROM posts p
INNER JOIN users u ON u.id = p.user_id
WHERE p.published_at IS NOT NULL;

LEFT JOIN

All rows from left table; NULLs on right when no match:

SELECT
  u.display_name,
  p.title
FROM users u
LEFT JOIN posts p ON p.user_id = u.id;

Users with no posts still appear—p.title is NULL.

Filter users who never posted:

SELECT u.id, u.display_name
FROM users u
LEFT JOIN posts p ON p.user_id = u.id
WHERE p.id IS NULL;

Code explanation:

• WHERE p.id IS NULL after LEFT JOIN finds non-matching right side

RIGHT JOIN

Mirror of LEFT—keep all from right table:

SELECT u.display_name, p.title
FROM users u
RIGHT JOIN posts p ON p.user_id = u.id;

Rare in practice—swap tables and use LEFT JOIN for readability.

FULL OUTER JOIN

MySQL has no FULL OUTER JOIN keyword—emulate with UNION of LEFT and RIGHT or use left/right patterns. PostgreSQL supports native FULL OUTER.

CROSS JOIN

Cartesian product—every row paired with every row:

SELECT u.display_name, c.name AS category
FROM users u
CROSS JOIN categories c;

Use deliberately (small sets); accidental cross join omits ON and explodes row count.

Self Join

Table joined to itself—e.g. employees and managers (same users table demo with alias):

-- If users had manager_id referencing users.id:
-- SELECT e.display_name AS employee, m.display_name AS manager
-- FROM users e
-- LEFT JOIN users m ON m.id = e.manager_id;

Concept: two aliases e and m on one physical table.

UNION and UNION ALL

Combine result sets with same column count/types:

SELECT email AS contact FROM users
UNION
SELECT CONCAT('post-', id) AS contact FROM posts;

UNION removes duplicate rows; UNION ALL keeps duplicates and is faster:

SELECT id, title FROM posts WHERE user_id = 1
UNION ALL
SELECT id, title FROM posts WHERE user_id = 2;

NATURAL JOIN and Implicit Join (Avoid)

NATURAL JOIN matches all same-named columns—fragile when schemas change.

Old-style comma join:

SELECT p.title, u.display_name
FROM posts p, users u
WHERE p.user_id = u.id;

Works but always prefer explicit JOIN … ON—clearer and harder to forget WHERE.

Join Diagram Summary

INNER:     only overlap
LEFT:      all left + matching right
RIGHT:     all right + matching left
CROSS:     every combination

FAQ

Join vs subquery?

Often equivalent—optimizer picks plan; readability and index use decide.

Many-to-many?

Use junction table post_categories joining posts and categories.

ON vs WHERE?

ON = join condition; WHERE = filter after join—put outer join null checks carefully.

Join order performance?

Optimizer reorders—index foreign keys and primary keys.

Duplicate rows in result?

Usually many-to-many join without aggregation—add DISTINCT or GROUP BY.

Full join in MySQL?

Use UNION workaround or rewrite with LEFT joins.