The JOIN statement is one of the most important and widely used concepts in SQL (Structured Query Language). Essentially, it is used to combine rows from two or more tables into one database, based on a relationship between them. It is an essential tool for working with relational databases, where information is spread across multiple tables. Without the use of JOINs, extracting meaningful data spanning multiple tables would be much more complicated and less efficient.
Types of JOIN
There are several types of JOIN in SQL, each designed to handle different situations and query requirements. The main types of JOIN are described below:
Inner join
The Inner join This is the most common type of JOIN. It is used to return only those rows that have matching values in both tables involved. In other words, if the rows in one table do not have a match in the other table, they will not be included in the final result. This type of JOIN is very useful when you want to extract only the data that is relevant to both tables.
Left join
The left join (or left outer join) returns all rows from the left-hand table and the matching rows from the right-hand table. If there is no match, the rows from the right-hand table will appear as NULL in the result. This type of JOIN is useful when you wish to retrieve all rows from the main table, regardless of whether or not they have a match in the secondary table.
Right join
The right join (or right outer join) is similar to a left join, but with the tables reversed. It returns all rows from the table on the right and the matching rows from the table on the left. If there is no match, the rows from the table on the left will appear as NULL in the result. It is used when the right-hand table needs to be prioritised in the query.
Examples of use
To gain a better understanding of how these JOIN sequences work, let’s look at some practical examples.
Example of an inner join
Let’s suppose we have two tables: Customers y Orders. The table Customers contains information about a shop’s customers, and the table Orders contains the orders placed by those customers.
SELECT Customers.Name, Orders.OrderDate
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;
In this example, the INNER JOIN returns a list of customer names along with the dates on which they placed orders, but only for those customers who have placed at least one order.
Example of a left join
Using the same tables Customers y Orders, we now want to obtain a list of all customers, including those who have not placed any orders.
SELECT Customers.Name, Orders.OrderDate
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;
This LEFT JOIN will return a list of all customers, and if a customer has not placed an order, the column Order Date will appear as NULL.
Example of a right join
Finally, let’s see how a Right Join using the same tables.
SELECT Customers.Name, Orders.OrderDate
FROM Customers
RIGHT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;
Here, the RIGHT JOIN will return all records, including those that may not have a corresponding customer in the table Customers. This could happen if, for example, a customer were deleted from the table Customers but their orders are still in the database.
Here are some related training courses that might interest you:
Español 
English (UK) 
Français