Prev: Audit login
Next: SQL statement - 'chop' phrase
From: Rakesh on 17 Dec 2009 01:14
Can you tell me the Diff Between Left And Right join i know this diff but not this one some more core diff we can used Left join and changeing order of the table we can achived the right join result. why we requred to join ........?
LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table
RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table



Plamen Ratchev wrote:

Re: left join or inner join?
31-Mar-09

In addition, Itzik Ben-Gan's book "Microsoft SQL Server 2008: T-SQL
Fundamentals" is a great tutorial. There is a sample chapter on joins:
http://www.sql.co.il/books/tsqlfund2008/9780735626010_MSSQLServer08T-SQLFundamentals_ch03.pdf


--
Plamen Ratchev
http://www.SQLStudio.com

Previous Posts In This Thread:

On Tuesday, March 31, 2009 10:07 PM
iNewbi wrote:

left join or inner join?
hi, i just wanted to know, what has the better performance, a left join (or
right) or an inner join?

also, do you have some website that explains how sql server makes a join
when a query is processed?

thanks!!!

On Tuesday, March 31, 2009 10:23 PM
Plamen Ratchev wrote:

Inner join returns only rows that satisfy the join conditions.
Inner join returns only rows that satisfy the join conditions. Left
outer join has one more logical step to add the outer rows for the
preserved table. In that regards results may differ and not sure it
makes sense to compare performance. Based on the logic of your query you
have to use the correct join type to retrieve the needed results.

Two resources you may find useful:

Diagram for the logical query processing:
http://www.sql.co.il/books/insidetsql2008/Logical%20Query%20Processing%20Poster.pdf

More details on left outer joins:
http://www.ibm.com/developerworks/data/library/techarticle/purcell/0112purcell.html


--
Plamen Ratchev
http://www.SQLStudio.com

On Tuesday, March 31, 2009 10:33 PM
Dan Guzman wrote:

An inner join typically performs better because more work is needed in outer
An inner join typically performs better because more work is needed in outer
joins to preserve unlatching outer rows.


See Itzik' poster and books for a descriptions of the logical order of query
processing:
http://www.sql.co.il/books/insidetsql2008/Logical%20Query%20Processing%20Poster.pdf

Note that the actual order of execution is often different than the logical
order of evaluation. SQL Server strives to perform the task as efficiently
as possible while providing the correct logical results.

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"iNewbie" <iNewbie(a)discussions.microsoft.com> wrote in message
news:EB3EEF2B-5590-428B-92AE-8D77D1DB33B1(a)microsoft.com...

On Tuesday, March 31, 2009 11:07 PM
Plamen Ratchev wrote:

Re: left join or inner join?
In addition, Itzik Ben-Gan's book "Microsoft SQL Server 2008: T-SQL
Fundamentals" is a great tutorial. There is a sample chapter on joins:
http://www.sql.co.il/books/tsqlfund2008/9780735626010_MSSQLServer08T-SQLFundamentals_ch03.pdf


--
Plamen Ratchev
http://www.SQLStudio.com

On Wednesday, April 01, 2009 9:09 AM
iNewbi wrote:

Re: left join or inner join?
thanks to everybody!

the diagram is really helpfull.



"Plamen Ratchev" wrote:


Submitted via EggHeadCafe - Software Developer Portal of Choice
Build an ASP.NET Thumbnail Image Generator
http://www.eggheadcafe.com/tutorials/aspnet/3293e4e0-043b-4359-bbda-345afa8604cd/build-an-aspnet-thumbnai.aspx
From: Tom Cooper on 17 Dec 2009 02:27
That is the only difference between a LEFT JOIN and a RIGHT JOIN. That is

SELECT <column list>
FROM TableA
LEFT OUTER JOIN TableB ON <join condition>

will return the same result set as

SELECT <column list>
FROM TableB
RIGHT OUTER JOIN TableA ON <join condition>

Tom

