PostgreSQL Join Types

Question

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?

There are things called anti-joins, although they are not handled using the JOIN keyword. — Gordon Linoff
– Gordon Linoff, Commented Nov 6, 2020 at 17:29
The UNION JOIN was part of the SQL Standard until 2003. Afaik PostgreSQL never implemented it, though. — The Impaler
– The Impaler, Commented Nov 6, 2020 at 17:31
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, Commented Nov 7, 2020 at 2:28
No, it is not clear to anyone, and if you can't clearly say what you mean then it's not clear to you either. Look at the comments & the answers--"your professor might be"--"Lateral joins are not a different join type". Nobody knows what you mean, they are guessing. It seems that you are just hoping that there is some commonly understood thing that your professor & others use it for or would all use it for. There isn't. — philipxy
– philipxy, Commented Nov 7, 2020 at 11:05

GMB · Accepted Answer · 2020-11-06 17:32:00Z

4

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.

answered Nov 6, 2020 at 17:32

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Laurenz Albe · Accepted Answer · 2020-11-06 19:07:21Z

3

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.

answered Nov 6, 2020 at 19:07

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Collectives™ on Stack Overflow

PostgreSQL Join Types

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related