ORACLE: return value or null-CodePudding

I have a user table

USER_ID	FIRSTNAME	LASTNAME
1000	Tom	Doe
2000	Tina	Doe
3000	Michael	Doe
4000	Robert	Doe

and a table with values

USER_ID	VALUE
1000	10
2000	20
3000	40
4000	20
1000	20
3000	10
4000	30

Now I would like to write an SQL-statement that lists all users with the value 10 and if the value is not 10 or there is none in the table, it should return a null.

USER_ID	FIRSTNAME	LASTNAME	VALUE
1000	Tom	Doe	10
2000	Tina	Doe	null
3000	Michael	Doe	10
4000	Robert	Doe	null

How can I realize this?

CodePudding user response：

Use a LEFT JOIN where you restrict the value from your value table to 10. This will automatically give you NULL back if there is no value 10:

SELECT 
  USERS.*, 
  USER_VALUES.VALUE
FROM USERS
LEFT JOIN USER_VALUES
ON USERS.USER_ID = USER_VALUES.USER_ID
AND USER_VALUES.VALUE = 10
ORDER BY USERS.USER_ID

I added a dbfiddle

CodePudding user response：

That's outer join:

Sample data:

SQL> with
  2  tuser (user_id, firstname) as
  3    (select 1000, 'Tom'    from dual union all
  4     select 2000, 'Tina'   from dual union all
  5     select 3000, 'Michael'from dual union all
  6     select 4000, 'Robert' from dual
  7    ),
  8  tvalues (user_id, value) as
  9    (select 1000, 10 from dual union all
 10     select 2000, 20 from dual union all
 11     select 3000, 40 from dual union all
 12     select 4000, 20 from dual union all
 13     select 1000, 20 from dual union all
 14     select 3000, 10 from dual union all
 15     select 4000, 30 from dual
 16    )

Query:

 17  select a.user_id, a.firstname, b.value
 18  from tuser a left join tvalues b on b.user_id = a.user_id and b.value = 10
 19  order by a.user_id;

   USER_ID FIRSTNA      VALUE
---------- ------- ----------
      1000 Tom             10
      2000 Tina
      3000 Michael         10
      4000 Robert

SQL>

CodePudding user response：

You simply need a subquery and a LEFT join -

SELECT U.USER_ID, U.FIRSTNAME, U.LASTNAME, V.VALUE
  FROM users U
  LEFT JOIN (SELECT USER_ID, VALUE
               FROM values
              WHERE values = 10) V ON U.USER_ID = V.USER_ID