Can all correlated sub queries be queried using joins in Postgresql?

Question

So say I have a table

Dress(Dress_id, Colour, Value) and another table

On_Loan(Dress_id, Start_date, End_date)

and I have this query, which I am not sure it is a correlated sub query or not

SELECT D.COLOUR,D.VALUE FROM DRESS WHERE D.DRESS_ID = (SELECT ON_LOAN.DRESS_ID FROM ON_LOAN WHERE ON_LOAN.DRESS_ID = D.DRESS_ID);

Basically I want to return the colour and values of the dresses that are on loan. I have also queried it like this:

SELECT COLOUR,VALUE FROM DRESS INNER JOIN ON ON_LOAN WHERE DRESS.DRESS_ID = ON_LOAN.DRESS_ID);

Both of which give the same output, so i am wondering if all correlated subqueries can be turned into equivalent queries with joins?

Thanks.

Answer 1

Many correlated subqueries can be represented as JOINs. In fact, correlated subqueries are a type of join in the general sense.

But if you are asking if a "simple" JOIN operator query is always available, the answer is "no". A pretty simple example is:

select t.*,
       (select tt.x
        from tt
        where tt.y = t.y
        order by tt.z
        limit 1
       ) as z
from t;

Of course, this can be expressed without using a correlated subquery. But it requires more than a single JOIN operation.