Hi,

My sample data looks like this:

Table1:

ID Name Age

2 Ted 34
4 Kim 43
5 Joe 29

Table2:

Name Age

Ted 34
Tom 23

Except clause only works for exact column numbers and type matches. The result I'm looking for is this:

4 Kim 43
5 Joe 29

Basically the minus of table 2 from table 1 for matching column name and age, but since table 2 is without an ID column, its getting tricky and I'm unable to use EXCEPT as I want the added ID information in the end result.

The only way I could think of was:

select tb1.id,tb1.name,tb1.age from
(select name,age from table1
except
select name,age from table2)
inner join table1 on
table1.name=tb1.name and table1.age=tb1.age

Now if my simple table1 gets replaced by a select statement covering 5-6 joins, my solution above has got to be the least ideal way to do this isn't it?

thanks!

Another option to avoid the join could be this:
The subselect should usually only be executed once because it is uncorrelated. (Depends on the optimizer decision, of course.)