RIGHT JOIN & FULL OUTER JOIN
A RIGHT JOIN is the mirror image of a LEFT JOIN — it returns all rows from the right table and matching rows from the left table. In practice, most developers prefer LEFT JOIN and simply swap the table order.
A FULL OUTER JOIN returns all rows from both tables, with NULLs where there is no match on either side. This gives you a complete picture of all data from both tables.
- RIGHT JOIN: All rows from the right table, matching rows from the left
- FULL OUTER JOIN: All rows from both tables, NULLs where no match exists
- Note: MySQL does not support FULL OUTER JOIN directly — use UNION instead
Example
-- RIGHT JOIN: All orders, even those without a customer
SELECT
c.name AS customer,
o.product,
o.amount
FROM customers c
RIGHT JOIN orders o ON c.id = o.customer_id;
-- FULL OUTER JOIN (PostgreSQL syntax)
SELECT
c.name AS customer,
o.product,
o.amount
FROM customers c
FULL OUTER JOIN orders o ON c.id = o.customer_id;
-- FULL OUTER JOIN in MySQL (using UNION)
SELECT c.name, o.product, o.amount
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
UNION
SELECT c.name, o.product, o.amount
FROM customers c
RIGHT JOIN orders o ON c.id = o.customer_id; Notes
- In practice, INNER JOIN and LEFT JOIN cover 95% of all use cases. RIGHT JOIN and FULL OUTER JOIN are less common.
CROSS JOIN, Self Joins & Multiple Joins
A CROSS JOIN produces a Cartesian product — every row from the first table is paired with every row from the second table. This is rarely used in practice but can be useful for generating combinations.
A self join is when a table is joined with itself. This is useful for hierarchical data, such as employees and managers, where both are stored in the same table.
Example
-- CROSS JOIN: Every customer paired with every product
SELECT c.name, o.product
FROM customers c
CROSS JOIN orders o;
-- Self Join: Employees and their managers
CREATE TABLE employees (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100),
manager_id INT
);
INSERT INTO employees (name, manager_id) VALUES
('CEO', NULL),
('VP Sales', 1),
('VP Engineering', 1),
('Developer', 3),
('Designer', 3);
-- Self join to get employee and manager names
SELECT
e.name AS employee,
m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;
-- Multiple joins: joining 3 or more tables
-- (example with products table)
SELECT
c.name AS customer,
o.order_date,
o.product,
o.amount
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
ORDER BY c.name, o.order_date; Notes
- Be careful with CROSS JOIN — if table A has 1000 rows and table B has 1000 rows, the result will have 1,000,000 rows!