What are SQL JOINs? | SQL Interview Questions

What You'll Learn

Types of JOINs
When to use each
Examples

JOIN Types

INNER JOIN

Returns only matching rows from both tables.

query.sqlSQL

SELECT users.name, orders.total
FROM users
INNER JOIN orders ON users.id = orders.user_id;

LEFT JOIN (LEFT OUTER JOIN)

Returns all rows from left table + matching from right.

query.sqlSQL

SELECT users.name, orders.total
FROM users
LEFT JOIN orders ON users.id = orders.user_id;
-- Users without orders will have NULL for orders.total

RIGHT JOIN (RIGHT OUTER JOIN)

Returns all rows from right table + matching from left.

query.sqlSQL

SELECT users.name, orders.total
FROM users
RIGHT JOIN orders ON users.id = orders.user_id;

FULL OUTER JOIN

Returns all rows from both tables.

query.sqlSQL

SELECT users.name, orders.total
FROM users
FULL OUTER JOIN orders ON users.id = orders.user_id;

Visual Representation

query.sqlSQL

INNER JOIN:     Only intersection
LEFT JOIN:      All left + intersection
RIGHT JOIN:     All right + intersection
FULL JOIN:      Everything from both

Multiple JOINs

query.sqlSQL

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 order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE o.status = 'completed';

Self JOIN

query.sqlSQL

-- Find employees and their managers
SELECT
  e.name AS employee,
  m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;