SQL SERVER- Differences Between Left Join and Left Outer Join

There are a few questions that I had decided not to discuss on this blog because I think they are very simple and many of us know it. Many times, I even receive not-so positive notes from several readers when I am writing something simple. However, assuming that we know all and beginners should know everything is not the right attitude.

Since day 1, I have been keeping a small journal regarding questions that I receive in this blog. There are around 200+ questions I receive every day through emails, comments and occasional phone calls. Yesterday, I received a comment with the following question:

What are the differences between Left Join and Left Outer Join? Click here to read original comment.

This question has triggered the threshold of receiving the same question repeatedly. Here is the answer:

There is absolutely no difference between LEFT JOIN and LEFT OUTER JOIN. The same is true for RIGHT JOIN and RIGHT OUTER JOIN. When you use LEFT JOIN keyword in SQL Server, it means LEFT OUTER JOIN only.

I have already written in-depth visual diagram discussing the JOINs. I encourage all of you to read the article for further understanding of the JOINs:

Read Introduction to JOINs – Basic of JOINs

Reference: Pinal Dave (http://blog.SQLAuthority.com)

19 thoughts on “SQL SERVER- Differences Between Left Join and Left Outer Join

  1. Hi, Pinal

    One of my interview i got qns what is theta join.i read your
    previous blog.i didn’t find about theta join.
    could you please tell us something about theta join?

    Like

  2. Hi Pinal,

    Why do we have Right Outer Join when we have Left Outer Join. We can swap the tables and get the desired result. Please can you throw some light on this.

    Regards,
    Uday

    Like

  3. HI,
    My result set differs from 1 to 2 ..Could you please explain me the reason ?
    1)select CASE
    WHEN
    (
    b.col2 = ‘Y’
    )
    then ‘Y’ else ‘N’
    from TABLE_A a left join TABLE_B b on a.col1=b.col1 ;

    2) select CASE
    WHEN
    (
    b.col2 = ‘Y’
    )
    then ‘Y’ else ‘N’
    from TABLE_A a left join (sel col1,col2 from TABLE_B) b on a.col1=b.col1 ;

    Like

  4. Hi Pinal,
    I just came across the following scenario today and wanted to get it clarified from you. I know there is no difference between left and left outer join but however the result set was different in my scenario. Bascially I joined two tables using left outer join (note my select query had isnull in it) result I got was no rows but when I removed the outer from it (used only left join) I got 1 rows with column values as 0. Why is this difference

    Like

  5. Hi Pinal,

    I used Left outer join in my SQL query and the execution time was 6 min and for the same query when I removed the “Outer”, the processing time was only 3 min.

    Like

  6. Following query respond diffrent on 2005 and 2008 can u explain why?
    Thanks in advance.

    create table table1(Id int, Name varchar(10))

    create table table2(Id int)

    insert into table1 values(101, ‘Test’)
    insert into table2 values(101)
    insert into table2 values(101)
    insert into table2 values(101)

    SELECT * FROM (SELECT DISTINCT [Table1].* , ROW_NUMBER() OVER (ORDER BY [Table1].Name) AS RowNumber
    FROM [Table1]
    LEFT JOIN [Table2] ON [Table1].Id = [Table2].Id) AS TempTable

    SELECT * FROM (SELECT DISTINCT [Table1].* , ROW_NUMBER() OVER (ORDER BY [Table1].Name) AS RowNumber
    FROM [Table1]
    LEFT JOIN [Table2] ON [Table1].Id = [Table2].Id) AS TempTable
    WHERE RowNumber BETWEEN 1 AND 10

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s