Why does LEFT JOINing leave the second table's id NULL?

Question

Please refer to the following answer first: this

So in that answer, it provided a way to take a data based on one distinct column with its corresponding columns. How it picks which one of that distinct column can be sorted by ascending or descending on the condition WHERE t1.lastname > t2.lastname or WHERE t1.lastname < t2.lastname respectively, which I understand.

I'm still practicing SQL and I have questions in regard to the method provided in the above link.

1. When I tried to select t2.id, it is all null. I can't comprehend this, isn't t2 basically the same table as t1? If so, how is it possible that its id became NULL, but not t1s ids?
2. Why is it necessary to check WHERE t2.id IS NULL when all t2.id is going to return NULL anyway?
3. This is a part about which I think I have a slight idea. However, please correct me if I'm wrong. The above method worked (let's talk about the descending order here) because: firstly, the I LEFT JOINed my t1 and t2 together based on their ids. Secondly, I also check that the t1.lastname has to be bigger (>) than t2.lastname which I assume is using ASCII or UNICODE values, which returns me voila! Only one result; the one that has a higher value. Okay now more question: does it check the t1.lastname with t2.lastname one by one but to all t2.lastname and then returns nothing if just one of the t2.lastname renders that condition invalid?

I think that I'm missing something about something here. Could someone please help me? Thank you in advance.

Answer 1

LEFT JOIN will show left part of the join(values from left table) and will show:

• values from right table in right part of the join if join condition are fulfilled or
• null values if join condition are not fulfilled

For example:

   INSERT INTO #test (id, firstname, lastname)
   VALUES
   (1, 'A', 'A'),
   (2, 'B', 'B'),
   (3, 'A', 'B'),
   (4, 'B', 'C')

   SELECT t1.*, t2.*
   FROM #test AS t1
   LEFT JOIN #test AS t2
   ON t1.firstname = t2.firstname
   AND t1.lastname < t2.lastname

it will show

1   A   A   3   A   B
2   B   B   4   B   C
3   A   B   NULL    NULL    NULL
4   B   C   NULL    NULL    NULL

It shows all rows from t1 but last two rows will have nulls for t2 becaue join condition t1.lastname < t2.lastname is not fulfilled. For AB we don't have rows with greater lastname value than B, and for BC we don't rows with greater lastname value than C.

If you now add WHERE condition:

SELECT t1.*, t2.*
FROM #test AS t1
LEFT JOIN #test AS t2
ON t1.firstname = t2.firstname
AND t1.lastname < t2.lastname
WHERE t2.id IS NULL;

you limit you result to rows which didn't fulfilled join conditions.