SQL集合运算 差集 并集 交

时间:2024-01-13 12:49:14

SQL-3标准中提供了三种对检索结果进行集合运算的命令:并集UNION;交集INTERSECT;差集EXCEPT(在Oracle中叫做 MINUS)。在有些数据库中对此的支持不够充分,如MySql中只有UNION,没有其他两种。实际上这些运算都可以通过普通的SQL来实现,虽然有时有些繁琐。

假设有两个表(或视图)s,t,s中有两个字段sa,sb;t中有两个字段ta,tb;

差集EXCEPT:

PLAIN TEXT
SQL:
  1. SELECTsaFROMs
  2. EXCEPT
  3. SELECTtaFROMt;

可以写作

PLAIN TEXT
SQL:
  1. SELECTsaFROMs
  2. WHEREsaNOTIN
  3.    (SELECTtaFROMt)

上面的例子中忽略了对s和t单独的条件,这些总可以加入AND条件完成,或者使用视图。如果是多个字段比较麻烦,如:

PLAIN TEXT
SQL:
  1. SELECTsa, sbFROMs
  2. EXCEPT
  3. SELECTta, tbFROMt;

需要写成

PLAIN TEXT
SQL:
  1. SELECTsa, sbFROMs
  2. WHERE(sa, sb)NOTIN
  3.    (SELECTta, tbFROMt)

上面使用的语法不见得数据库都支持。好在不支持EXCEPT的MySQL支持这种语法,而不支持这种语法的MSSQL又支持EXCEPT。

注意对于这样的row constructors(Mysql术语),是和下面写法(以及其他类似写法)不等价的。

PLAIN TEXT
SQL:
  1. SELECTsa, sbFROMs
  2. WHEREsaNOTIN
  3.    (SELECTtaFROMt)
  4. ANDsbNOTIN
  5.    (SELECTtbFROMt)

在MSSQL中的一个解决技巧是,把这两个字段(假设字符类型)拼起来,即

PLAIN TEXT
SQL:
  1. SELECTsa, sbFROMs
  2. WHEREsa+sbNOTIN
  3.    (SELECTta+tbFROMt)

交集INTERSECT:

PLAIN TEXT
SQL:
  1. SELECTsaFROMs
  2. INTERSECT
  3. SELECTtaFROMt;

可以写成

PLAIN TEXT
SQL:
  1. SELECTsaFROMs
  2. WHEREsa IN
  3.    (SELECTtaFROMt)

当然也可以写成

PLAIN TEXT
SQL:
  1. SELECTsaFROMs
  2. WHEREEXISTS
  3.    (SELECT*FROMtWHEREt.ta=s.sa)

或者使用连接

PLAIN TEXT
SQL:
  1. SELECTsaFROMs, t
  2. WHEREsa = ta

实际上这几个语句都有点问题,就是INTERSECT在出现重复时的语义问题。按照SQL-3标准,类似UNION,可以有明确的
INTERSECT ALL或者INTERSECT
DISTINCT语法。一般的INTERSECT实现并没有明确这一点,而且从逻辑上讲意义也不大。那么当s或t中出现重复的时,如sa='x'的有2
个,sb='x'的有3个,使用上面的子查询将返回2行,使用连接将返回6行,当然这两个语句都可以加上一个DISTINCT,就实现了
INTERSECT DISTINCT语义了。

并集UNION:

MySql从4.0开始就支持UNION(ALL 和 DISTINCT)了,为完整起见,也列举一下。
其实实现这样一个结果是很麻烦的

PLAIN TEXT
SQL:
  1. SELECTsaFROMs
  2. UNIONDISTINCT
  3. SELECTtaFROMt;

需要使用外连接,而且是Full的外连接

PLAIN TEXT
SQL:
  1. SELECTDISTINCTNVL(s.sa, t.ta)
  2. FROMs FULLOUTERJOINtON(s.sa=t.ta)

上面的例子中我使用了Oracle的语法,实际上MySql不支持FULL OUTER JOIN(虽然支持LEFT和RIGHT OUTER JOIN),好在MySql支持UNION。

对于UNION ALL语义,我还没有想出来用普通查询如何实现,如果在上面语句中去掉DISTINCT,结果肯定不对。