英文:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论