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/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/