Oracle Query Join issue

Question

Hello,

I have an Oracle table as shown below

Orders
---------
ORDERID
DESCRIPTION
TOTALVALUE
ORDERSTATUS

I have the below mentioned query

select ORDERID,ORDERSTATUS
FROM ORDERS
WHERE ORDERID IN( 1000,1001,1002,1003,1004,1005,1006,1007,1008,1009,1010,1020,
                  1030,104,1040,1090,789)

Some orderIDs mentioned above are not in orders table. In spite of that I want the orderIDs to appear in the resultset with status as null.

Appreciate your help.

Answer 1

You can use a CTE as table for the orderIds (or store them into a temporary table), and outer join your Orders:

With tmp As (
  Select 1000 As orderId From dual
  Union All
  Select 1001 From dual
  Union All
  ...
)
Select tmp.orderId, o.orderStatus
From tmp
Left Join orders o On ( o.orderId = tmp.orderId )

orderStatus is NULL, when no order is found.

Answer 2

You would have to do an outer join to accomplish something like this :

SELECT ORDERID, ORDERSTATUS
FROM (
SELECT 1000 AS ORDERID FROM dual UNION SELECT 1001 FROM dual -- etc
) tmpOrderid
LEFT OUTER JOIN ORDERS O
    ON tmpOrderid.ORDERID = O.ORDERID;

I have never used Oracle, but there is most likely a function that can generate numbers (for exemple, generate_series(1000, 1010) in PostgreSQL).

Answer 3

there is one more trick in oracle.

SELECT LEVEL + 1000 dt FROM DUAL CONNECT BY LEVEL < (2000 - 1000)

it generates a recordset with 1000 rows which might be left joined with your table.

Answer 4

What about this:

SELECT T.COLUMN_VALUE AS ORDERID, ORD.ORDERSTATUS
FROM TABLE(SYS.ODCINUMBERLIST(
        1000,1001,1002,1003,1004,1005,1006,1007,1008,1009,1010,1020,1030,104,1040,1090,789
    )) T
LEFT JOIN ORDERS ORD ON ORD.ORDERID = T.COLUMN_VALUE;

You can also get it to work if the order IDs aren't fixed but a parameter. But the solution depends on whether you call the statement from PL/SQL or from another programming language such as C#, Java, PHP etc.

Update: SYS.ODCINUMBERLIST is just a table type that's already defined in Oracle. You could use your own type:

CREATE TYPE NUMBER_TABLE_T AS TABLE OF NUMBER;