What is Join and Their Types in SQL

When working with databases, you often need to get information from more than one table. This is where joins come in! Joins allow you to combine rows from two or more tables based on a common column between them.

In this blog, I’ll explain what joins are and go over the main types with simple examples.

Below is a tree diagram that illustrates the different types of joins:

1. INNER JOIN

An INNER JOIN gives you only the rows that have matching data in both tables. If there is no match, the row won’t appear in the result.

Example: 

You want to see a list of customers who have placed orders.

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

This will only show customers who have made an order.

2. LEFT JOIN (LEFT OUTER JOIN)

A LEFT JOIN gives you all the rows from the left table (the first one you mention), and the matching rows from the right table (the second one). If there’s no match, you get NULL in the right table’s columns.

Example: 

You want to see all customers, even if they haven’t placed an order.

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

This will show all customers, and if they haven’t placed an order, the order information will be NULL.

3. RIGHT JOIN (RIGHT OUTER JOIN)

A RIGHT JOIN is the opposite of a LEFT JOIN. It returns all the rows from the right table, along with any matching rows from the left table. If there’s no match, the left table’s columns will be NULL.

Example: 

You want to see all orders, even if some customers are no longer in the system.

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

This will show all orders, even if the customer who placed them doesn’t exist in the customers' table anymore.

4. FULL JOIN (FULL OUTER JOIN)

A FULL JOIN combines the results of both LEFT and RIGHT JOINS. It returns all rows from both tables. Where there are no matches, you will see NULL in the columns of the table without a match.

Example: 

You want to see all customers and all orders, regardless of whether there is a match.

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

This will show all customers and all orders, with NULL values where there are no matches.

5. CROSS JOIN

A CROSS JOIN returns the Cartesian product of the two tables. This means it will return all possible combinations of rows from both tables.

Example: 

You want to see every customer paired with every order.

SELECT customers.name, orders.order_id
FROM customers
CROSS JOIN orders;

This will show every customer listed with every order, which can lead to a large number of results!

6. SELF JOIN

A SELF JOIN is a special case where a table is joined with itself. This is useful when you want to compare rows within the same table.

Example: 

You want to find employees and their managers from the same employee table.

SELECT e1.name AS Employee, e2.name AS Manager
FROM employees e1
INNER JOIN employees e2 ON e1.manager_id = e2.employee_id;

This will show each employee alongside their respective manager.

Conclusion

Understanding joins is crucial for working with relational databases. They help you retrieve data from multiple tables efficiently and effectively. The main types of joins—INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, CROSS JOIN, and SELF JOIN—each serve different purposes depending on the data you need.

Experiment with these joins to see how they can help you get the data you want!