PostgreSQL Join Types

1
votes

While doing some of my homework I found a question where they ask me to list all possible join types in PostreSQL. My answer would be:

  • INNER JOIN
  • LEFT OUTER JOIN
  • RIGHT OUTER JOIN
  • FULL OUTER JOIN
  • CROSS JOIN

with all its NATURAL equivalences (NATURAL INNER JOIN, NATURAL LEFT OUTER JOIN, ...).

However, the professor told us that there exists one more JOIN type that it is difficult to find. In my 2 years of working with SQL I have never seen any other JOIN types than these.

Do you know any other JOIN type? Could you please help me?

Thank you very much.

2
sqlpostgresqljoin
There are things called anti-joins, although they are not handled using the JOIN keyword. – Gordon Linoff
The UNION JOIN was part of the SQL Standard until 2003. Afaik PostgreSQL never implemented it, though. – The Impaler
What about LATERAL JOINs? See 7.2.1.5 Lateral Subqueries – The Impaler
This is not clear.--What does "join type" mean? Is a comma/implicit join a different "type" of JOIN from CROSS JOIN? Are joins without OUTER distinct "types" of join? Are inner joins with ON vs USING 2 different "types"? Does LATERAL give a different "type"? It's a bit much to interpret a join expression using LATERAL as a different type of join. LATERAL is a keyword that a DBMS can require when certain names appear in certain subexpressions. It could have been left out of the language with DBMSs required to handle all cases. The cases that require LATERAL interpret names in the obvious way. – philipxy
@philipxy Hi Philip, I think it was clear to almost all of us. The answers address my question perfectly. Thank you. – idriskameni

2 Answers

4
votes

Your professor might be relating to LATERAL JOINs. That's actually a kind of subquery, that is introduced in the FROM clause.

There is a variety of lateral joins:

  • CROSS JOIN LATERAL

  • INNER JOIN LATERAL

  • LEFT JOIN LATERAL

For more information, you can see the documentation.

2
votes

Lateral joins are not a different join type. Bot there are two more join types in PostgreSQL: semi-joins and anti-joins.

You cannot explicitly specify those join types, but the optimizer can transform subqueries in IN and EXISTS conditions into semi-joins and NOT EXISTS into an anti-join.

You can see these join types in EXPLAIN output.