April 3, 2013 at 9:26 pm
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/
April 3, 2013 at 9:32 pm
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
April 3, 2013 at 11:47 pm
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 !!!
April 4, 2013 at 12:03 am
Hi all,
I also thinks that the options are not well written... Sorry for that 🙁 ..
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/
April 4, 2013 at 12:26 am
kapil_kk (4/4/2013)
Hi all,I also thinks that the options are not well written... Sorry for that 🙁 ..
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 !!!
April 4, 2013 at 12:33 am
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 🙁 ..
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/
April 4, 2013 at 1:12 am
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.
April 4, 2013 at 1:35 am
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!
April 4, 2013 at 1:52 am
Thanks Hugo for last two lines.
🙂
Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!
April 4, 2013 at 2:24 am
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
April 4, 2013 at 2:34 am
I suggest for next authors to keep in mind few rules:
1 Be simple
2 Be clear
3 Be synthetic
April 4, 2013 at 2:48 am
Sorry to say that I also think that the wording of the answers is poor, and that the correct answer should be b.
April 4, 2013 at 3:04 am
Hugo Kornelis (4/4/2013)
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!
Thanks Hugo for your words 🙂
I thought of making this as QOTD as previously I was not aware of that fact of AND n WHERE clause when use them with JOINS...
As, if I write the same queries using INNER JOIN with AND and WHERE, I was getting same no of rows in both case while when I replace INNER JOIN with LEFT JOIN i got different no of rows. So I thought of putting this scenario as QOTD..
Next time I will try to make the QOTD better than today!!!
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 4, 2013 at 4:39 am
Mighty (4/4/2013)
Sorry to say that I also think that the wording of the answers is poor, and that the correct answer should be b.
+1
But it was a good attempt so don't let this put you off, learn and, in the words of the great bard...
Try, Try again! 😀
_____________________________________________________________________
[font="Comic Sans MS"]"The difficult tasks we do immediately, the impossible takes a little longer"[/font]
April 4, 2013 at 5:13 am
Nice idea, not so nice implementation.
It occurs to me that most of us will labor on a question by ourselves, not getting much input, sometimes producing a question that is confusing or not clear.
There are plenty of old hands here, many of whom wouldn't mind spending a few minutes to review a question and make suggestions for improvement. If that were done more often, I think we'd see fewer questions where the majority of the discussion was about how the question was written versus discussion of the topic the author intended to highlight.
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn
Viewing 15 posts - 1 through 15 (of 57 total)
You must be logged in to reply to this topic. Login to reply