Question with SELECT instruction from a self-relative table

Question

Hello dev’s I own the following table courses(self relational):

desc cursos;

+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| id_curso  | int(11)     | NO   | PRI | NULL    | auto_increment |
| nome      | varchar(30) | YES  |     | NULL    |                |
| horas     | int(11)     | YES  |     | NULL    |                |
| valor     | float(10,2) | YES  |     | NULL    |                |
| fk_prereq | int(11)     | YES  | MUL | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+

Where fk_prereq is the foreign key that references the course id (from the table itself) that is prerequisite for the other, so if that column assumes a value NULL is because such a course has no prerequisite.

I have some data registered in this table:

select * from cursos;

+----------+-----------------------+-------+--------+-----------+
| id_curso | nome                  | horas | valor  | fk_prereq |
+----------+-----------------------+-------+--------+-----------+
|        1 | db relacional         |    20 | 400.00 |      NULL |
|        2 | business intelligence |    40 | 800.00 |         1 |
|        3 | relatorios avançados  |    20 | 600.00 |         2 |
|        4 | logica programacao    |    10 | 200.00 |      NULL |
|        5 | ruby                  |    40 | 400.00 |         4 |
+----------+-----------------------+-------+--------+-----------+

My question is this, if I want to bring the name, hours, value and the prerequisite of this course, I would usually try to do something like:

select      c.nome
        ,   c.horas
        ,   c.valor
        ,   ifnull(p.nome, '---') as 'pre-requisito' 
from        cursos c 
left join   cursos p on c.id_curso = p.fk_prereq

But that way the projection goes wrong and the result I have is the following:

+-----------------------+-------+--------+-----------------------+
| nome                  | horas | valor  | pre-requisito         |
+-----------------------+-------+--------+-----------------------+
| db relacional         |    20 | 400.00 | business intelligence |
| business intelligence |    40 | 800.00 | relatorios avançados  |
| logica programacao    |    10 | 200.00 | ruby                  |
| relatorios avançados  |    20 | 600.00 | ---                   |
| ruby                  |    40 | 400.00 | ---                   |
+-----------------------+-------+--------+-----------------------+

The correct way would be to do with this instruction:

select      c.nome
        ,   c.horas
        ,   c.valor
        ,   ifnull(p.nome, '---') as 'pre-requisito' 
from        cursos c 
left join   cursos p on p.id_curso = c.fk_prereq

And the result would be:

+-----------------------+-------+--------+-----------------------+
| nome                  | horas | valor  | pre-requisito         |
+-----------------------+-------+--------+-----------------------+
| db relacional         |    20 | 400.00 | ---                   |
| business intelligence |    40 | 800.00 | db relacional         |
| relatorios avançados  |    20 | 600.00 | business intelligence |
| logica programacao    |    10 | 200.00 | ---                   |
| ruby                  |    40 | 400.00 | logica programacao    |
+-----------------------+-------+--------+-----------------------+

In short I did not understand why a simple exchange of on c.id_curso = p.fk_prereq for on p.id_curso = c.fk_prereq, would totally change the expected result, because in a table that is not self-relative if I do the first way I tried the result already comes out correct.

Answer 1

If you read the article Visual Representation of SQL Joins in the Codeproject will be able to quickly understand why the behavior.

In your case it would be as follows:

Basically, a LEFT JOIN allows to obtain all the results of the table A regardless of whether there is an association in the table B, and at the same time will bring the table records B associated by the key fk_prereq at the table A (returning NULL otherwise).

---

In your case I believe the results are different because on the left side of the JOIN, c.id_curso = p.fk_prereq, was the table column A, which is leading the compiler to the idea of "switching" the tables of the JOIN precisely because it is the same table on both sides, that is why when we change the columns it works correctly.