Predict the output of these queries

-- Create FirstTable
CREATE TABLE FirstTable (in_id INT, vc_value VARCHAR(10), in_flag INT)
GO

-- Populate FirstTable
INSERT INTO FirstTable (in_id, vc_value, in_flag)
SELECT 1, 'First', 1
UNION ALL
SELECT 2, 'Second', 1
UNION ALL
SELECT 3, 'Third', 2
UNION ALL
SELECT 4, 'Fourth', 1
UNION ALL
SELECT 5, 'Fifth', 2
UNION ALL
SELECT 6, 'Sixth', 1
UNION ALL
SELECT 7, 'Seventh', 2
GO

-- Create SecondTable
CREATE TABLE SecondTable (in_id INT, vc_value VARCHAR(10), in_flag INT)
GO

-- Populate SecondTable
INSERT INTO SecondTable (in_id, vc_value, in_flag)
SELECT 1, 'First', 1
UNION ALL
SELECT 2, 'Second', 1
UNION ALL
SELECT 3, 'Third', 2
UNION ALL
SELECT 8, 'Eighth', 1
UNION ALL
SELECT 9, 'Ninth', 2
GO

Predict the output of these queries?

--Query 1
SELECT * FROM FirstTable a
LEFT JOIN SecondTable b ON a.in_id = b.in_id AND a.in_flag = 1
GO

--Query 2
SELECT * FROM FirstTable a
LEFT JOIN SecondTable b ON a.in_id = b.in_id
WHERE b.in_flag = 1
GO

DROP TABLE FirstTable
GO

DROP TABLE SecondTable
GO


Answer: Query 1 returns the rows of SecondTable where in_flag = 1 with all rows of FirstTable. Query 2 only returns rows from FirstTable and SecondTable where in_flag = 1.

Explanation: The ON clause applies before JOIN so it retrieves all the rows of SecondTable where in_flag = 1 but it does not affect FirstTable so it retrieves all the rows of FirstTable. When WHERE clause is applied, it applies to the complete result so it removes all the rows from FirstTable and SecondTable where Flag is not equal to 1, essentially keeping in_flag = 1 rows from FirstTable and SecondTable.

No comments:

Post a Comment