Find the output after every JOIN like Left, Right & inner.

[@santypanda4903]

Is this the full video? Where is the link? I thought it got abruptly cut at the end.

[@vabz_parab]

Record count for 'NULL' in case of LEFT JOIN should be 2. Not 4 (Wrong output shown in the last)

[@TheBigDataShow]

It will actually be 4. One NULL will create 2 rows after joining with the other 2 NULL of the right table and then the next NULL will again join with 2 NULL of the right table. Kindly generate these tables in MySQL and cross check this.

[@TheBigDataShow]

I think you are right. If only checked with NULL then the value will be different. I have used the following query ``` WITH JoinedData AS ( SELECT t1.id AS FirstTableID, t2.id AS SecondTableID FROM FirstTable t1 LEFT JOIN SecondTable t2 ON t1.id = t2.id OR (t1.id IS NULL AND t2.id IS NULL) ) SELECT COALESCE(FirstTableID, 'NULL') AS JoinKey, COUNT(*) AS RecordCount FROM JoinedData GROUP BY COALESCE(FirstTableID, 'NULL'); ``` The output will be this { "1": 2, "2": 1, "3": 2, "NULL": 4 } If you will use simple join then ``` WITH JoinedData AS ( SELECT t1.id AS FirstTableID, t2.id AS SecondTableID FROM FirstTable t1 LEFT JOIN SecondTable t2 ON t1.id = t2.id ) SELECT FirstTableID, COUNT(*) AS RecordCount FROM JoinedData GROUP BY FirstTableID; ``` Here in the second query NULL values in FirstTable do not match with any NULL in SecondTable. Output { "LEFT JOIN": { "1": 2, "2": 1, "3": 2, "NULL": 2 } }

[@TheBigDataShow]

@vabz_parab thank you for correction

[@vabz_parab]

@@TheBigDataShow thanks and welcome!! And I liked your 1st query approach too. Seems interesting 👍🏻

[@Someonner]

AMEX also asks the same question