Start with the relationship

Use a JOIN when the answer to a question requires data that lives in more than one table. A join key is the column, or columns, used to match rows. A join predicate is the condition after ON.

SELECT columns
FROM table_a a
JOIN table_b b ON a.key = b.key;

The sample data

These examples use Users, Orders, Products, and Employees. The main join keys are Users.id = Orders.user_id and Employees.manager_id = Employees.id.

• Ana has two matching orders.
• Ben and Cara have no orders.
• Order 103 points to a missing user.
• One-to-many joins can return more rows than the left table contains.

Relationship patterns

Before choosing a JOIN, identify the cardinality: how many rows on one side can match rows on the other side. Cardinality explains why some joins return one row per entity, while others multiply rows.

SELECT u.id, u.name, p.bio
FROM Users u
JOIN UserProfiles p ON p.user_id = u.id;

SELECT e.id, e.name, p.pass_number
FROM Employees e
LEFT JOIN ParkingPasses p ON p.employee_id = e.id;

SELECT u.id, u.name, o.id AS order_id
FROM Users u
JOIN Orders o ON o.user_id = u.id;

SELECT o.id AS order_id, o.total, u.name
FROM Orders o
JOIN Users u ON u.id = o.user_id;

SELECT s.name AS student_name, c.name AS class_name
FROM Students s
JOIN Enrollments e ON e.student_id = s.id
JOIN Classes c ON c.id = e.class_id;

SELECT e.name AS employee, m.name AS manager
FROM Employees e
LEFT JOIN Employees m ON m.id = e.manager_id;

SELECT oi.order_id, oi.product_id, p.name
FROM OrderItems oi
JOIN Products p
  ON p.id = oi.product_id
  AND p.vendor_id = oi.vendor_id;

INNER JOIN

SELECT *
FROM Users u
INNER JOIN Orders o ON u.id = o.user_id;

LEFT JOIN

SELECT *
FROM Users u
LEFT JOIN Orders o ON u.id = o.user_id;

Common pattern: users with no orders

SELECT u.id, u.name
FROM Users u
LEFT JOIN Orders o ON u.id = o.user_id
WHERE o.id IS NULL;

RIGHT JOIN

SELECT *
FROM Users u
RIGHT JOIN Orders o ON u.id = o.user_id;

Equivalent LEFT JOIN rewrite

SELECT *
FROM Orders o
LEFT JOIN Users u ON u.id = o.user_id;

FULL OUTER JOIN

SELECT *
FROM Users u
FULL OUTER JOIN Orders o ON u.id = o.user_id;

SELECT *
FROM Users u
LEFT JOIN Orders o ON u.id = o.user_id

UNION

SELECT *
FROM Users u
RIGHT JOIN Orders o ON u.id = o.user_id;

CROSS JOIN

CROSS JOIN produces the Cartesian product of two tables. Every row from the first table is paired with every row from the second table.

SELECT *
FROM Users
CROSS JOIN Products;

Use it intentionally for combinations such as users by products, dates by employees, or sizes by colors.

SELF JOIN

A self join is useful when rows in a table relate to other rows in the same table: managers and employees, categories and parent categories, comments and replies, or organization structures.

SELECT
  e.name,
  m.name AS manager
FROM Employees e
LEFT JOIN Employees m ON e.manager_id = m.id;

Anti join

An anti join returns rows from one table that do not have a match in another table. SQL does not usually have an ANTI JOIN keyword, so this is commonly written with LEFT JOIN ... WHERE right_table.id IS NULL or NOT EXISTS.

SELECT u.id, u.name
FROM Users u
LEFT JOIN Orders o ON u.id = o.user_id
WHERE o.id IS NULL;

SELECT u.id, u.name
FROM Users u
WHERE NOT EXISTS (
  SELECT 1
  FROM Orders o
  WHERE o.user_id = u.id
);

Use it for records missing a relationship, such as users with no orders, products never sold, or employees without assigned equipment.

Semi join

A semi join returns rows from one table when at least one match exists in another table, without duplicating the left-side row for every match. Use EXISTS.

SELECT u.id, u.name
FROM Users u
WHERE EXISTS (
  SELECT 1
  FROM Orders o
  WHERE o.user_id = u.id
);

Use this when you only need to know whether a match exists, not return columns from the joined table. An INNER JOIN can duplicate users when they have multiple orders. EXISTS keeps each user row once.

Non-equi join

Most examples use equality conditions, but joins can also use ranges or other comparisons.

