ORACLE:返回值或null

Question

我有一个用户表

tbody> <<tr>

USER_ID	FIRSTNAME	LASTNAME
1000	汤姆	和
2000	蒂娜	能源部
3000	迈克尔	和
4000	罗伯特。	和

使用LEFT JOIN，将值表中的值限制为10。如果没有值，这将自动返回NULL:

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

我添加了一个dbfiddle

这是外部连接:

样本数据:

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    )

查询:

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>

您只需要一个子查询和一个左连接-

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