Some questions are timeless and they never grow old; no matter how much they grow old their interest never dies. Earlier, I asked a simple puzzle based on a conversation on SQLAuthority Page, and have received an overwhelming response from readers. I still get emails related to this puzzle every day. Let us see a quick puzzle between SQL Join and SQL Null.
Before we move on, I suggest you read the question and its initial solution here:
The puzzle had three parts and the users had to fill up the second part, which I had left it for them to complete. I received many responses, and here is another response which is absolutely valid and worthy of acknowledgment. Once again, before you read the solution, I suggest you give the puzzle a quick try. Perhaps you’d find another neat way to solve this puzzle.
The puzzle had three parts:
- Fill in the blanks – T-SQL code
- Query and Resultset
Just like this puzzle, there are many other learning nuggets in our SQL Server Questions and Answers course online.
Complete Solution Query – SQL Join
USE TempDB GO -- Sample Table CREATE TABLE FirstTable (Col1 INT) CREATE TABLE SecondTable (Col1 INT) GO ALTER TABLE FirstTable ADD CONSTRAINT UX_FirstTable UNIQUE NONCLUSTERED (Col1); ALTER TABLE SecondTable ADD CONSTRAINT UX_SecondTable UNIQUE NONCLUSTERED (Col1); GO INSERT INTO FirstTable (Col1) VALUES (NULL) GO INSERT INTO SecondTable (Col1) VALUES (NULL) GO -------------------------------------- -- Solution SELECT st.col1 FROM FirstTable ft RIGHT JOIN SecondTable st ON st.Col1 = ft.Col1 UNION ALL SELECT st.col1 FROM FirstTable ft LEFT JOIN SecondTable st ON st.Col1 = ft.Col1 GO DROP TABLE FirstTable; DROP TABLE SecondTable; GO
Now the reason I did not select this as a valid answer is because if you notice original solution required to insert one row into the FirstTable. However, when you look at carefully, you can notice even this solution is a valid solution. There were many other solutions which require to change T-SQL which I initially provided, however, as per the puzzle that was not allowed.
Any other solution out there?
Reference: Pinal Dave (https://blog.sqlauthority.com)