Understanding this SQL LEFT JOIN (with IS NULL) example
<p>In a database, there is a table called category:</p>

<pre><code>CREATE TABLE category(
        category_id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(20) NOT NULL,
        parent INT DEFAULT NULL
);
</code></pre>

<p>It was created to make an Adjacent List Model Tree. Here is what is inside the table currently:</p>

<p><a href="https://i.stack.imgur.com/9tXUx.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/9tXUx.png" alt="enter image description here"></a></p>

<p>In this example (you can find the source link at the bottom), the following sql code was used to obtain of the "leaf" elements in the table, with "leaf" elements being the rows in the table that did not have any other row use their "category_id" in the "parent" column. The following code was run:</p>

<pre><code>SELECT t1.name FROM
category AS t1 LEFT JOIN category as t2
ON t1.category_id = t2.parent
WHERE t2.category_id IS NULL;
</code></pre>

<p>The result of the previous SQL code gives this result:</p>

<p><a href="https://i.stack.imgur.com/3mUUs.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/3mUUs.png" alt="enter image description here"></a></p>

<p>For example, there is no row in the table that has the value 3 inside the parent column, hence TUBE (with category_id == 3) is a "leaf" element.</p>

<p><strong>QUESTION:</strong> Why did that sql code logically give this result? I'm happy that it does since it is what I needed, but I can't wrap my head around the reasoning behind it.</p>

<hr>

<p>source of the example: <a href="http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/" rel="nofollow noreferrer">http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/</a></p>