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 (https://blog.sqlauthority.com)
20 Comments. Leave new
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?
Theta joins are made up by the interviewer. This is used by many interviewers to make sure you know fact from fiction. You can’t believe everything you read on the net, and this applies to SQL big time!
[removed word by admin]
Theta join is a join in relational algebra. It’s a join where the join operator can be other than =
I think LEFT JOIN is ANSI standard and LEFT OUTER JOIN supported in MS SQL
No. The word OUTER is optional
Yes madhivanan you are correct Outer is optional in left outer join insted of that we can use left join only…
You surely know how to do SEO ;-)
Hi,
Please explain how to handle null values on a column that is being used to join 2 tables?
Thanks,
Amit
Refer this post
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
Yes you can swap the tables and get the same result
THank You
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 ;
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
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.
probably because the execution plan was already in place when you ran it the second time.
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
I believe this is a difference in how the DISTINCT worked in the 2 different environments
if left join and left outer join both are the same. then why using the 2 key words?