Hello everyone

I'm sure this is an old question, but I can't find a good explanation.
I've read that many, but not all, SQL queries with a subselect are in
fact equivalent to a query doing a join of the relevant tables with a
join condition.

What is an easy way for me to see this equivalence? And how do I know
when I can't use a join and *have* to use a sub-select?

thanks,
Jeff

Jeff Calico wrote:
You can write subselect in many place.
For example:
1) FROM ... <table> JOIN (subselect) ...
2) FROM ... <table> JOIN LATERAL (subselect) ...
3) WHERE ... <expression> = (subselect) ...
4) WHERE ... <expression> IN (subselect) ...
5) WHERE ... <expression> BETWEEN (subselect) AND (subselect) ...
6) WHERE ... EXISTS (subselect) ...
7) SELECT ... , (subselect), ...
perhaps more.

Or, did I misunderstand exsact definition of subselect?

Tonkuma wrote:
Another thing to consider is that there are correlated subqueries and
noncorrelated subqueries.

Correlated means the subquery returns a different result based on the
row of the superquery, e.g.:

SELECT * FROM foo
WHERE foo.col1 IN (SELECT blah FROM bar WHERE bar.col2 = foo.col2)

Noncorrelated means the subquery is invariant on all rows of the
superquery, e.g.:

SELECT * FROM foo
WHERE foo.col1 IN (SELECT blah FROM bar WHERE bar.col2 = 42)

I can't think of a concise way to define when subqueries can be
rewritten as joins. There are some cases that are well-known, like the
noncorrelated case above. But trying to write a guide to cover _all_
these cases is really difficult.

Regards,
Bill K.