How to use aliases in an Oracle correlated subquery join?

Question

I'm using a subquery for a join operation. When I hard-code parameters, things work fine. But those parameters must come from outer sql which I believe is referred to as correlated query. I would like to pass them in using table aliases but this results in invalid identifier.

Example invented for the purpose of question:

SELECT 
    PR.PROVINCE_NAME 
    ,CO.COUNTRY_NAME
FROM
    PROVINCE PR
    JOIN (
      SELECT COUNTRY_ID, COUNTRY_NAME 
      FROM COUNTRY 
      WHERE COUNTRY_ID=PR.COUNTRY_ID
    ) CO ON CO.COUNTRY_ID=PR.COUNTRY_ID
WHERE
    PR.PROVINCE_ID IN (1,2)

This is typed in, so I hope I haven't introduced any syntax issues here. The question is specifically about COUNTRY_ID=PR.COUNTRY_ID following the WHERE clause. Are aliases legal in such subqueries?

Answer 1

You are using the alias in two different locations. For one it is legal, for the other it is not:

SELECT pr.province_name, co.country_name
FROM province pr
  JOIN (
    SELECT country_id, country_name 
    FROM country 
    WHERE country_id = pr.country_id --<< this reference to the outer table/alias is invalid
  ) co ON co.country_id = pr.country_id -- this is valid
WHERE pr.province_id IN (1,2)

With a (standard) join to a derived table, the inner select can't access an alias or table from the outer select. The "invalid identifier" that you get is cause by the line WHERE country_id = pr.country_id.

Given your JOIN condition you can safely remove that without changing the result. And the Oracle optimizer is smart enough to push that condition into the derived table.

In fact the optimizer will rewrite the query to:

SELECT pr.province_name, co.country_name
FROM province pr
    JOIN country co ON co.country_id = pr.country_id
WHERE pr.province_id IN (1,2);

---

There is however a way of accessing table (or aliases) from the outer query in a derived table: it's called a lateral join.

This is part of the SQL standard, but you need Oracle 12 in order to be able to use it:

The following is legal:

SELECT pr.province_name,co.country_name
FROM province pr
    JOIN LATERAL (
      SELECT country_id, country_name 
      FROM country 
      WHERE country_id = pr.country_id
    ) co ON co.country_id = pr.country_id
WHERE pr.province_id IN (1,2)