Search code examples
sqlpostgresqljoin

PostgreSQL Join Types

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

My answer:

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

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?

Solution

  • 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.