How to use subquery in outer join condition

Question

My database have customers and orders. Customer may have many orders with different primary keys and creation dates.

I want to make a view for customers, which consist of each customer joined with their latest order (or null if customer doesn't have any order).

I tried the following

SELECT [...], c.customer_id, o.order_id
FROM customers c
LEFT OUTER JOIN [...]
LEFT OUTER JOIN [...]
[...]
LEFT OUTER JOIN orders o ON (
    o.customer_id = c.customer_id
    AND o.create_dt = (
        SELECT MAX(create_dt) FROM orders o2 WHERE o2.customer_id = c.customer_id
    )
);

But I get the the following error

a column may not be outer-joined to a subquery

I figured out Oracle doesn't support subquery in outer join. What is the correct way to implement this view? Please note this example is simplified, and necessary changes to the statement should only affect the specific join, as there are multiple other joins and conditions going on, which are not shown here for simplicity.

Tuomas Toivonen · Accepted Answer · 2018-01-16T09:20:56.800

I used the following solution which I figured out myself

SELECT [...],
c.customer_id,
(select o.order_id from orders where o.customer_id = c.customer_id
    AND o.create_dt = (
        SELECT MAX(create_dt) FROM orders o2 WHERE o2.customer_id = 
          c.customer_id))
as order_id
FROM customers c
LEFT OUTER JOIN [...]
LEFT OUTER JOIN [...]

Basically this achieves the same results I tried to to achieve with join. Why I can't use the subquery in the join, but in the column I can? What are generally pros and cons using join versus subquery for the column performance wise and otherwise? In which scenarios should I use one or another?

I think the join I tried (which didn't work) is more declaratively expressing what I'm trying to achieve, and it's a shame that Oracle doesn't support the subquery there. What is the reason for this restriction?

score 0 · Answer 2 · answered Jan 12 '18 at 09:59

0

You can explore the outer apply or cross apply to achieve your objective. Snippets might be as below

select d.*, e.*
from   customers c
outer apply (
    select [...], o.order_id
    from   orders o
    where  o.customer_id= c.customer_id
    order  by create_dt desc
    fetch first 1 rows only 
) e

For more information you may visit this link

answered Jan 12 '18 at 09:59

SuicideSheep

5,260
19
64
117

Although Oracle supports the non-standard `outer apply` it would be better to use the SQL standard's keywords: `left join lateral` or `cross join lateral` instead (the `apply` keyword was introduced by Microsoft ignoring the SQL standard) – Jan 12 '18 at 10:02
I would like to use more common operators to achieve this, to keep in line with the existing implementation. Also, performance is a crucial concern. – Tuomas Toivonen Jan 12 '18 at 10:03
Even the `LATERAL` sounds too obscure. Isn't there really more standard way to do this, which also works on older Oracle implementations? – Tuomas Toivonen Jan 12 '18 at 10:07

score 0 · Answer 3 · answered Jan 14 '18 at 21:40

I would use the GROUP BY combined with MAX

SELECT c.customer_id, o.order_id
FROM customers c
LEFT OUTER JOIN orders o ON o.customer_id = c.customer_id
LEFT OUTER JOIN (
    SELECT customer_id, MAX(create_dt) max_create
    FROM orders 
    GROUP BY customer_id
) t ON t.customer_id = o.customer_id AND
       t.max_create = o.create_dt

How to use subquery in outer join condition

3 Answers3