Published on

January 27, 2012

Understanding SQL Server Joins

Have you ever wondered how SQL Server joins work? Joining tables is a fundamental concept in SQL and it’s important to understand how it works in order to write efficient and effective queries.

In a previous blog post, we discussed the INNER JOIN and NULL in SQL Server. Today, we have a puzzle for you to test your SQL skills and deepen your understanding of joins.

The Puzzle

Let’s start by setting up a sample table:

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

Now, we have a missing T-SQL statement. Your task is to figure out what the missing script will be in order to generate a specific displayed result.

-- Missing T-SQL Statement
--------------------------------------
-- SQL Query
SELECT st.col1
FROM FirstTable ft
LEFT JOIN SecondTable st ON st.Col1 = ft.Col1
GO

Once you have figured out the missing script, run the query and compare the current resultset with the expected resultset:

Current Resultset:

Expected Resultset: Try harder. You will figure it out.

If you’re struggling to find the solution, don’t worry! You can find the answer here.

Remember, solving puzzles like this can help you improve your SQL skills and deepen your understanding of SQL Server concepts. So, don’t give up and keep challenging yourself!

Let us know if you enjoyed this puzzle and we’ll bring you more in the future. Happy SQL coding!

Click to rate this post!
[Total: 0 Average: 0]

Let's work together

Send us a message or book free introductory meeting with us using button below.