How to return an value/null no matter the column value is exist or not

TABLES

<<USER>> id name ------------ 1 alex 2 gary 3 peter 4 mary <<ITEMS>> id index items ----------------- 1 1 iphone 1 2 apple 2 1 nokia

SQL

SELECT u.id, u.name, i.items FROM USER u, ITEMS i WHERE u.id = item.id(+) AND (item.index = 2 OR item.index IS NULL);

Actual result

id name item ----------------------- 1 alex apple 3 peter <null> 4 mary <null>

But my expected result is....

id name item ----------------------- 1 alex apple 2 gary <null> 3 peter <null> 4 mary <null>

I really don't know how to handle this part when index 2 is not found in id 2, I think it is the mainly root cause of returning this result without row of "gary".

-------------Problems Reply------------

Do a standard LEFT JOIN instead:

SELECT u.id, u.name, i.items
FROM USER u
LEFT JOIN ITEMS i ON u.id = i.id
AND i.index = 2 OR i.index IS NULL;

Note that the item's conditions are in the ON clause - to get a true outer join! (If they are in WHERE, it would execute as a regular inner join.)

Hmm, perhaps you can remove OR i.index IS NULL part...

SELECT us.id
,us.name
,it.items
FROM USER US
LEFT JOIN ITEMS IT ON US.ID = IT.ID
AND (IT.index = 2 OR IT.index IS NULL);

SQL Fiddle

Category:sql Views:6 Time:2018-12-27
Tags: sql oracle

Related post

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.216 (s). 11 q(s)