CODEWITHSUNDEEP
oracle-databasesubquery
fernando1979
fernando1979

Reputation: 1947

coalesce with subquery in a where clause in Oracle

Hy,

I have the next sql query in Oracle:

Imagine I have a table "items" with "id" and "name" fields and other table "prices_items" which have three fields named "id, itemId, category". The category may have three values: "1,2,3". So the query I need to do is get the price of an item from the table "prices_items" but the item can have until three prices because of the category field. So, in priotiry order I need to get the price of an item which has category 1, if the item doesnt have this category I have to find the price for category 2 and so on.

from items 
left join prices_items on prices_items.itemId = items.itemId 
where prices_items.id = coalesce(select id 
                                 from prices_items 
                                 where itemId= items.itemId and category=1,
                                 select id 
                                 from prices_items 
                                 where itemId= items.itemId and category=2, 
                                 select id 
                                 from prices_items 
                                 where itemId= items.itemId and category=3)

The query I am using is like this but I dont know how its working because coalesce is being executed on each join?. How is this being executed?

Thanks

Upvotes: 0

Views: 4146

Answers (2)

shawnt00
shawnt00

Reputation: 17915

The coalesce() is going to keep the first prices_items.id found in order of the categories listed. Instead of individual subqueries you could write it this way and it will probably give a better plan.

select ...
from items inner join prices_items on prices_items.itemId = items.itemId 
where prices_items.category = (
    select min(pi2.category) from prices_items pi2
    where pi2.itemId = items.itemId
);

If the priority of categories doesn't happen to follow an ascending sequence you could handle it with a case expression:

select ...
from items inner join prices_items on prices_items.itemId = items.itemId 
where
    case prices_items.category
        when 2 then 1
        when 3 then 2
        when 1 then 3
    end = (
        select
            min(case pi2.category
                when 2 then 1
                when 3 then 2
                when 1 then 3
            end)
        from prices_items pi2
        where pi2.itemId = items.itemId
    );

As far as how your current query is actually running it may or may not be materializing all the subquery results. From an end results perspective all you really need to know is that only the first non-null value from the coalesce() arguments is the one kept. The reality is that it is probably more efficient to re-write the query so you don't need them.

There are other ways to write this. The one that's most common these days seems to be the row_number() approach:

with data as (
    select *,
        row_number() over (partition by pi.itemId order by pi.category) as rn
    from items inner join prices_items pi on pi.itemId = items.itemId
)
select ...
from data
where rn = 1;

Here's another Oracle-specific solution:

select *
from
    items inner join
    (
        select itemId, min(price) keep (dense_rank first order by category) as price
        from prices_items
        group by itemId
    ) pi on pi.itemId = items.itemId;

Upvotes: 1

MT0
MT0

Reputation: 167991

Oracle Setup:

CREATE TABLE items (
  itemid NUMBER PRIMARY KEY,
  name VARCHAR2(20)
);

CREATE TABLE prices_items (
  itemId NUMBER REFERENCES items ( itemid ),
  category INT,
  price NUMBER,
  CHECK ( category IN ( 1, 2, 3 ) ),
  PRIMARY KEY ( itemid, category )
);

INSERT INTO items
SELECT 1, 'A' FROM DUAL UNION ALL
SELECT 2, 'B' FROM DUAL UNION ALL
SELECT 3, 'C' FROM DUAL;

INSERT INTO prices_items
SELECT 1, 1, 32.5 FROM DUAL UNION ALL
SELECT 1, 2, 23.9 FROM DUAL UNION ALL
SELECT 1, 3, 19.99 FROM DUAL UNION ALL
SELECT 2, 1, 42.42 FROM DUAL UNION ALL
SELECT 2, 3, 99.99 FROM DUAL UNION ALL
SELECT 3, 2, 0.02 FROM DUAL UNION ALL
SELECT 3, 3, 10 FROM DUAL;

Query:

SELECT i.itemid,
       name,
       category,
       price
FROM   items i
       INNER JOIN
       ( SELECT itemid,
                MIN( category ) AS category,
                MAX( price ) KEEP ( DENSE_RANK FIRST ORDER BY category ) AS price
         FROM   prices_items
         GROUP BY itemid
       ) p
       ON ( i.itemid = p.itemid );

Output:

ID NAME CATEGORY PRICE
-- ---- -------- -----
 1 A    1        32.50
 2 B    1        42.42
 3 C    2         0.02

Upvotes: 1

Related Questions