sql 交集 差集

2019独角兽企业重金招聘Python工程师标准>>>

-- 使用not exists 获取差集
SELECT  s.id FROM  TABLES s WHERE
    NOT EXISTS (
        SELECT 1 FROM  tableM m  WHERE  m.tableS_id = s.id
    )
ORDER BY  s.id ASC;

-- 使用left join获取差集
SELECT s.id FROM TABLES s
LEFT JOIN tableM m ON m.tableS_id = s.id
WHERE m.tableS_id IS NULL
ORDER BY s.id ASC;

-- 使用union all获取差集
SELECT c.id FROM (
        SELECT s.id AS id FROM TABLES s
        UNION ALL (
                SELECT m.tableS_id AS id FROM tableM m
            )

) c
GROUP BY c.id HAVING COUNT(c.id) = 1;

-- 使用 exists 获取交集
SELECT s.id FROM TABLES s
WHERE EXISTS (
        SELECT 1 FROM tableM m WHERE m.tableS_id = s.id
    )
ORDER BY s.id ASC;

-- 使用left join 获取交集
SELECT DISTINCT s.id FROM TABLES s
LEFT JOIN tableM m ON m.tableS_id = s.id
WHERE m.tableS_id IS NOT NULL
ORDER BY s.id ASC;

-- 使用inner join 获取交集
SELECT DISTINCT s.id FROM TABLES s
INNER JOIN tableM m ON m.tableS_id = s.id
ORDER BY s.id ASC;

-- 使用union all获取交集
SELECT c.id FROM (
        SELECT s.id AS id FROM TABLES s
        UNION ALL  (
                SELECT m.tableS_id AS id FROM tableM m
            )
    ) c
GROUP BY c.id HAVING COUNT(c.id) > 1

 

-- 如果是oracle数据库，可使用minus和intersect函数分别获取差集和并集，示例如下：

-- 使用minus获取差集
SELECT s.id FROM TABLES s
minus
SELECT  m.tableS_id FROM tableM m
ORDER BY s.id ASC;

-- intersect获取交集
SELECT s.id FROM TABLES s
intersect
SELECT m.tableS_id FROM tableM m
ORDER BY s.id ASC;

转载于:https://my.oschina.net/kevin2kelly/blog/888116