What is the Difference Between Left, Right and Full Outer Join?

Joining tables is fun but you’ve got to know which of them outer joins to use, too. Hell, why is it called outer joins anyways?! Consider, simply, two tables, L and R (am I being original yet? :)). Now, let’s go ahead with the left join on R from L:

SELECT * 
    FROM l
        LEFT JOIN r ON r.Id = l.Id

This will return all rows from L and that have a matching Id in R. If there are no matching on Id rows in R, the R counterpart will contain all NULLs. Consider a situation when a company broadcasts some videos and a viewer can only view one broadcast at a time. Let's say we got four broadcasts and four users, three of which are currently watching a broadcast and one is not (UserId being 3). This will result in the following two tables:

+----------+---------------------+
| StreamId |                Name |
+----------+---------------------+
|        1 |           Channel 2 |
|        2 | Funny Videos Online |
|        3 |     Weather Channel |
|        4 | Funny Videos Online |
+----------+---------------------+

+---------+----------+
|  UserId | StreamId |
+---------+----------+
|       1 |        1 |
|       2 |        3 |
|       4 |        3 |
+---------+----------+

If we run a LEFT OUTER JOIN like this:

SELECT s.StreamId, u_s.UserId
	FROM Streams s
		LEFT OUTER JOIN Users_Streams u_s ON u_s.StreamId = s.StreamId

We would get the following result:

+----------+----------+
| StreamId     UserId |
+----------+----------+
|        1 |        1 |
|        2 |     NULL |
|        3 |        2 |
|        3 |        4 |
|        4 |     NULL |
+----------+----------+

As you can see, we have at least one record for each stream. If we have multiple users watching the same stream, we get multiple records for the same stream – one for each user. If there are no users watching a stream, we get a NULL on the right hand side – the columns from the table we are joining to.
Now, what if we do a RIGHT OUTER JOIN? As the name implies, we are now selecting all records from the table we are joining on (right of the table we are initially selecting from) and matching them up with the table we are selecting from. Let's intriduce the Users table which is going to have our missing record with UserId of 3:

+----------+---------------------+
| StreamId |                Name |
+----------+---------------------+
|        1 |           Channel 2 |
|        2 | Funny Videos Online |
|        3 |     Weather Channel |
|        4 |      Amazing Chases |
+----------+---------------------+

+---------+----------+
|  UserId | StreamId |
+---------+----------+
|       1 |        1 |
|       2 |        3 |
|       4 |        3 |
+---------+----------+

+---------+----------+
|  UserId |     Name |
+---------+----------+
|       1 |    bukky |
|       2 |  luefher |
|       3 |    mr. x |
|       4 |   doodle |
+---------+----------+

Well, now, Mr. X is not watching anything. If we simply reverse our query, either by flipping the join or swapping the tables' places, we are only going to get which user watches which stream:

-- Right join
SELECT s.StreamId, u_s.UserId
	FROM Streams s
		RIGHT OUTER JOIN Users_Streams u_s ON u_s.StreamId = s.StreamId

-- or Flip tables
SELECT s.StreamId, u_s.UserId
	FROM Users_Streams u_s
		LEFT OUTER JOIN #Streams s ON s.StreamId = u_s.StreamId

But we will be missing people who are not watching anything since we only select from the table that indicates who is watching now:

+----------+----------+
| StreamId |   UserId |
+----------+----------+
|        1 |        1 |
|        3 |        2 |
|        3 |        4 |
+----------+----------+

This is precisely why we need our new Users table. Now, let's see who is watching what or not watching anything. The following two queries, one being a LEFT OUTER JOIN and the other being a RIGHT OUTER JOIN:

-- Who's watching what or not watching (LEFT OUTER JOIN)
SELECT u.Name, u.UserId, s.StreamId, s.Name AS Stream
	FROM Users u
		LEFT JOIN Users_Streams u_s ON u_s.UserId = u.UserId
		LEFT JOIN Streams s ON s.StreamId = u_s.StreamId
		
-- What's being watched by whom and what's not being watched (RIGHT OUTER JOIN)
SELECT u.Name, u.UserId, s.StreamId, s.Name AS Stream
	FROM Streams s 
		RIGHT JOIN Users_Streams u_s ON u_s.StreamId = s.StreamId
		RIGHT JOIN Users u ON u.UserId = u_s.UserId

will return the same result:

+----------+----------+----------+-----------------+
|     Name |   UserId | StreamId |          Stream |
|    bukky |        1 |        1 |       Channel 2 |
|  luefher |        2 |        3 | Weather Channel |
|    mr. x |        3 |     NULL |            NULL |
|   doodle |        4 |        3 | Weather Channel |
+----------+----------+----------+-----------------+

