Skip to main content

Using the JOIN Clause

Introduction to JOIN Clause in SQL

In SQL, the JOIN clause is used to combine rows from two or more tables, based on a related column between them. It's a powerful tool to consolidate data and create more complex queries. In this tutorial, we will be focusing on how to use the JOIN clause effectively in SQL.

Types of JOINs in SQL​

There are four types of JOINs available in SQL:

  1. INNER JOIN: This returns records that have matching values in both tables.
  2. LEFT (OUTER) JOIN: This returns all records from the left table, and the matched records from the right table.
  3. RIGHT (OUTER) JOIN: This returns all records from the right table, and the matched records from the left table.
  4. FULL (OUTER) JOIN: This returns all records when there is a match in either left or right table.

INNER JOIN​

The INNER JOIN keyword selects records that have matching values in both tables.

Here is an example:

SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns. If there are records in the "Orders" table that do not have matches in "Customers", these orders will not be shown!

LEFT JOIN (LEFT OUTER JOIN)​

The LEFT JOIN keyword returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side, if there is no match.

Here is an example:

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

The LEFT JOIN keyword returns all records from the left table (Customers), even if there are no matches in the right table (Orders).

RIGHT JOIN (RIGHT OUTER JOIN)​

The RIGHT JOIN keyword returns all records from the right table (table2), and the matched records from the left table (table1). The result is NULL from the left side, when there is no match.

Here is an example:

SELECT Orders.OrderID, Employees.LastName, Employees.FirstName
FROM Orders
RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
ORDER BY Orders.OrderID;

The RIGHT JOIN keyword returns all records from the right table (Employees), even if there are no matches in the left table (Orders).

FULL JOIN (FULL OUTER JOIN)​

The FULL JOIN keyword return rows when there is a match in one of the tables. Therefore, if there is a row in Employees that does not match any row in Orders, or if there is a row in Orders that does not match any row in Employees, that row will be included in the result-set.

Here is an example:

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerName;

The FULL OUTER JOIN keyword returns all records when there is a match in the left (Customers) or the right (Orders) table records.

Conclusion

JOIN is a powerful feature in SQL, as it allows you to combine data from multiple tables into a single result. This allows for more complex queries and greater flexibility when working with your data. Practice these different types of JOINs and experiment with your own data sets to see how they work firsthand. Happy querying!