SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


LEFT JOIN


LEFT JOIN

Author
Message
kapil_kk
kapil_kk
SSCertifiable
SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)

Group: General Forum Members
Points: 7656 Visits: 2776
Comments posted to this topic are about the item LEFT JOIN

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
baabhu
baabhu
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3243 Visits: 1218
For me at least option b and option d looks pretty much similar.

option B.

Query 1 will return all rows of table 1 and Query 2 will return only rows where condition matched Flag = 1

Option D
Query 1 return all rows of table 2 where Flag =1 with all rows of table 1 while Query 2 only return rows from table 1 and table 2 where FLAG = 1
Danny Ocean
Danny Ocean
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2770 Visits: 1549
baabhu (4/3/2013)
For me at least option b and option d looks pretty much similar.

option B.

Query 1 will return all rows of table 1 and Query 2 will return only rows where condition matched Flag = 1

Option D
Query 1 return all rows of table 2 where Flag =1 with all rows of table 1 while Query 2 only return rows from table 1 and table 2 where FLAG = 1




Hi Kapil, I agree with baabhu.

Both option B and D are look same. Let see result of queries.

Query 1:-
We are using left join. It means, it include all records from left table and matched records from right tables.
So, 7 records come from Table1 and 2 records come from Table2 due to condition in join (AND t1.flag = 1).
If you remove this condition from join, it will give you 3 records from Table2.

Query2:-
Again we are using left join between Table1 and Table2. it include all records from Table1 and matched records from Table2.
After get the data from joins, where condition (WHERE t2.flag = 1) will take place.
This will return 2 records both tables because we put the condition on Table2.flag column.

Let me know if you have any concern.

Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!
www.GrowWithSql.com
kapil_kk
kapil_kk
SSCertifiable
SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)

Group: General Forum Members
Points: 7656 Visits: 2776
Hi all,

I also thinks that the options are not well written... Sorry for that Sad ..
My point of concern was to to throw light on different behaviors of AND and WHERE clause when use with LEFT and INNER JOIN..

--Query 1
SELECT
*
FROM table1 t1
LEFT JOIN table2 t2
on t1.id = t2.id AND t1.flag = 1

--Query 2
SELECT
*
FROM table1 t1
left JOIN table2 t2
on t1.id = t2.id
WHERE t2.flag = 1

If you execute these queries you will get same amount of rows in case of INNER JOIN while no of rows gets different when you put LEFT JOIN in this...

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Danny Ocean
Danny Ocean
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2770 Visits: 1549
kapil_kk (4/4/2013)
Hi all,

I also thinks that the options are not well written... Sorry for that Sad ..
My point of concern was to to throw light on different behaviors of AND and WHERE clause when use with LEFT and INNER JOIN..

--Query 1
SELECT
*
FROM table1 t1
LEFT JOIN table2 t2
on t1.id = t2.id AND t1.flag = 1

--Query 2
SELECT
*
FROM table1 t1
left JOIN table2 t2
on t1.id = t2.id
WHERE t2.flag = 1

If you execute these queries you will get same amount of rows in case of INNER JOIN while no of rows gets different when you put LEFT JOIN in this...


I appreciate your reply and don't worry about mistakes. It's courage to take a step, learning from mistakes and improve it for future. :-)

Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!
www.GrowWithSql.com
kapil_kk
kapil_kk
SSCertifiable
SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)

Group: General Forum Members
Points: 7656 Visits: 2776
Danny Ocean (4/4/2013)
kapil_kk (4/4/2013)
Hi all,

I also thinks that the options are not well written... Sorry for that Sad ..
My point of concern was to to throw light on different behaviors of AND and WHERE clause when use with LEFT and INNER JOIN..

--Query 1
SELECT
*
FROM table1 t1
LEFT JOIN table2 t2
on t1.id = t2.id AND t1.flag = 1

--Query 2
SELECT
*
FROM table1 t1
left JOIN table2 t2
on t1.id = t2.id
WHERE t2.flag = 1