SELECT
  o.id,
  o.total,
  d.discount_rate
FROM Orders o
JOIN DiscountBands d
  ON o.total BETWEEN d.min_total AND d.max_total;

Use non-equi joins for date windows, pricing tiers, geographic bounds, score bands, and other non-exact matches.

Joining multiple tables

Real queries often join more than two tables. Each join should have a clear reason and a clear predicate. Sketch the path before writing the query.

SELECT
  u.name,
  o.id AS order_id,
  p.name AS product_name
FROM Users u
JOIN Orders o ON u.id = o.user_id
JOIN OrderItems oi ON o.id = oi.order_id
JOIN Products p ON oi.product_id = p.id;

Filtering joins: ON vs WHERE

For INNER JOIN, putting a condition in ON or WHERE often produces the same final rows. For outer joins, the placement can change the result.

Keeps all users, only matching paid orders

SELECT *
FROM Users u
LEFT JOIN Orders o
  ON u.id = o.user_id
  AND o.status = 'paid';

Accidentally removes users with no paid orders

SELECT *
FROM Users u
LEFT JOIN Orders o ON u.id = o.user_id
WHERE o.status = 'paid';

Many-to-many joins

Many-to-many relationships need a third table that stores the relationship between two entities. A student can enroll in many classes, and a class can have many students.

SELECT
  s.name AS student_name,
  c.name AS class_name
FROM Students s
JOIN Enrollments e ON s.id = e.student_id
JOIN Classes c ON e.class_id = c.id;

ON, USING, and NATURAL JOIN

ON is the clearest and most flexible way to write join conditions.

SELECT *
FROM Users u
JOIN Orders o ON u.id = o.user_id;

USING can be shorter when both tables have the same join column name.

SELECT *
FROM Orders
JOIN Payments USING (order_id);

NATURAL JOIN automatically joins columns with the same names. Prefer ON for teaching and production examples. Use USING only when it improves clarity. Avoid NATURAL JOIN because schema changes can silently change query behavior.

JOIN vs UNION

JOINs combine columns from related rows.

SELECT u.name, o.total
FROM Users u
JOIN Orders o ON u.id = o.user_id;

UNION stacks rows from compatible result sets.

SELECT email FROM Customers
UNION
SELECT email FROM Leads;

Aggregating after JOINs

Joins often change row counts. Aggregations should account for one-to-many relationships.

SELECT
  u.id,
  u.name,
  COUNT(o.id) AS order_count,
  SUM(o.total) AS lifetime_value
FROM Users u
LEFT JOIN Orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

Which JOIN should you use?

Best practices

• Join using indexed keys when possible.
• Select only the columns you need.
• Avoid SELECT * in production queries.
• Use clear table aliases.
• Understand the query execution plan for expensive joins.
• Filter early when it reduces the dataset before joining.
• Confirm whether unmatched rows should be included before choosing the JOIN type.
• Know the relationship cardinality before joining: one-to-one, one-to-many, many-to-one, many-to-many, self-referencing, optional, or composite.
• Expect duplicate-looking rows when one left row matches multiple right rows.
• Use EXISTS when checking for existence instead of joining only to filter.
• Be careful with NULL values in join keys because NULL = NULL is not true in normal SQL comparisons.
• Avoid NATURAL JOIN in production examples.
• Use composite join conditions when a relationship depends on more than one column.
• Check row counts before and after each join while debugging.
• Add indexes on foreign keys and heavily used join/filter columns.

Common mistakes

• Missing the ON condition and accidentally creating a Cartesian product.
• Joining on the wrong column because names look similar.
• Using INNER JOIN when unmatched rows should still be reported.
• Putting right-table filters in WHERE after a LEFT JOIN.
• Assuming JOIN removes duplicates. It does not.
• Joining many-to-many tables without understanding the bridge table.
• Using SELECT * and creating unclear duplicate column names like id, created_at, or status.
• Aggregating after joins without checking whether the join multiplied rows.
• Confusing JOIN with UNION.

Database compatibility notes

• INNER JOIN, LEFT JOIN, and CROSS JOIN are broadly supported.
• RIGHT JOIN is supported by many databases, but some teams avoid it for readability.
• FULL OUTER JOIN is not available in MySQL as a direct keyword.
• SQLite supports RIGHT JOIN and FULL OUTER JOIN in modern versions, but older versions did not.
• Syntax details can vary around quoted identifiers, date functions, execution plans, and optimizer hints.