Oracle内连接空值处理：排除空值与仅显示含空值记录

问题描述 #

• 问题1：如何在Oracle内连接结果中排除空值，仅显示所有属性均不为空的行
• 问题2：如何仅显示四张表中任意属性存在空值/缺失的记录

示例表结构与测试数据 #

创建表 #

create table name
(
id number,
firstname varchar2(20),
lastname varchar2(20)
);

create table details
(
id number,
dob_month varchar2(20),
dob_day varchar2(20),
dob_year varchar2(20)
);

create table social
(
id number,
ssn varchar2(20),
telephone number
);

create table address
(
id number,
address varchar2(20)
);

插入测试数据 #

insert into name values (1, 'Will' , 'Smith');
insert into name values (2, 'Barry' , 'White');
insert into name values (3, 'Tom' , 'Jones');
insert into name values (4, 'Rod' , 'Stewart');
insert into name values (5, 'Elvis' , 'Presley');

insert into details values (1,'May',31,null);
insert into details values (2,'August',22,1980);
insert into details values (3,'October',null,1973);
insert into details values (4,'January',30,1980);
insert into details values (5,'March',11,1980);

insert into social values (1,'123-45-6789',null);
insert into social values (2,'222-45-5555',789456123);
insert into social values (3,'333-45-7777',888888888);
insert into social values (4,null,693456741);
insert into social values (5,'999-45-1111',null);

insert into address values (null, null);
insert into address values (2, '12th street');
insert into address values (null, null);
insert into address values (4, '14th Avenue');
insert into address values (5, null);

问题1 解决方案 #

你之前的查询仅完成了表的内连接，没有添加空值过滤条件。要筛选出所有属性均不为空的行，需要在WHERE子句中明确指定所有需要检查的列都不为空：

SELECT 
    name.id, firstname, lastname,
    dob_month, dob_day, dob_year,
    ssn, telephone, address.address
FROM name
JOIN details ON name.id = details.id
JOIN social ON details.id = social.id
JOIN address ON social.id = address.id
WHERE 
    -- name表属性非空
    name.id IS NOT NULL
    AND firstname IS NOT NULL
    AND lastname IS NOT NULL
    -- details表属性非空
    AND details.dob_month IS NOT NULL
    AND details.dob_day IS NOT NULL
    AND details.dob_year IS NOT NULL
    -- social表属性非空
    AND social.ssn IS NOT NULL
    AND social.telephone IS NOT NULL
    -- address表属性非空
    AND address.id IS NOT NULL
    AND address.address IS NOT NULL;

执行此查询后，只会返回所有属性都无空值的记录（你的测试数据中仅id=2的记录符合条件）。

问题2 解决方案 #

要筛选出任意属性存在空值的记录，我们可以使用左连接保留所有关联的记录，然后在WHERE子句中判断任意一列是否为空：

SELECT 
    name.id AS name_id, firstname, lastname,
    details.id AS details_id, dob_month, dob_day, dob_year,
    social.id AS social_id, ssn, telephone,
    address.id AS address_id, address
FROM name
LEFT JOIN details ON name.id = details.id
LEFT JOIN social ON name.id = social.id
LEFT JOIN address ON name.id = address.id
WHERE 
    -- 任意一列为空即满足条件
    name.id IS NULL
    OR firstname IS NULL
    OR lastname IS NULL
    OR details.dob_month IS NULL
    OR details.dob_day IS NULL
    OR details.dob_year IS NULL
    OR social.ssn IS NULL
    OR social.telephone IS NULL
    OR address.id IS NULL
    OR address.address IS NULL;

如果只需要内连接后的记录中存在空值的行，只需将LEFT JOIN替换为JOIN即可。

内容的提问来源于stack exchange，提问作者Advait Advait