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

[@Sankeeislive]

Bhai foreign jana h 😂leke chaloge hame bhi 😂😂😂😂😂

[@santypanda4903]

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

[@Someonner]

AMEX also asks the same question

[@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 👍🏻

[@m04d10y1996]

Null doesn't join with itself.​@@TheBigDataShow

[@NiteeshKumarPinjala]

select table_1.id, count(*)
from table_1 left join table_2 on table_1.id = table_2.id
group by table_1.id
Am i wrong?