It seems that we all love to solve puzzles. On SQLAuthority Page, we have been playing the number game and those who are playing with us know how much fun we are having. Sometimes, the answers are so innovative and informative that they open up those aspects of the technology which I have not thought of. Today, I have a very relaxing puzzle and a SQL Brain Teaser for all of you. It is based on my earlier blog post on INNER JOIN and NULL, so I suggest reading the said post first if you want to get the complete idea.
The puzzle has three parts:
- Setup
- Fill in the blanks – T-SQL code
- Query and Resultset
We will set up the sample table first. We will run the sample query, which will give us some result. You will have to figure out what the missing script will be, which in turn will generate a specific displayed result.
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 -------------------------------------- -- Missing T-SQL Statement -------------------------------------- -- SQL Query SELECT st.col1 FROM FirstTable ft LEFT JOIN SecondTable st ON st.Col1 = ft.Col1 GO -- Clean up DROP TABLE FirstTable; DROP TABLE SecondTable; GO
Current Resultset:
Expected Resultset:
Try harder. You will figure it out. If you cannot figure out and you want to give up, here is the answer.
If you do not feel like doing SQL any further, try the following image of Mars taken by Viking 1 in 1976.
Try to find the human face:
Do let me know if you like this kind of puzzle and we will have many more of these.
Reference: Pinal Dave (https://blog.sqlauthority.com)
20 Comments. Leave new
INSERT INTO FirstTable (Col1)
VALUES (1)
Hi Pinal sir,
We like this type puzzle. Its like game and fun.
Here we have the expected resultset :
SELECT st.col1
FROM FirstTable ft
LEFT JOIN SecondTable st ON st.Col1 = ft.Col1
UNION ALL
SELECT st.col1
FROM FirstTable ft
RIGHT JOIN SecondTable st ON st.Col1 = ft.Col1
Explanation :
First we can’t use INNER JOIN, it provides blank resultset.
So we have to go with other joins.
By running first query we are getting result from FirstTable.
We also want multiple NULL values from that.
We have to take out NULL from Second Table by RIGHT OUTER JOIN.
Regard$
Chirag Satasiya
you are providing simplest form of SQL. I am hoping much more from your side. You are master & teach us to become expert on SQL.
We all start with small and go to next level.
Hello Pinal Sir,
How r u ?
Samarth reply is correct.
INSERT INTO FirstTable (Col1)
VALUES (4)
we cant insert any value in the FirstTable to crate a second row. Left Outer join will return all rows from the Left table(Two rows in this case).
Reason For this output : we are getting right table colum( col1 ) from this query. It will return NULL for both rows because when we compare NULL to any Value(Including NULL itself), The result will always be UNKNOWN (NULL).
Thanks,
Ashish Jain
INSERT INTO FirstTable (Col1)
VALUES (”)
“when we compare NULL to any Value(Including NULL itself), The result will always be UNKNOWN (NULL).” Technically, that isn’t true. First, a comparison must always return true or false so the assesment of the comparison can be made. Second, the assesment of NULL=NULL will return true if “SET ANSI_NULLS OFF” is executed. If it is set on, (default) the result is false. Third, no matter what the setting is, the assesment of NULL=NULL will return true if you try to insert a second null field value into a uniquely constrained field. That was why the second insert in the first table wasn’t a null value. And finally, fourth, you could truncate the second table and there still would be two null rows returned because the left join requires returning all the rows of the first table (unless a where clause overrides it)
For strange results try the following query setting the first statement off and on:
SET ANSI_NULLS ON
DECLARE @tbl TABLE(i1 int UNIQUE)
INSERT INTO @tbl VALUES (NULL),(2),(3),(4),(5)
SELECT a.i1 first
FROM @tbl a
WHERE a.i1 NOT IN (SELECT i1 FROM @tbl WHERE i1 IS NULL OR i1 = 3)
SELECT a.i1 second
FROM @tbl a
WHERE a.i1 NOT IN (SELECT i1 FROM @tbl WHERE i1 = 2)
SELECT st.col1
FROM FirstTable ft
FULL JOIN SecondTable st ON st.Col1 = ft.Col1
GO
Pinal,
Love the puzzle. Keep them comming. Love your Blog!
I agree with Samarth , we have to insert any value in the first table as null=null condition is like “”
R Jagtap
Your comment is awaiting moderation.
I agree with Samarth , we have to insert any value in the first table as null=null condition is like
Hi Pinal,
I think we’ll have to use below code into ‘Missing T-SQL Statement’ section.
SELECT st.col1
FROM FirstTable ft
LEFT JOIN SecondTable st ON st.Col1 = ft.Col1
UNION ALL
Hey sir you must design a course SSEPD(Sql server expertise with pinal dave)..i will make sure i m first to enroll.
About puzzle it was nice please keep up updating with this tits n bits of sql.it makes us confident…
SELECT st.col1
FROM FirstTable ft
FULL OUTER JOIN SecondTable st ON st.Col1 = ft.Col1
Result :
col1
——
NULL
NULL
SELECT st.col1
FROM FirstTable ft
Right outer JOIN
(
select Col1 from SecondTable
union all
select NULL As Col1
) As st
ON st.Col1 = ft.Col1
SELECT st.col1
FROM FirstTable ft
right JOIN SecondTable st ON st.Col1 = ft.Col1
GO
Hi Pinal,
Can you please help us understand whats the difference between Full Join and Cartisian product, because in above puzzel, when there are null values in the both tables i get two records in the result of Full join query (which is what is answer to your Puzzel) but if i have any other number or value rather then null lets say 1, i am getting only 1 record as result of the same full join query.
Regards,
Qutub…
Hi Pinal,
I have a different question on Joins, could you please state the reason why we have case in-sensitive joins on SQL Server?
I have worked on different databases as well and we have case sensitive joins there. Is there a specific reason for this kind of behavior?
Following SQL returns the data irrespective of the join conditions.
SELECT * FROM TABLE1 A
INNER JOIN TABLE2 B
ON UPPER(A.NAME)=LOWER(B.NAME);
Thanks in advance!!
Regards,
Litesh
Interesting post to whom willing to learn sql.. Here below my ans!
INSERT INTO FirstTable (Col1)
VALUES (“any values here”)
with CtrNull as
(
SELECT st.col1
FROM FirstTable ft
LEFT JOIN SecondTable st ON st.Col1 = ft.Col1
),
CteNull as
(
SELECT st.col1
FROM FirstTable ft
LEFT JOIN SecondTable st ON st.Col1 = ft.Col1
)
SELECT * from CtrNull
union all
SELECT * from CteNull