Why outer joins are needed in SQL

Introduction

Outer joins help us find all related data, even when some parts are missing. They show us everything from one table and matching data from another, including unmatched rows.

You want to list all customers and their orders, even if some customers have no orders.

You need to see all employees and their departments, including employees not assigned to any department yet.

You want to find all products and their sales, even if some products have never been sold.

You want to combine two lists where some items might not have a match in the other list.

Syntax

SQL

SELECT columns
FROM table1
LEFT OUTER JOIN table2 ON table1.key = table2.key;

LEFT OUTER JOIN returns all rows from the left table and matched rows from the right table.

If there is no match, columns from the right table will be NULL.

Examples

Shows all customers and their orders. Customers without orders still appear with NULL for order id.

SQL

SELECT customers.name, orders.id
FROM customers
LEFT OUTER JOIN orders ON customers.id = orders.customer_id;

Shows all departments and their employees. Departments without employees still appear with NULL for employee name.

SQL

SELECT employees.name, departments.name
FROM employees
RIGHT OUTER JOIN departments ON employees.department_id = departments.id;

Shows all products and sales. Includes products without sales and sales without matching products.

SQL

SELECT products.name, sales.amount
FROM products
FULL OUTER JOIN sales ON products.id = sales.product_id;

Sample Program

This query lists all customers and their orders. Customers without orders show NULL for order_id.

SQL

CREATE TABLE customers (id INT, name VARCHAR(20));
CREATE TABLE orders (id INT, customer_id INT);

INSERT INTO customers VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Carol');
INSERT INTO orders VALUES (101, 1), (102, 1), (103, 3);

SELECT customers.name, orders.id AS order_id
FROM customers
LEFT OUTER JOIN orders ON customers.id = orders.customer_id
ORDER BY customers.id, orders.id;

Important Notes

Outer joins help avoid losing data when one table has missing matches.

LEFT, RIGHT, and FULL outer joins differ by which table's rows are all kept.

Summary

Outer joins show all rows from one or both tables, even if no match exists.

They are useful to find missing or unmatched data.

LEFT OUTER JOIN is the most common, showing all rows from the left table.