TSQL OUTER JOINS (LEFT and RIGHT)

After learning the INNER JOIN when querying SQL Server, the LEFT OUTER JOIN and RIGHT OUTER JOIN are often what people attempt to learn next. The reason that I say attempt is that most people don’t get OUTER JOINs right the first time they learn it. This becomes very clear when interviewing developers and asking the following question:

Can you explain the difference between an INNER JOIN and an OUTER JOIN?

Most candidates get the INNER JOIN right, but very few developer or software engineer candidates are able to accurately answer the OUTER JOIN part of the question.

For many years I had a difficult time trying to explain the difference myself, I would try to say things like:

The INNER JOIN only returns those rows that are an exact match, and the OUTER JOIN (LEFT OR RIGHT) returns the rows that are an exact match, plus all remaining rows from one side or the other based on whether it is a LEFT or RIGHT OUTER JOIN.

This usually ended up with a blank stare, or a really confused look on the face of the person asking me the question. Then I discovered Venn Diagrams to describe JOIN types.

The best way that I have discovered to represent JOINing tables is to use Venn Diagrams that show what overlaps, what tables have in common, and what is missed.  This is represented on my Free SQL Server Join Types Poster, if you haven’t seen the poster, please check it out, it will help with your understanding of all SQL Server JOIN Types.

The INNER JOIN was previously discussed in another post, so I am not going to repeat it, just visit that post if you need an INNER JOIN refresher.

Keep in mind that LEFT OUTER JOIN is often abbreviated as LEFT JOIN and RIGHT OUTER JOIN is often abbreviated as RIGHT JOIN. For the purpose of this demo, and to be clear, I will always include the word OUTER which is optional in TSQL.

First we will establish a sample database with a couple tables to query against.

CREATE TABLE Table1
(
  id INTEGER IDENTITY NOT NULL,
  Name VARCHAR(100) NOT NULL,
  fk INTEGER NULL,
  fk_table3 INTEGER NULL,
  PRIMARY KEY (id)
) ;

CREATE TABLE Table2
(
  id INTEGER NOT NULL,
  FavoriteColor VARCHAR(100) NOT NULL
);
-- notice... Table2 is more real world, kinda slopy with no PRIMARY KEY

INSERT INTO Table2 (id, FavoriteColor) VALUES
  (1, 'red'),(2, 'green'),(3, 'blue'),
  (4, 'pink'),(5, 'purple'),(6, 'mauve'),
  (7, 'orange'),(8, 'yellow'),(1, 'indigo');

INSERT INTO Table1 (Name, fk, fk_table3) VALUES
  ('Steve', 1, NULL),('Aaron', 3, NULL),('Mary', 2, NULL),
  ('Fred', 1, NULL),('Anne', 5, NULL),('Beth', 8, 1),
  ('Johnny', NULL, 1), ('Karen', NULL, 2);

So let’s take a look at the Venn Diagrams for LEFT OUTER JOIN and RIGHT OUTER JOIN.


LeftOuterJoinVenn

The LEFT OUTER JOIN diagram shows that the query will return all rows from TABLE1, and those rows from TABLE 2 that match up on the JOIN condition. For the rows in TABLE1 that don’t match TABLE2, the TABLE2 side of the results will be filled with NULLs. Keep in mind that if a single row from TABLE 1 matches multiple rows from TABLE2, that will result in multiple rows in the output, not just a single row.

Here is the sample code.

SELECT *
  FROM Table1 t1
  LEFT OUTER JOIN Table2 t2
    ON t1.fk = t2.id;

Which produces the following output.

LeftOuterJoinResults

 

Now lets take a look at the RIGHT OUTER JOIN and see how it compares.

RightOuterJoinVenn

The Venn diagram shows that the RIGHT OUTER JOIN should include all the rows from Table2, and it will match what it can from TABLE1, and return NULLs on the left side of the results where there is no match.

Here is a sample of a RIGHT OUTER JOIN.

SELECT *
  FROM Table1 t1
 RIGHT OUTER JOIN Table2 t2
    ON t1.fk = t2.id;

Which gives us the following results:

RightOuterJoinResults

 

For More Information:

Simple Introduction to TSQL Cursors

Here is a 5 minute training with an overview of TSQL cursors, and how to get started with your first cursor on SQL Server.

See Also:

Today’s Live Training: Using Cursors and TSQL String Functions

Here is the outline:

  • Using Cursors
  • Using String Functions

Here is the link to the Google On Air Broadcast where you can watch for free.

https://plus.google.com/events/ci8jms67qttuteegimrf4sj70sk

After the presentation, I will update this page with a link to the YouTube video.

This session will be about 90 minutes, Aaron Buma will present about half and I will present the other half.

If you would like to download the slides and sample TSQL code, you can download them here: Cursors and String Functions.zip

See Also:

Executing a TSQL batch multiple times using GO

Using the GO commend in TSQL to run a batch of code multiple times is something that I commonly do in demo’s and training’s.  What amazes me is how many people after the training will tell me that they didn’t know about using the GO command to run a statement multiple times.

Here’s how it works. First lets create a table, then we will insert 100000 into the table using a while loop.

CREATE TABLE SampleTable(
	ID INT NOT NULL IDENTITY(1,1),
	SomeData uniqueidentifier NOT NULL DEFAULT(NEWID())
);

DECLARE @i INT; 
SET @i = 1;
WHILE @i <= 100000
BEGIN
	INSERT dbo.SampleTable DEFAULT VALUES ;
	SET @i = @i + 1;
END 

Sure that works, but it takes several lines of code to loop. There is an easier way to do it.

CREATE TABLE SampleTable(
	ID INT NOT NULL IDENTITY(1,1),
	SomeData uniqueidentifier NOT NULL DEFAULT(NEWID())
);

GO
	INSERT dbo.SampleTable DEFAULT VALUES; 
GO 100000

In this example, GO is the batch separator, SSMS breaks up the TSQL on GO statements, then runs each batch one at a time.
The first GO command is used to simply separate the CREATE statement from the INSERT statement. Then the second GO with followed by 100000 tells SSMS to run that batch 100000 times. If we didn’t have the first GO, the CREATE TABLE would have been attempted 100000 times, not just the INSERT STATEMENT.

GO IS NOT A TSQL KEYWORD.

GO is simply a SSMS batch separator word that can be modified to be something else in the SSMS settings if you want to change it. GO is not part of TSQL, and won’t work when you call TSQL from your own programs.

 

A quick tip that will hopefully save you some time.

Today’s Training: SQL Queries Section 1 Review

As part of our weekly free SQL Query Training, we have split the 70-461 curriculum into 4 sections to prepare everyone to take the certification test later in the year. Today’s session is to review the first 25% of the sessions, or the section 1 review as we are calling it.

The review topics we will be covering today are the following:

Here is the download file for this week with all the TSQL query examples included.

Section 1 Review.zip

Weekly SQL Training – MERGE Statement and Recursive Queries

The free SQL Server Queries weekly training is being presented by Emergency Reporting. The training is being presented by Aaron Buma and myself. This week we are covering two topics, first the MERGE statement will be covered by Aaron, then I will present on Recursive Queries. There MERGE statement is one of those that I was not familiar enough with when I took the certification exam 2 years ago. The recursive queries section was touched on a bit during the CTE presentation, here I will go into much more detail with additional samples.

You can download the slides and TSQL sample code here:
MERGE-Statement-and-Recursive-Queries.zip

Here are the slides, located at slideshare:

And here is the video on my YouTube Channel:

 

This is a live broadcast that was available on Google On Air Broadcasts.

See Also: