Question 89859 in PostgreSQL Question posted in
The official documentation can be found here.

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

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)

Tags:

2

Answers


  1. 0

    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'
    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search