CODEWITHSUNDEEP
sqlh2
Hooli
Hooli

Reputation: 1195

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.

Upvotes: 0

Views: 246

Answers (3)

kjmerf
kjmerf

Reputation: 4345

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)

Upvotes: 1

Mahesh Sambu
Mahesh Sambu

Reputation: 349

SELECT 
"Person_Backup"."Name", 
"Person_Backup"."Age"
FROM "Person_Backup" 
LEFT JOIN "Person" ON "Person"."Name" = "Person_Backup"."Name" 
AND "Person"."Age" = "Person_Backup"."Age"
WHERE "Person"."ID" IS NULL

Upvotes: 1

sstan
sstan

Reputation: 36483

You can use the NOT EXISTS clause for this:

select *
  from person_backup b
 where not exists (select null
                     from person p
                    where p.name = b.name
                      and p.age = b.age)

Upvotes: 1

Related Questions