left join question

Question

left join question

curious_sqldba

SSC-Dedicated

Points: 36494
More actions
August 16, 2013 at 9:41 am

#282101

select a.col1,a.col2
from Table1 A
left join Table2 B
select a.col1,a.col2
from Table1 A
Would these two queries return same result set?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

curious_sqldba (8/16/2013)
select a.col1,a.col2 from Table1 A left join Table2 B select a.col1,a.col2 from Table1 A
Would these two queries return same result set?

They would if either of the tables has only 1 row. The reality is most likely no, they won't.

--EDIT--

I completely misread the second query at a glance.

~~The second query is a cross join which will return a cartesian product of the two tables.~~

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Ness SSCarpal Tunnel Points: 4282 More actions · Answer 2

Sean Lange (8/16/2013)
curious_sqldba (8/16/2013)
select a.col1,a.col2 from Table1 A left join Table2 B select a.col1,a.col2 from Table1 A
Would these two queries return same result set?
They would if either of the tables has only 1 row. The reality is most likely no, they won't.
The second query is a cross join which will return a cartesian product of the two tables.

Not sure where the cartesian product is? You are only looking at one table

If there is more than one matching row in table2 you would have more rows.

SQL DBA
Every day is a school day, and don't trust anyone who tells you any different.
http://sqlblogness.blogspot.co.uk

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

I believe that Sean, misread the queries.

The correct answer is it depends. As Ness said, Table2 might affect the results to get duplicates.

Here's an example.

DECLARE @Table1 TABLE(

col1 int,

col2 char(1))

DECLARE @Table2 TABLE(

col1 int,

col2 char(1))

INSERT INTO @Table1

VALUES( 1, 'A'),( 2, 'B'),( 3, 'C'),( 4, 'D')

INSERT INTO @Table2

VALUES( 1, 'A'),( 1, 'B'),( 1, 'C'),( 4, 'D')

select a.col1,a.col2

from @Table1 A

left join @Table2 B ON a.col1 = b.col1

select a.col1,a.col2

from @Table1 A

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

Oh good grief. I misread that query. I thought the second query was a cross join. I read it as this:

select a.col1,a.col2

from Table1 A, Table2 B

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

ScottPletcher SSC Guru Points: 101122 More actions · Answer 5

In the context of a LEFT JOIN, yes, the queries would return the same rows from TableA (although, as noted, they might appear multiple times because of the join).

That is, since it's a LEFT JOIN, TableB will never prevent all rows from being pulled from TableA, with any reasonable join conditions.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".