How do combine two Queries or is it only one?

Question

Question:

Example:

I have two exact similar tables my PostgreSQL Database. info_table and info_table_dump with the exact same columns date_at, name, color and length . Now i want to know if there are entries in info_table_dump that do not exist in info_table. Therefore i made these Query:

SELECT
date_at,name,color,length
FROM
info_table_dump
EXCEPT
SELECT
date_at,name,color,length
FROM
info_table;

The result is fine. It works like i assumed (i created 2 entries that do not match) the none-duplicates are shown. But now i wanted only to fetch the given ids of the non-duplicate rows. Something like this...

SELECT
id
FROM
info_table_dump
WHERE
(SELECT
date_at,name,color,length
FROM
info_table_dump
EXCEPT
SELECT
date_at,name,color,length
FROM
info_table);

I also tried something with EXISTS but its not the result i wanted.

So my Question how do combine the Query?

I want only the ìds of the info_table_dump rows, who not already exist in info_table.

`SELECT id FROM info_table_dump where (date_at,name,color,length) not in (SELECT date_at,name,color,length FROM info_table)` Can't remember how nulls are handled. — shawnt00, Jan 29 '22 at 17:41

LukStorms · Accepted Answer · 2022-01-29T20:43:46.700

2

So they should not exist in info_table?

SELECT id
FROM info_table_dump d
WHERE NOT EXISTS (
  SELECT 1
  FROM info_table i
  WHERE i.date_at IS NOT DISTINCT FROM d.date_at
    AND i.name IS NOT DISTINCT FROM d.name
    AND i.color IS NOT DISTINCT FROM d.color
    AND i.length IS NOT DISTINCT FROM d.length
);

The IS NOT DISTINCT FROM is just in case some columns are nullable.

A more conventional old-school method would be a left join and filtering the unmatched. Which might actually have better performance.

SELECT d.id
FROM info_table_dump d
LEFT JOIN info_table i
  ON i.date_at IS NOT DISTINCT FROM d.date_at
 AND i.name IS NOT DISTINCT FROM d.name
 AND i.color IS NOT DISTINCT FROM d.color
 AND i.length IS NOT DISTINCT FROM d.length
WHERE i.id IS NULL;

Test on db<>fiddle here

edited Jan 29 '22 at 20:43

answered Jan 29 '22 at 17:30

LukStorms

28,916
5
31
45

Thanks. It Worked. I just do not understand that `SELECT 1` line. if you have some link for further reading i would appreciate that. – black_hole_sun Jan 29 '22 at 18:07
1

[Here's a link](https://www.techonthenet.com/postgresql/exists.php) Basically, it doesn't matter if it's `SELECT 1` or `SELECT *` or `SELECT 'WTF'`. What matters is, if it can find at least 1 match. – LukStorms Jan 29 '22 at 18:23

How do combine two Queries or is it only one?

1 Answers1