Outer Joins are used to fetch data from SQL tables based on some conditions on columns of the multiple tables. There are mainly two types of joins in SQL servers called Inner and Outer Joins.
Outer Joins are further classified into three different categories depending on their logical differences. In this article, we will learn about Outer Joins in SQL server and their types.
What is Outer Join in SQL Server?
Outer joins are the joins that are used to return the matched and unmatched rows from one or both of the tables. There are three types of outer joins:
Why Do We Use Outer Join in SQL Server?
While working with an SQL server, we may need to fetch records from a table based on some condition on the columns of another table. In this case, the outer join can be used. Before moving toward the practical example let’s consider a real-life use case where the idea of Outer Join can be implemented.
Example: If the owner of an online store wants to see all the customers and the orders for customers that have placed the orders. In this case, the outer join can help to fetch the records. An outer join is implemented on the customer and orders table that returns this required output.
How to Use Outer Join in SQL Server?
In this section, we will learn Outer Join in SQL server using query examples. Consider the database tables attached for the given queries.
Full Outer Join
ㅤ SELECT students.name, books.title FROM students FULL OUTER JOIN books ON students.student_id=books.student_id; ㅤ
In the above example, we select the names from the student’s table and book titles from the books table based on the student_id column present in both of the tales.
Right Outer Join
ㅤ SELECT Orders.OrderID, Employees.LastName, Employees.FirstName FROM Orders RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID ORDER BY Orders.OrderID; ㅤ
In the above example, we use Right Outer Join to get all employees, and any orders they have placed.
Left Outer Join
ㅤ SELECT Customers.CustomerName, Orders.OrderID FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID ORDER BY Customers.CustomerName; ㅤ
In the above example, we used Left Outer Join to get all customers, and any orders they might have.
Keep in mind that the Full Outer Join and Full Join are the same. In this article, we have gone through the Outer Join and its types. Joins are sometimes complex to understand, so you can always consider a real-life example to make it easy and practice the above examples.