Learning PostgreSQL 11 - Third Edition

Self-joins

It's possible to join a table with itself. This is called a self-join. A self-join has no special syntax. In fact, all the data sources in a query are independent, even though they could be the same physically. Imagine you want to find out for the a table, for every record, if there are other records with a bigger value of the a_int field . The following query can be used for this:

car_portal=> SELECT t1.a_int AS current, t2.a_int AS bigger FROM car_portal_app.a t1 INNER JOIN car_portal_app.a t2 ON t2.a_int > t1.a_int;current | bigger--------+--------      1 |      2      1 |      3      2 |      3(3 rows)

The a table is joined to itself. From the logic of the query, it doesn't matter whether two different tables are joined or the same table is used twice. ...