"Rakesh Shrivastava" wrote in message
news:2009121711436rakesh.shrivastava27(a)gmail.com...
> Can you tell me the Diff Between Left And Right join i know this diff but
> not this one some more core diff we can used Left join and changeing order
> of the table we can achived the right join result. why we requred to join
> ........?
> LEFT JOIN: Return all rows from the left table, even if there are no
> matches in the right table
> RIGHT JOIN: Return all rows from the right table, even if there are no
> matches in the left table
>
>
>
> Plamen Ratchev wrote:
>
> Re: left join or inner join?
> 31-Mar-09
>
> In addition, Itzik Ben-Gan's book "Microsoft SQL Server 2008: T-SQL
> Fundamentals" is a great tutorial. There is a sample chapter on joins:
> http://www.sql.co.il/books/tsqlfund2008/9780735626010_MSSQLServer08T-SQLFundamentals_ch03.pdf
>
>
> --
> Plamen Ratchev
> http://www.SQLStudio.com
>
> Previous Posts In This Thread:
>
> On Tuesday, March 31, 2009 10:07 PM
> iNewbi wrote:
>
> left join or inner join?
> hi, i just wanted to know, what has the better performance, a left join
> (or
> right) or an inner join?
>
> also, do you have some website that explains how sql server makes a join
> when a query is processed?
>
> thanks!!!
>
> On Tuesday, March 31, 2009 10:23 PM
> Plamen Ratchev wrote:
>
> Inner join returns only rows that satisfy the join conditions.
> Inner join returns only rows that satisfy the join conditions. Left
> outer join has one more logical step to add the outer rows for the
> preserved table. In that regards results may differ and not sure it
> makes sense to compare performance. Based on the logic of your query you
> have to use the correct join type to retrieve the needed results.
>
> Two resources you may find useful:
>
> Diagram for the logical query processing:
> http://www.sql.co.il/books/insidetsql2008/Logical%20Query%20Processing%20Poster.pdf
>
> More details on left outer joins:
> http://www.ibm.com/developerworks/data/library/techarticle/purcell/0112purcell.html
>
>
> --
> Plamen Ratchev
> http://www.SQLStudio.com
>
> On Tuesday, March 31, 2009 10:33 PM
> Dan Guzman wrote:
>
> An inner join typically performs better because more work is needed in
> outer
> An inner join typically performs better because more work is needed in
> outer
> joins to preserve unlatching outer rows.
>
>
> See Itzik' poster and books for a descriptions of the logical order of
> query
> processing:
> http://www.sql.co.il/books/insidetsql2008/Logical%20Query%20Processing%20Poster.pdf
>
> Note that the actual order of execution is often different than the
> logical
> order of evaluation. SQL Server strives to perform the task as
> efficiently
> as possible while providing the correct logical results.
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
>
> "iNewbie" <iNewbie(a)discussions.microsoft.com> wrote in message
> news:EB3EEF2B-5590-428B-92AE-8D77D1DB33B1(a)microsoft.com...
>
> On Tuesday, March 31, 2009 11:07 PM
> Plamen Ratchev wrote:
>
> Re: left join or inner join?
> In addition, Itzik Ben-Gan's book "Microsoft SQL Server 2008: T-SQL
> Fundamentals" is a great tutorial. There is a sample chapter on joins:
> http://www.sql.co.il/books/tsqlfund2008/9780735626010_MSSQLServer08T-SQLFundamentals_ch03.pdf
>
>
> --
> Plamen Ratchev
> http://www.SQLStudio.com
>
> On Wednesday, April 01, 2009 9:09 AM
> iNewbi wrote:
>
> Re: left join or inner join?
> thanks to everybody!
>
> the diagram is really helpfull.
>
>
>
> "Plamen Ratchev" wrote:
>
>
> Submitted via EggHeadCafe - Software Developer Portal of Choice
> Build an ASP.NET Thumbnail Image Generator
> http://www.eggheadcafe.com/tutorials/aspnet/3293e4e0-043b-4359-bbda-345afa8604cd/build-an-aspnet-thumbnai.aspx

 | 
Pages: 1
Prev: Audit login
Next: SQL statement - 'chop' phrase