SQL Advanced

SQL Joins

Combine data from multiple tables using INNER JOIN, LEFT JOIN, and other join types.

SQL JOIN

A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

Types of Joins

INNER JOIN — Returns rows when there is a match in both tables
LEFT JOIN — Returns all rows from the left table, matched rows from the right
RIGHT JOIN — Returns all rows from the right table, matched rows from the left
FULL OUTER JOIN — Returns all rows when there is a match in either table
CROSS JOIN — Returns all combinations (Cartesian product)
SELF JOIN — Join a table with itself

Understanding JOIN Results

INNER JOIN: only matched records (intersection)

LEFT JOIN: all left records + matched right records (null if no match)

FULL OUTER JOIN: all records from both tables (null where no match)

Example

sql

-- Sample tables: users, posts, comments

-- INNER JOIN - only matching records
SELECT
  u.name,
  p.title,
  p.created_at
FROM posts p
INNER JOIN users u ON p.author_id = u.id;

-- LEFT JOIN - all posts, even without author
SELECT
  p.title,
  u.name AS author_name
FROM posts p
LEFT JOIN users u ON p.author_id = u.id;

-- Multiple JOINs
SELECT
  p.title,
  u.name AS author,
  COUNT(c.id) AS comment_count,
  AVG(r.rating) AS avg_rating
FROM posts p
INNER JOIN users u ON p.author_id = u.id
LEFT JOIN comments c ON c.post_id = p.id
LEFT JOIN ratings r ON r.post_id = p.id
GROUP BY p.id, p.title, u.name
ORDER BY comment_count DESC;

-- Self JOIN (employee hierarchy)
SELECT
  e.name AS employee,
  m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

Want to run this code interactively?

Try in Compiler