Is this the full video? Where is the link? I thought it got abruptly cut at the end.
@Someonner2 ай бұрын
AMEX also asks the same question
@vabz_parab2 ай бұрын
Record count for 'NULL' in case of LEFT JOIN should be 2. Not 4 (Wrong output shown in the last)
@TheBigDataShow2 ай бұрын
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.
@TheBigDataShow2 ай бұрын
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 } }
@TheBigDataShow2 ай бұрын
@vabz_parab thank you for correction
@vabz_parab2 ай бұрын
@@TheBigDataShow thanks and welcome!! And I liked your 1st query approach too. Seems interesting 👍🏻