How can my where clause return results for unmatched values?-CodePudding

I have a table which has certain IDs, and values against them. If I pass a list of IDs in the where clause it will return only matching IDs, and discard the rest. How can I get the unmatched IDs also with a null value against them.

Table: select * from members;

Result:

|member_id   |price   |
 -------------- -------
|   0        |1234    |
|   1        |99      |
 -------------- -------

However if we run this query, it won't return the unmatched values. select member_id,price from members where member_id in (0,1,2,3)

How can I show IDs 2&3 as well, but with a null against them for price column?

Thanks!

CodePudding user response：

The values can't appear exclusively in the where clause; they must appear somehow in the from clause. You must create a view of some sort including those values. A common way is to use an Oracle-provided collection type and select from it, something like this:

select t.column_value as member_id, m.price
from   sys.odcinumberlist(0, 1, 2, 3) t left outer join members m
       on t.column_value = m.member_id
;

 MEMBER_ID      PRICE
---------- ----------
         0       1234
         1         99
         2           
         3

odcinumberlist is a collection data type (varray of number) defined in the sys package, provided by Oracle. When used in the from clause of a select statement, the values are in a column named column_value.

Whether this will work in "mariadb" (whatever that is), you will have to try and see. If it does not, you can try something more traditional, like

select t.member_id, m.price
from   (
         select 0 as member_id from dual union all
         select 1              from dual union all
         select 2              from dual union all
         select 3              from dual
       ) t
       left outer join members m on t.member_id = m.member_id
;

CodePudding user response：

You can use a LEFT JOIN for this:

SELECT m.member_id, m.price

FROM members m

LEFT JOIN members m2 ON m2.member_id = m.member_id

AND m2.member_id IN (0,1,2,3)

WHERE m2.member_id IS NULL