CODEWITHSUNDEEP
sqloracle-databasejoingreatest-n-per-group
Tuomas Toivonen
Tuomas Toivonen

Reputation: 23492

How to use subquery in outer join condition

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.

Upvotes: 1

Views: 663

Answers (3)

Tuomas Toivonen
Tuomas Toivonen

Reputation: 23492

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?

Upvotes: 1

Radim Bača
Radim Bača

Reputation: 10701

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

Upvotes: 0

SuicideSheep
SuicideSheep

Reputation: 5550

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

Upvotes: 0

Related Questions