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.