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


i'm trying fetch records table aren't in table. know whether exist in other table based on age column

person

+------------+------------+------------+ | id         | name       |  age       | +============+============+============+ | 1          | john       |   21       | +------------+------------+------------+ | 2          | jane       |   24       | +------------+------------+------------+ 

person_backup

+------------+------------+------------+ | id         | name       |  age       | +============+============+============+ | 1          | john       |   22       | +------------+------------+------------+ | 2          | jane       |   24       | +------------+------------+------------+ 

in example, query should respond person_backup.1 john 22

select  "person_backup"."name",  "person_backup"."age" "person_backup"  inner join "person" on "person"."name" = "person_backup."name"  ("person"."age" != "person_backup"."age") 

at moment return record person_backup , records person. want records person_backup though. also, tried using group by led other, out of scope issues i'd prefer not take route.

i suspect there's easier way around this, perhaps different query. want select columns person_backup aren't in person using age differentiator.

this 1 looks @ 3 columns - id, name , age - 1 string , compares strings between tables using concat:

select * person_backup concat(person_backup.id, person_backup.name, person_backup.age) not in (select concat(person.id, person.name, person.age) person) 

Comments