If you execute these queries you will get same amount of rows in case of INNER JOIN while no of rows gets different when you put LEFT JOIN in this...


I appreciate your reply and don't worry about mistakes. It's courage to take a step, learning from mistakes and improve it for future. :-)

Thanks :-)

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Carlo Romagnano
Carlo Romagnano
SSCrazy Eights
SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)

Group: General Forum Members
Points: 9352 Visits: 3441
baabhu (4/3/2013)
For me at least option b and option d looks pretty much similar.

option B.

Query 1 will return all rows of table 1 and Query 2 will return only rows where condition matched Flag = 1

Option D
Query 1 return all rows of table 2 where Flag =1 with all rows of table 1 while Query 2 only return rows from table 1 and table 2 where FLAG = 1


+1
Real bad worded qotd.
Hugo Kornelis
Hugo Kornelis
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25037 Visits: 12583
Great idea for a question, Kapil!

But ... (isn't there always a "but"?)

When I saw the question in the newsletter, I expected the answer options to be phrased in terms of the supplied data. Like, for instance, "Both query 1 and query 2 returns rows marked thrid, fourth and ninth" (not saying this is either the right answer or even a believable distracter, just giving a random example). After all, what is the point of giving sample data in the question if you're not using it in the answer?
So I worked out both queries in my head and knew the result sets to expect when I clicked on the link to the website - and found that the answer options were just descriptions instead of actual data.

Then, the descriptions were not very clear worded. Kapil already admitted so himself, so I won't pound on that.

And finally, the answer marked as "correct" is not correct. I'm afraid Kapil himself has fallen victim to misunderstanding his own words, or to misphrasing what he had in mind. And the people claiming that answer options 2 and 4 are the same are also wrong.

Option 4, marked as correct, starts with: "Query 1 return all rows of table 2 where Flag =1 with (...)". You can even stop reading there (though I did read the whole answer). This already makes the answer option wrong. Row "eighth" in Table2 has Flag=1, but should not be returned by Query 1. (To make very sure I was not mistaken, I copied and pasted the code in SSMS, fixed the lowercase/uppercase inconsistencies, and executed the code - I was not mistaken).

Option 2, marked as incorrect, reads: "Query 1 will return all rows of table 1 and Query 2 will return only rows where condition matched Flag = 1". This is correct. Query 1 returns all rows from table1 (because there is no WHERE filter to cut out Table1 rows, and a LEFT join will never remove rows from the table listed on the left hand side). Rows "First" and "Second" are matched to the corresponding row from Table2, because the entire join condition evaluaties as true - the ID values match and Flag = 1. The other rows are "matched" to null values, because there are no rows in Table2 that satisfy the join - either the ID values don't match, or Flag is not 1 (or both). And rows "Eighth" and "Ninth" from Table2 do not appear anywhere, because they can also not be matched to any row in Table1 with the join condition evaluating to true.


Too bad that a question with a good idea suffers from poor execution. But I'm going to say to Kapil what I said yesterday to the author of that day's question: having good ideas for questions (and the guts to submit them) is the most important. Don't give up, keep submitting questions. I'm sure the next will be better!


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Danny Ocean
Danny Ocean
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2770 Visits: 1549
Thanks Hugo for last two lines.
:-)

Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!
www.GrowWithSql.com
DavidBridgeTechnology.com
DavidBridgeTechnology.com
SSC Veteran
SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)

Group: General Forum Members
Points: 265 Visits: 238
I am sorry to have to say that I did not like the wording of the question or the answers and I am surprised at myself for answering this question at all as normally I wouldn't if I thought there was ambiguity.

I answered 2 ( or b)

My reckoning being that the first query would return all rows with matching id from table 1 regardless of flag in table 2 and the second query would take head of the flag status in table 2 so have a reduced result set.

Although I think the wording is poorly chosen, I have to agree with Hugo when he says that submitting a QOTD is a bit gutsy so well done in this respect. I did one a few weeks back and was worried about the slating I would get for it.

David Bridge
David Bridge Technology Limited
www.davidbridgetechnology.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search