What is a "self join" for?

A use of the self join that I consider "practical" (since its use to represent trees can be problematic) is to make control of competition. As I detailed in another answer , suppose you have a data that could be the subject of multiple concurrent writes, but it is important that only one of them be successful (for example, the purchase of the last item in the stock). Transactions will not only prevent a race condition (i.e. one overwrites the data of the other), and pessimistic locking can degrade performance.

One solution is to use a "stock chain": the current stock is represented by a row in the table, and when the stock changes instead of changing the row a new one is created - with reference to the previous row :

create table estoque(
    id         integer   not null,
    valor      integer   not null,
    anterior   integer   null        unique,
    primary key (id),
    foreign key (anterior) references estoque(id)
);

In this way, you can make transactions parallel to the will, because if the chance of two tries to change the stock at the same time, the second will always fail - because the new line added will violate the UNIQUE restriction. >

And where does the self join go? At the time of searching for the current stock:

select e1.id, e1.valor
from estoque e1
  left outer join estoque e2 on e2.anterior = e1.id
where e2.id is null;

i.e. "select the line that is not the line from any other line".

Update: Caffé's answer in a related question shows a better way to control competition. However, there are still cases where this "chain of records" can be useful - for example, when you want to keep not only the latest record but also the history of past values (while controlling the competition). An example would be wiki content, where two or more people can try to edit at the same time. A revision chain not only ensures that one edition does not overwrite the other, but also keeps all edits in the history.

Trees can be represented in a single table. For example:

eletrônicos
    áudio e vídeo
        TVs
            HD
            Full HD
            4k
        Home Theaters
        ...
    informática
    ...
...

In table form:

id       parent_id     nome
----------------------------------------
1        NULL          eletrônicos
2        1             áudio e vídeo
3        2             TVs
4        3             HD
5        3             Full HD
6        3             4k
7        2             Home Theaters
8        1             informática  

A query that starts from any node in the tree can use self joins to move up the hierarchy:

SELECT
    categorias.nome AS categoria,
    pai.nome AS pai,
    avo.nome AS avo
FROM categorias
    LEFT OUTER JOIN categorias pai
    ON pai.id = categorias.parent_id
    LEFT OUTER JOIN categorias avo
    ON avo.id = pai.parent_id
WHERE categorias.id = 6 -- 4k

Result:

categoria   pai    avo
---------------------------------
4k          TVs    áudio e vídeo

As I said at the outset, trees may be represented in this structure, but the best representation may be different depending on how you need to use the data. For more details on tree structures in databases, see #

Self Join is used when a table references its own data. Looked like this:

SELECT * 
FROM Table t1, Table t2
WHERE t1.Id = t2.ID