SQL Join Types Explained: Inner, Outer, and More in 2024

let's explore SQL joins with examples.

What are Joins in SQL?

In SQL, a join is used to combine rows from two or more tables based on related columns between them. It allows you to retrieve data from multiple tables in a single query, providing a powerful mechanism for querying and analyzing relational data.

Types of Joins

There are several types of joins in SQL, each serving a different purpose:

1. Inner Join

An inner join returns only the rows that have matching values in both tables based on the specified join condition.

SELECT orders.order_id, customers.customer_name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id;

2. Left Join (or Left Outer Join)

A left join returns all rows from the left table (the table mentioned before the LEFT JOIN keyword), along with matching rows from the right table. If there are no matching rows in the right table, NULL values are included in the result set.

SELECT orders.order_id, customers.customer_name
FROM orders
LEFT JOIN customers ON orders.customer_id = customers.customer_id;

3. Right Join (or Right Outer Join)

A right join returns all rows from the right table (the table mentioned after the RIGHT JOIN keyword), along with matching rows from the left table. If there are no matching rows in the left table, NULL values are included in the result set.

SELECT orders.order_id, customers.customer_name
FROM orders
RIGHT JOIN customers ON orders.customer_id = customers.customer_id;

4. Full Join (or Full Outer Join)

A full join returns all rows from both tables, matching rows from both tables where available. If there is no match, NULL values are included in the result set.

SELECT orders.order_id, customers.customer_name
FROM orders
FULL JOIN customers ON orders.customer_id = customers.customer_id;

5. Cross Join

A cross join returns the Cartesian product of the two tables, meaning it returns all possible combinations of rows from both tables. It doesn't require a join condition.

SELECT *
FROM orders
CROSS JOIN customers;

Example

Let's consider two tables: orders and customers.

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE
);

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100)
);

Suppose we have the following data in these tables:

orders:

customers:

Inner Join Example

SELECT orders.order_id, customers.customer_name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id;

This query will return:

This is because there are no matching customer IDs for order 3 in the customers table.

Left Join Example

SELECT orders.order_id, customers.customer_name
FROM orders
LEFT JOIN customers ON orders.customer_id = customers.customer_id;

This query will return:

Since there is no matching customer ID for order 3, the customer name is NULL in the result set.

Conclusion

Joins in SQL are powerful tools for combining data from multiple tables based on related columns. Understanding the different types of joins and their usage scenarios is essential for writing efficient and effective SQL queries.