CODEWITHSUNDEEP
sqloracle-databaseselectunion
J R
J R

Reputation: 223

How could I optimize this dynamic SQL query?

I've been having difficulties with the following query. I've been trying to optimize it and perhaps make it more readable. Let's say I have 3 tables orders_returned, orders_completed, orders_delivered with matching columns oder_id, customer_id. Depending on selected options, I might need to retrieve orders which were delivered, then returned and finally completed (same order_id occurs in all three tables) which have the same customer_id. Also I might only need to retrieve only delivered and returned orders in which case I would omit AND order_id IN (SELECT order_id FROM ORDERS_COMPLETED) from the WHERE clause. For example, Get delivered and returned orders by customers John and Tim As of now my query looks like this:

SELECT order_id
FROM
(
    SELECT order_id, customer_id
    FROM ORDERS_RETURNED
    UNION
    SELECT order_id, customer_id
    FROM ORDERS_COMPLETED
    UNION
    SELECT order_id, customer_id
    FROM ORDERS_DELIVERED
)
WHERE 
    customer_id IN ('customer1', 'customer2', ...)
    AND order_id IN (SELECT order_id FROM ORDERS_RETURNED)
    AND order_id IN (SELECT order_id FROM ORDERS_COMPLETED)
    AND order_id IN (SELECT order_id FROM ORDERS_DELIVERED)

I'm still learning SQL and would like to see if there are better options.

EDIT: I am using Oracle database. There is also Orders table which has distinct order_ids and some other irrelevant columns. It does not store customer_ids. Also, the order might occur in one table or in two of them only, so joins, I think, are of no use here.

Upvotes: 0

Views: 264

Answers (3)

DazzaL
DazzaL

Reputation: 21973

you could do this with something like:

with data
     as (select customer_id,
                order_id,
                nvl(max(case status when 'RETURNED' then 'Y' end), 'N') returned,
                nvl(max(case status when 'COMPLETED' then 'Y' end), 'N') completed,
                nvl(max(case status when 'DELIVERED' then 'Y' end), 'N') delivered
           from (select 'RETURNED' status, order_id, customer_id
                   from orders_returned
                 union all
                 select 'COMPLETED' status, order_id, customer_id
                   from orders_completed
                 union all
                 select 'DELIVERED' status, order_id, customer_id
                   from orders_delivered)
          group by customer_id, order_id)
select *
  from data
 where returned = 'Y'
   and delivered = 'Y'
   and customer_id in ('xx', 'xxx') ;

or

with data
     as (select customer_id,
                order_id,
                max(returned) returned,
                max(completed) completed,
                max(delivered) delivered
           from (select 'Y' returned, null completed, null delivered, order_id, customer_id
                   from orders_returned
                 union all
                 select null, 'Y', null, order_id, customer_id
                   from orders_completed
                 union all
                 select null, null, 'Y', order_id, customer_id
                   from orders_delivered)
          group by customer_id, order_id)
select *

from data where returned = 'Y' and delivered = 'Y' and customer_id in ('xx', 'xxx');

eg: http://sqlfiddle.com/#!4/3e2fb/2

Upvotes: 0

sgeddes
sgeddes

Reputation: 62841

Since you have an Order table, I presume you are also storing the CustomerId in that table as well. Assuming so, try this:

SELECT DISTINCT O.OrderId
FROM Orders O
   LEFT JOIN Orders_Completed OC ON O.OrderId = OC.OrderId
   LEFT JOIN Orders_Delivered OD ON O.OrderId = OD.OrderId
   LEFT JOIN Orders_Returned ORE ON O.OrderId = ORE.OrderId
WHERE O.CustomerId IN (...)
   AND OD.OrderId IS NOT NULL AND ORE.OrderId IS NOT NULL AND OC.OrderId IS NULL

This particular query will return you all distinct orders where customer in (...) where the order has been delivered and returned, but not completed. Toggle the use of the IS NULL and IS NOT NULL to get your desired output.

Good luck.

Upvotes: 2

Kundan Singh Chouhan
Kundan Singh Chouhan

Reputation: 14292

You should use Joins instead of Inner/Nested queries.

Try below instead ::

SELECT A.order_id, A.customer_id FROM ORDERS_RETURNED A 
INNER JOIN ORDERS_COMPLETED B ON A.order_id = B.order_id AND A.customer_id = B.customer_id
INNER JOIN ORDERS_DELIVERED C ON A.order_id = C.order_id AND A.customer_id = C.customer_id
Where A.customer_id IN ('customer1', 'customer2', ...)

Upvotes: 0

Related Questions