CODEWITHSUNDEEP
sqlpostgresqljoin
idriskameni
idriskameni

Reputation: 173

PostgreSQL Join Types

A homework question asked me to list all possible join types in PostreSQL.

My answer:

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

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

What are other JOIN types?

Upvotes: 1

Views: 331

Answers (2)

Laurenz Albe
Laurenz Albe

Reputation: 247595

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.

Upvotes: 3

GMB
GMB

Reputation: 222632

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.

Upvotes: 4

Related Questions