SQL Outer Join Overview | What is Outer Join SQL
In this article, you will learn SQL OUTER JOIN and full overview with examples such as Outer Join SQL, SQL FULL OUTER Join, Left Outer Join, Right Outer Join as well as we will cover the union between SQL Left Join and Right Outer joins.
if you want to learn about SQL INNER JOIN, SQL CROSS APPLY, and NATURAL JOIN SQL, check out this article.
Overview:
therefore. it must essential for you to understand the process to get the data from the multiple tables because beginner learners might not have the ideas of Join in the SQL Server. Basically, we split the large tables into smaller tables. Therefore, in a select statement, we can retrieve the data from these tables using joins.
These tables should have some common fields related to each other, you might find data split across multiple databases and sometimes you see that it is a very complex structure as well. therefore, we can join the tables and get the required fields from these tables in the result set. furthermore, with joins, we can join the data together from the multiple tables, databases into the user-friendly way and represent this data in the application.
Therefore, when performing an INNER JOIN, rows from either table that are unmatched in the other table are not returned. furthermore, while in an OUTER JOIN, unmatched rows in one or both tables can be returned. There are the types following:
I) SQL FULL OUTER JOIN: it returns unmatched rows from both the tables.
II) SQL LEFT OUTER JOIN: it returns only the unmatched rows from the left table.
III) SQL RIGHT OUTER JOIN: it returns only the unmatched rows from the right table.
Types of SQL Join
There are many SQL Join types in SQL Server. In the following image, you can see SQL Joins all categories
Demo Sample Table:
First, we will create a sample demo database table to see all types with examples in detail.
We can understand efficiently using the examples. then Let’s create a sample table and insert the data into it.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE TABLE [dbo].[Students]( [StudentID] [int] IDENTITY(1,1) PRIMARY KEY CLUSTERED, [StudentName] [varchar](60) NULL, [fatherName] [varchar](40) NULL, [Course] [varchar](40) NULL ) CREATE TABLE College (StudentID INT PRIMARY KEY CLUSTERED , CollegeID INT, CollegeName VARCHAR(40) ); |
Table name: Students
The given below table name is Students, and you can insert student’s data in this table using the SQL insert query in your database table. the given data represented the data store in the Students table.
| StudentID | StudentName | FatherName | Course |
|---|---|---|---|
| 1 | Liam Mason | Noah Elijah | Engineering |
| 2 | William James | Logan Benjamin | Pharmacy |
| 3 | Oliver Lucas | Jacob Michael | Management |
| 4 | Alexander Daniel | Ethan Matthew | Medical |
| 5 | Aiden Henry | Joseph Jackson | Engineering |
| 6 | Samuel Carter | David Sebastian | B.Pharma |
| 7 | Jayden | John | Pharmacy |
| 8 | Owen Dylan | Luke Gabriel | Management |
| 9 | Anthony Isaac | Grayson Jack | Engineering |
| 10 | Julian Levi | Joshua Andrew | B.Pharma |
| 11 | Lincoln Mateo | Ryan Nathan | Medical |
| 12 | Aaron | Isaiah | Graduation |
| 13 | Thomas Charles | Caleb Josiah | Pharmacy |
| 14 | Christian Hunter | Jonathan Connor | Graduation |
| 15 | Landon Nolan | Nicholas Ezra | Medical |
Table name: College
The given below table name is College, and you can insert college data in this table using the SQL insert query in your database table. the given below data represented the data store in the college table.
| StudentID | CollegeID | CollegeName |
|---|---|---|
| 1 | 11 | Stanford University |
| 2 | 12 | Yale University |
| 3 | 13 | Columbia University |
| 4 | 11 | Stanford University |
| 5 | 13 | Columbia University |
| 6 | 17 | University of the West |
| 7 | 18 | Saint Peter’s University |
| 10 | 19 | Duke University |
| 12 | 18 | Saint Peter’s University |
| 15 | 12 | Yale University |
1. SQL OUTER JOIN
There are two table Students and College, which are connected to each other, let’s us understand the example if you see in the table, we have a common field (StudentID) in both the tables, therefore, we can join both tables with this column. furthermore, the following query, we defined the FULL OUTER JOIN between students and the College table on the StudentID the column of both the table.
SQL Full Outer Join Example
|
1 2 3 |
SELECT * FROM Students FULL OUTER JOIN College ON Students.StudentID = College.StudentID; |
Matching Rows between both the tables
Unmatched Rows from both the tables (NULL values)
Therefore, Let’s execute the above query to return output. then, We get the following output.
Furthermore, in the output result, We can see that a few records with NULL values as well. Let’s understand this in a better way with the help of using a Venn diagram.
In the following above screenshot, you can see the following information such as
StudentID 1, 2,3,4,5,6,7,10,12,15 exists in both Students and College table.
In the Full Outer Join query output, therefore, we get all these rows with data from both the tables therefore, StudentID 8, 9, 11, 13, 14 exists in the Students table but not in the College table.
furthermore, It does not include any matching rows in the College table. therefore, we get NULL values for those records
Now, for demo purposes let’s insert one more record in College tables. furthermore, in this query, we insert EmpID 16 that does not exist in the Students table.
|
1 2 3 4 5 6 7 8 9 10 11 |
INSERT INTO [dbo].[College] ([StudentID], [CollegeID], [CollegeName] ) VALUES (16, 17, 'University of the West' ); GO |
Furthermore, to Rerun the Full Outer Join query. In the following image, you get one additional row with NULL values. therefore, We do not have any matching row for EmpID 16 in the student table. Due to this, we get NULL values for it in the output.
Therefore, as a summary of the SQL FULL OUTER JOIN, we can represent using the following Venn diagram. We get what is represented in the highlighted area in the output of Full Outer Join.
FULL OUTER JOIN with WHERE clause
Therefore, we can add a WHERE clause with a FULL OUTER JOIN [+] to get rows with no matching data between both Join tables.
In the given below the following query, we add a WHERE clause to show only records having NULL values.
|
1 2 3 4 5 |
SELECT * FROM Students FULL OUTER JOIN College ON Students.StudentID = College.StudentID WHERE Students.StudentID IS NULL OR College.StudentID IS NULL; |
Therefore, Execute this command and view the output. you can see that it only returns rows that do not match either in Student or College table.
2. SQL LEFT OUTER JOIN
In a SQL Left Outer Join, we get the following rows in our output.
It will give us the output of the matching row between both the tables
If no records match from the left table, then it also shows those records with NULL values
Therefore, let’s execute the following code to return SQL LEFT OUTER JOIN output
|
1 2 3 |
SELECT * FROM Students LEFT OUTER JOIN College ON Students.StudentID = College.StudentID |
In the given below output screenshot image, you can see that we have NULL values for StudentID 8,9,11,13 and 14.
therefore, These StudentID do not exist on the right side College table.
Furthermore, in the output result, We can see that a few records with NULL values as well. Let’s understand this in a better way with the help of using a Venn diagram.
Furthermore, here, we need to note the table placement position in the Join statement. therefore, Currently, we have a Students table on the left side and College table on the Right side.
Let’s rewrite the query and swap the position of tables in the query. In this query, we have the College table in the left position, so the Left Outer Join should check the values for this table and return a NULL value in case of a mismatch.
In the given below the following screenshot, we can see that only one NULL value for StudentID 16. It is because StudentID 16 is not available in the Students table.
|
1 2 3 |
SELECT * FROM College LEFT OUTER JOIN Students ON College.StudentID = Students.StudentID |
Therefore, as a summary, we can represent the SQL Left Outer Join using the given below following Venn diagram. furthermore, We get the highlighted area in the output of SQL Left Outer Join.
3. SQL Right OUTER JOIN
In the SQL Right Outer Join, we get the following rows in our output.
It will give the output of the matching row between both the tables
If no records match from the right table, then it also shows those records with NULL values
let’s execute the given below following query to get the output.
|
1 2 3 |
SELECT * FROM Students RIGHT OUTER JOIN College ON Students.StudentID = College.StudentID |
In the above output image, you can see that we get all matching rows along with one row with NULL values. furthermore, the Null value row has StudentID 16 because it does not exist in the Students table. therefore, you will notice the position of the College table is in the right position in Join. then due to this, we do not get values from the students table (left position) therefore, which does not match with the College table (Right side).
Therefore, the table position is important in the JOIN statement. furthermore, If we change the table positions, then we get different output. such as In the following query, we have a College table (Left) and Students table (Right).
|
1 2 3 |
SELECT * FROM College RIGHT OUTER JOIN Students ON College.StudentID = Students.StudentID |
Therefore, Aas a summary, we will represent the SQL Right Outer Join using the given below Venn diagram. furthermore, We get highlighted areas in the output of SQL Right Outer Join.
The Union between SQL Right Outer Join and SQL Left Outer Join
Therefore, as you have seen, in the above discussion about SQL Left Outer Join, and the SQL Right Outer Join with different examples. furthermore, we can do a Union of the result of both SQL Left Outer Join and SQL Right Outer Join. which gives the output of Full Outer Join.
let’s execute the given below the following query as an alternative to Full Outer Join.
|
1 2 3 4 5 6 7 |
SELECT * FROM Students LEFT OUTER JOIN College ON Students.StudentID = College.StudentID UNION ALL SELECT * FROM Students RIGHT OUTER JOIN College ON Students.StudentID = College.StudentID |
Therefore, in the following output, we will get all matching records, and unmatch records from the left table and unmatch records from the right table. furthermore, It is similar to an output of the SQL Full Outer Join.
Conclusion:
In this article, we explored the SQL Outer Join and its types along with examples. I hope you will enjoy this article and will be helpful for you. Feel free to provide feedback and if have any question then contact me, I will try to resolve the douth. Thank you!
About Author
