SQL — Self Join

Basic INNER JOIN

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

With table aliases

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

Multiple joins

SELECT u.name, o.total, p.name AS product
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN products p ON o.product_id = p.id;

INNER JOIN with WHERE

SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.total > 100;

Self join

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

Mini Practice

1. Join two tables
2. Use table aliases
3. Join multiple tables
4. Filter joined results

Up Next

Continue with Left Join - Left join operations.