Hooli Hooli - 3 months ago 7
SQL Question

selecting rows from one table that aren't in another table with h2

I'm trying to fetch all records from a table that aren't already in another table. I will know whether they exist in the other table based on the

Age
column

Person

+------------+------------+------------+
| ID | Name | Age |
+============+============+============+
| 1 | John | 21 |
+------------+------------+------------+
| 2 | Jane | 24 |
+------------+------------+------------+


Person_Backup

+------------+------------+------------+
| ID | Name | Age |
+============+============+============+
| 1 | John | 22 |
+------------+------------+------------+
| 2 | Jane | 24 |
+------------+------------+------------+


In this example, the query should only respond with
Person_Backup
.
1 John 22


SELECT
"Person_Backup"."Name",
"Person_Backup"."Age"
FROM "Person_Backup"
INNER JOIN "Person" ON "Person"."Name" = "Person_Backup."Name"
WHERE ("Person"."Age" != "Person_Backup"."Age")


At the moment this will return a record from Person_Backup and all records from Person. I only want the records from Person_Backup though. Also, I tried using
GROUP BY
but it led to other, out of scope issues so I'd prefer not to take that route.

I suspect there's an easier way around this, perhaps a completely different query. All I want is to select all columns from Person_Backup that aren't in Person using
Age
as the differentiator.

Answer

This one looks at all three columns - ID, Name and Age - as one string and compares the strings between tables using CONCAT:

SELECT *
FROM Person_Backup
WHERE CONCAT(Person_Backup.ID, Person_Backup.Name, Person_Backup.Age)
NOT IN
(SELECT CONCAT(Person.ID, Person.Name, Person.Age) FROM Person)
Comments