SQL joins are used to combine data from two or more tables in a relational database. They allow you to retrieve data from multiple tables as if they were a single table.
This can be useful for querying data that is spread across multiple tables, or for combining data from multiple tables for reporting or analysis purposes.
In this guide, we will discuss the different types of SQL joins and provide examples of how to use them.
Inner JOIN
An inner JOIN is the most commonly used type of join.
It returns only the rows that have matching values in both tables.
The basic syntax of an inner JOIN is as follows:
SELECT column_name(s)
FROM table1
JOIN table2
ON table1.column_name = table2.column_name;For example, let’s say we have two tables: “orders” and “customers”. The “orders” table contains information about customer orders, and the “customers” table contains information about the customers themselves.
We want to retrieve the names and order details of customers who have placed orders.
We would use the following query:
SELECT orders.order_id, customers.name, orders.order_date
FROM orders
JOIN customers
ON orders.customer_id = customers.customer_id;This query would return a table with the order ID, customer name, and order date for all orders placed by customers in the “customers” table.
LEFT JOIN
A LEFT JOIN returns all rows from the left table (table1), and the matching rows from the right table (table2).
If there is no match, the result will contain NULL values.
The basic syntax of a LEFT JOIN is as follows:
SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;For example, let’s say we have the same “orders” and “customers” tables as before.
But this time we want to see all customers, even if they have not placed an order.
We would use the following query:
SELECT customers.name, orders.order_id, orders.order_date
FROM customers
LEFT JOIN orders
ON customers.customer_id = orders.customer_id;This query would return a table with the customer name, order ID, and order date for all customers in the “customers” table, including those who have not placed orders.
Any customers who have not placed an order would have NULL values in the order ID and order date columns.
RIGHT JOIN
A RIGHT JOIN returns all rows from the right table (table2), and the matching rows from the left table (table1).
If there is no match, the result will contain NULL values.
The basic syntax of a RIGHT JOIN is as follows:
SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;For example, let’s say we have the same “orders” and “customers” tables as before.
But this time we want to see all orders, even if they were not placed by a customer in the “customers” table.
We would use the following query:
SELECT orders.order_id, customers.name, orders.order_date
FROM orders
RIGHT JOIN customers
ON orders.customer_id = customers.customer_id;This query would return a table with the order ID, customer name, and order date for all orders in the “orders” table, including those that were not placed by customers in the “customers” table.
Any orders that were not placed by a customer in the “customers” table would have NULL values in the customer name column.
FULL OUTER JOIN
A FULL OUTER JOIN returns all rows from both tables, and matches the rows from both tables on the specified columns.
If there is no match, the result will contain NULL values.
The basic syntax of a FULL OUTER JOIN is as follows:
SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name;For example, let’s say we have the same “orders” and “customers” tables as before.
But this time we want to see all customers and all orders, including those that have no matching values in the other table.
We would use the following query:
SELECT customers.name, orders.order_id, orders.order_date
FROM customers
FULL OUTER JOIN orders
ON customers.customer_id = orders.customer_id;This query would return a table with the customer name, order ID, and order date for all customers and all orders in both tables.
Any customers or orders that have no matching values in the other table would have NULL values in the non-matching columns.
CROSS JOIN
A CROSS JOIN, also known as a cartesian product, returns the combination of every row from the first table with every row from the second table.
It is not very useful in most cases, but it can be useful for some specific scenarios.
The basic syntax of a CROSS JOIN is as follows:
SELECT column_name(s)
FROM table1
CROSS JOIN table2For example, let’s say we have two tables: “colors” and “sizes”. The “colors” table has three rows: red, blue, and green. The “sizes” table has four rows: S, M, L, XL.
We want to see all the possible combinations of colors and sizes.
We would use the following query:
SELECT colors.name, sizes.name
FROM colors
CROSS JOIN sizes;This query would return a table with all possible combinations of colors and sizes.
Conclusion
In this guide, we have discussed the different types of SQL joins and provided examples of how to use them.
Remember that the type of join you use will depend on the specific data and the results you want to achieve.
Inner joins are the most commonly used type of join, but left, right, full outer and cross joins can be useful in specific scenarios.
Always test your queries and verify the results to ensure that you are getting the expected results.
