我有两个表：
人员表：
---------------------
|Person_ID | Phone_ID |
|---------------------|
|1234 | 12 |
|345 | 10 |
|43 | 33 |
|55 | 27 |
-----------------------
电话表：
--------------------------
|Phone_ID | Phone_Number |
|--------------------------|
| 12 | null |
| 10 | 9876 |
| 33 | 9654 |
| 27 | null |
--------------------------
当我执行查询时：
select t1.person_id, t2.phone_id
from person t1
left join
phone t2
on t1.phone_id=t2.phone_id;
我得到的结果：
--------------------------
| Person_ID | Phone_number |
|--------------------------|
| 1234 | null |
| 345 | 9876 |
| 345 | null |
| 43 | 9654 |
| 43 | null |
| 55 | null |
--------------------------
如何消除为已经有电话号码的人员id获取null值的问题？
your query do not return that results.
很可能您的手机和人员桌子与您预期的不一样。
您应该截断这些表并重新插入数据。然后重新执行查询。
truncate table Person ;
truncate table Phone ;
insert into person
select 1234 ,12 from dual union all
select 345, 10 from dual union all
select 43 , 33 from dual union all
select 55 , 27 from dual ;
commit;
insert into phone
select 12 , null from dual union all
select 10 , 9876 from dual union all
select 33 , 9654 from dual union all
select 27 ,null from dual ;
commit;
select t1.person_id, t2.phone_id
from person t1
left join phone t2
on t1.phone_id=t2.phone_id;
您的查询运行良好，正如我检查过的动态创建表一样，请尝试此操作，直接在您的查询窗口中运行此操作：
DECLARE @persons TABLE (
person_id INT,
phone_id INT )
DECLARE @phones TABLE (
phone_id INT,
phonenumber BIGINT )
INSERT INTO @persons VALUES(1234,12),(345,10),(43,33),(55,27)
INSERT INTO @phones VALUES(12,null),(10,9876),(33,9654),(27,null)
SELECT * FROM @persons
SELECT * FROM @phones
select t1.person_id, t2.phone_id
from @persons t1
left join
@phones t2
on t1.phone_id=t2.phone_id;
如果您需要消除包含null的条目，您可以只使用最后一个中的条件：
WHERE 'column_name' IS NOT NULL
或者，如果你想用空白或某些值替换null，你可以使用：
ISNULL(column_name,'replacement_value') AS new_col_name
WITH actual_phones AS
( SELECT phone_id, phone_number
FROM phone
WHERE phone_number IS NOT NULL )
SELECT person_id, phone_number
FROM person NATURAL JOIN actual_phones
UNION
SELECT person_id, null AS phone_number
FROM person
WHERE phone_id NOT IN ( SELECT phone_id
FROM actual_phones );