For an instructor lead, in-depth look at learning SQL click below.
One of the key concepts in SQL, a language built for managing data held in Relational Database Management System (RDBMS), is the OUTER join. SQL OUTER join is not just one type of operation, it actually encompasses three different types: LEFT, RIGHT, and FULL joines. In this blog, we will walk you through understanding these outer joins in SQL and provide examples of each to help you grasp the concept better.
Left Outer Join
The LEFT OUTER JOIN returns all records from the left table (table1), and the matched records from the right table (table2). It also returns NULL values for every column of the right table when there is no match.
|
1 2 3 4 5 6 7 |
SELECT Customers.CustomerName, Orders.OrderID FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID ORDER BY Customers.CustomerName; |
In the above example, the result set will include all the customers from the “Customers” table and any matching order IDs from the “Orders” table. If there is no matching order for a customer, the order ID will display as NULL.
Right Outer Join
The RIGHT OUTER JOIN works exactly in the opposite way of the LEFT OUTER JOIN. It returns all records from the right table and the matched records from the left table. If there is no match, the result is NULL on the left side.
|
1 2 3 4 5 6 7 |
SELECT Orders.OrderID, Customers.CustomerName FROM Orders RIGHT JOIN Customers ON Orders.CustomerID = Customers.CustomerID ORDER BY Orders.OrderID; |
This example will return all the orders from the “Orders” table and any matching customer names from the “Customers” table. If there is no matching customer for an order, the customer name will display as NULL.
Full Outer Join
The FULL OUTER JOIN keyword returns all records when there is a match in either left (table1) or right (table2) table records. It essentially combines the functions of a LEFT OUTER JOIN and a RIGHT OUTER JOIN and returns all (matching and non-matching) rows from the tables on both sides of the join clause.
|
1 2 3 4 5 6 7 |
SELECT Customers.CustomerName, Orders.OrderID FROM Customers FULL OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID ORDER BY Customers.CustomerName; |
This last example provides a mixture of the previous two. It will return all customers and all orders, filling in NULLs for any customers without orders and orders without customers.
Understanding these joins and how they interact with each other is fundamental to managing and working with SQL databases. If you’d like to learn more about SQL, stay tuned to our blog!