使用table1.* <> table2.*查询的执行逻辑及多数据库行为探究

PostgreSQL中的执行逻辑 #

• 当使用table1.* <> table2.*时，PostgreSQL会将两张表的行当作行类型进行整体比较
• 具体逻辑是按列的定义顺序逐列对比：只要任意一列的值不相等（且不是两边都为NULL），整个行的比较结果就为true
• 特殊情况处理：如果某列在两张表中都是NULL，PostgreSQL会判定这两个NULL相等；但如果一边是NULL另一边非NULL，则判定为不相等
• 实际例子：假设表有id和name两列，table1的行是(1, 'Alice')，table2的行是(1, 'Bob')，这个比较会返回true；如果table1行是(2, NULL)，table2行是(2, NULL)，则返回false；如果table1是(3, NULL)，table2是(3, 'Charlie')，则返回true

其他主流数据库的处理行为 #

MySQL/MariaDB #

• 不支持这种直接的行级比较语法，执行table1.* <> table2.*会直接报语法错误
• 要实现相同的行差异判断，必须显式列出所有列做对比，比如写table1.col1 <> table2.col1 OR table1.col2 <> table2.col2 OR ...

Oracle #

• 同样不支持table1.* <> table2.*的写法，执行时会触发语法错误
• 替代方案可以用显式列对比，或者将行转换为自定义对象类型后再比较，但最常用的还是逐列列出对比

SQL Server #

• 不支持该语法，执行时会报错
• 要找出两张表的差异行，更推荐用EXCEPT运算符，比如：

  SELECT * FROM table1
  EXCEPT
  SELECT * FROM table2;
  

  这个语句会返回table1中存在、但table2中无完全匹配行的记录（基于所有列的完全匹配）

SQLite #

• 支持行级比较，但需要用row(table1.*) <> row(table2.*)的语法
• 比较逻辑和PostgreSQL一致：逐列对比，NULL与NULL视为相等，只要有一列值不相等则行判定为不相等

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