如何在一个查询中获取两个表中所有不匹配的行?

huangapple go评论52阅读模式
英文:

How to get all non matching rows from both tables in one query?

问题

select m., t.
from test.test1 m
full outer join test.test2 t
on row(m) = row(t)

where m.date = '2022-11-01'

英文:

I have two tables with similar columns and I would like to know the difference between these tables. So if all values (column-wise) of the row exists in both table it is fine (I do not want to see this), while I want to see all rows that.

I have tried this:

select m.*, t.*
from  test.test1 m 
full outer join test.test2 t 
    on row(m) = row(t)

where m.date = '2022-11-01' 

but I am getting all rows only from the first table. Note. I want only one query (no subqueries)

答案1

得分: 0

需要在where语句中为您的关键列添加空值检查:

select m.*, t.*
from test.test1 m
full outer join test.test2 t
on row(m) = row(t)
where m.KEY is null or t.KEY is null and m.date = '2022-11-01';
英文:

You need to add the null check for your key columns in the where statement:

select m.*, t.*
from  test.test1 m 
full outer join test.test2 t 
    on row(m) = row(t)
where m.KEY is null or t.KEY is null and m.date = '2022-11-01'

答案2

得分: 0

可以使用 EXCEPT/EXCEPT ALL 集合运算符来比较具有相同列布局(数据类型和列顺序(如果使用 SELECT *)必须匹配)的表。
SELECT ''在TEST1中但不在TEST2中'' as SRC, EA.*
FROM (
  SELECT *
  FROM test.test1 m
  where m.date='2022-11-01'
  EXCEPT ALL
  SELECT *
  FROM test.test2
  ) EA
union all
SELECT ''在TEST2中但不在TEST1中'' as SRC, EA.*
FROM (
  SELECT *
  FROM test.test2
  EXCEPT ALL
  SELECT *
  FROM test.test1 m
  where m.date='2022-11-01'
  ) EA
英文:

You can use the EXCEPT/EXCEPT ALL set operators to compare tables with the column layout (data-types and order of columns (if using SELECT *) must match).

SELECT 'IN TEST1 but not in TEST2' as SRC, EA.*
FROM (
  SELECT *
  FROM test.test1 m
  where m.date='2022-11-01'
  EXCEPT ALL
  SELECT *
  FROM test.test2
  ) EA
union all
SELECT 'IN TEST2 but not in TEST1' as SRC, EA.*
FROM (
  SELECT *
  FROM test.test2
  EXCEPT ALL
  SELECT *
  FROM test.test1 m
  where m.date='2022-11-01'
  ) EA

huangapple
  • 本文由 发表于 2023年2月6日 03:20:21
  • 转载请务必保留本文链接:https://go.coder-hub.com/75354892.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定