I hope you understand the difference between the LEFT and RIGHT joins. So, why is it called OUTER? Well, as you probably already guessed, because the table you are joining on is "outer" to the one you initially getting data from.
There is also another type of OUTER JOIN, called FULL OUTER JOIN. If you got the JOINs above, you probably already have guessed what it is. The FULL OUTER JOIN will match records from both tables. The records that do not have a corresponding record in the second table will have NULLs in their columns and vise versa. Think of it as a LEFT and RIGHT OUTER JOIN combined. This is not a CROSS JOIN, as you might assume. Let's go ahead and execute a FULL OUTER JOIN:

SELECT u.Name, u.UserId, s.Name, s.StreamId
	FROM Users_Streams u_s
		FULL OUTER JOIN Users u ON u.UserId = u_s.UserId
		FULL OUTER JOIN Streams s ON s.StreamId = u_s.StreamId

And this is the result we get:

+----------+----------+---------------------+----------+
|     Name |   UserId |                Name | StreamId |
+----------+----------+---------------------+----------+
|    bukky |        1 |           Channel 2 |        1 |
|  luefher |        2 |     Weather Channel |        3 |
|   doodle |        4 |	    Weather Channel |        3 |
|    mr. x |        3 |                NULL |     NULL |
|     NULL |     NULL |	Funny Videos Online |        2 |
|     NULL |     NULL |	     Amazing Chases |        4 |
+----------+----------+---------------------+----------+

As you can see, every user and every stream is included in the results, along with a NULL in the user's columns if there are no viewers if you look at it from the "who's watching" and a NULL in the stream's columns if "no one is watching watching the stream".

I hope you enjoyed this article and it helped you out!
Who's the JOINest of'u all, post yar comments, dunt be shy 🙂

If you'd like to test it out yourself and play around with it, here is a starting point for you:

IF OBJECT_ID('#Streams') IS NOT NULL DROP TABLE #Streams
IF OBJECT_ID('#Users_Streams') IS NOT NULL DROP TABLE #Users_Streams
IF OBJECT_ID('#Streams') IS NOT NULL DROP TABLE #Users

CREATE TABLE #Streams
(
	StreamId INT,
	Name VARCHAR(50)
)

CREATE TABLE #Users_Streams
(
	StreamId INT,
	UserId INT
)

CREATE TABLE #Users
(
	UserId INT,
	Name VARCHAR(50)
)

INSERT INTO #Streams (StreamId, Name) VALUES (1, 'Channel 2')
INSERT INTO #Streams (StreamId, Name) VALUES (2, 'Funny Videos Online')
INSERT INTO #Streams (StreamId, Name) VALUES (3, 'Weather Channel')
INSERT INTO #Streams (StreamId, Name) VALUES (4, 'Amazing Chases')

INSERT INTO #Users_Streams (UserId, StreamId) VALUES (1, 1)
INSERT INTO #Users_Streams (UserId, StreamId) VALUES (2, 3)
INSERT INTO #Users_Streams (UserId, StreamId) VALUES (4, 3)

INSERT INTO #Users (UserId, Name) VALUES (1, 'bukky')
INSERT INTO #Users (UserId, Name) VALUES (2, 'luefher')
INSERT INTO #Users (UserId, Name) VALUES (3, 'mr. x')
INSERT INTO #Users (UserId, Name) VALUES (4, 'doodle')

-- Left join
SELECT s.StreamId, u_s.UserId
	FROM #Streams s
		LEFT OUTER JOIN #Users_Streams u_s ON u_s.StreamId = s.StreamId
		
-- Change to right join
SELECT s.StreamId, u_s.UserId
	FROM #Streams s
		RIGHT OUTER JOIN #Users_Streams u_s ON u_s.StreamId = s.StreamId
		
-- Flip tables
SELECT s.StreamId, u_s.UserId
	FROM #Users_Streams u_s
		LEFT OUTER JOIN #Streams s ON s.StreamId = u_s.StreamId
		
-- Who's watching what or not watching (LEFT OUTER JOIN)
SELECT u.Name, u.UserId, s.StreamId, s.Name AS Stream
	FROM #Users u
		LEFT JOIN #Users_Streams u_s ON u_s.UserId = u.UserId
		LEFT JOIN #Streams s ON s.StreamId = u_s.StreamId
		
-- What's being watched by whom and what's not being watched (RIGHT OUTER JOIN)
SELECT u.Name, u.UserId, s.StreamId, s.Name AS Stream
	FROM #Streams s 
		RIGHT JOIN #Users_Streams u_s ON u_s.StreamId = s.StreamId
		RIGHT JOIN #Users u ON u.UserId = u_s.UserId
		
-- Full join
SELECT u.Name, u.UserId, s.Name, s.StreamId
	FROM #Users_Streams u_s
		FULL OUTER JOIN #Users u ON u.UserId = u_s.UserId
		FULL OUTER JOIN #Streams s ON s.StreamId = u_s.StreamId

DROP TABLE #Streams
DROP TABLE #Users_Streams
DROP TABLE #Users
This entry was posted in MSSQL and tagged , , , , , , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *