SQL查询以从一个表中获取其他3个表中不存在的行 [英] SQL query to fetch rows from one table which are not present in other 3 tables

问题描述

我有4个表，

A，B，C，D

A有列Aid，
B具有列Aid，
C具有列Aid，
D具有列Aid

A has column Aid, B has column Aid, C has column Aid, D has column Aid

（Aid是所有4列中的公共列表）

(Aid is common column in all the 4 tables)

现在我想从A表中获取在B或C或D中不存在的行
任何人都可以让我知道SQL查询

now i want to fetch rows from A table which are not present in B or C or D can anyone please let me know the SQL query for this.

推荐答案

您可以使用 MINUS 运算符：

SELECT Aid FROM A
MINUS
SELECT Aid FROM B
MINUS
SELECT Aid FROM C
MINUS
SELECT Aid FROM D

示例

CREATE TABLE A ( Aid, Value ) AS
  SELECT 1, '1' FROM DUAL UNION ALL
  SELECT 1, '2' FROM DUAL UNION ALL
  SELECT 2, '3' FROM DUAL UNION ALL
  SELECT NULL, '4' FROM DUAL UNION ALL
  SELECT 3, '5' FROM DUAL;

CREATE TABLE B ( Aid, BValue ) AS
  SELECT 1, '3' FROM DUAL;

CREATE TABLE C ( Aid, CValue ) AS
  SELECT 4, '7' FROM DUAL;

CREATE TABLE D ( Aid, DValue ) AS
  SELECT NULL, '9' FROM DUAL;

查询：

SELECT Aid FROM A
MINUS
SELECT Aid FROM B
MINUS
SELECT Aid FROM C
MINUS
SELECT Aid FROM D

输出：

AID
---
  2
  3

更新以获取 A 的所有列：

Update to get all columns of A:

这将排除 Aid 等于或两个值均 NULL 的行：

This will exclude rows where the Aid is equal or when both are NULL:

SELECT *
FROM   A
WHERE  NOT EXISTS(SELECT 1 FROM B WHERE A.Aid = B.Aid OR (A.Aid IS NULL AND B.Aid IS NULL))
AND    NOT EXISTS(SELECT 1 FROM C WHERE A.Aid = C.Aid OR (A.Aid IS NULL AND C.Aid IS NULL))
AND    NOT EXISTS(SELECT 1 FROM D WHERE A.Aid = D.Aid OR (A.Aid IS NULL AND D.Aid IS NULL));

这篇关于SQL查询以从一个表中获取其他3个表中不存在的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！