1

so i have two tables. I'm trying to join two tables and only return records from table a that don't have a match in table b. i'm stumped so far.

Table A

--------------------
name amount     date
--------------------
bob     250 4/8/2010
dan     100 4/8/2010
sla     222 4/8/2010
obo     344 4/8/2010
bob     100 4/8/2010

Table B

--------------------
name amount     date
--------------------
bob     250 4/8/2010
dan     100 4/8/2010
sla     500 4/8/2010
obo     300 4/8/2010
bob     100 4/8/2010
potashin
  • 43,297
  • 11
  • 81
  • 105
morgan
  • 13
  • 2

2 Answers2

2

You can use EXISTS :

SELECT * 
FROM a 
WHERE NOT EXISTS (SELECT * 
                  FROM b 
                  WHERE name = a.name 
                    AND amount = a.amount 
                    AND date = a.date)
potashin
  • 43,297
  • 11
  • 81
  • 105
1

One of the canonical ways to do this in SQL is using an outer join:

select a.*
from a left outer join b
on (
  a."name" = b."name"
  and a."amount" = b."amount"
  and a."date" = b."date"
) where b."name" is null
and b."amount" is null
and b."date" is null

The left outer join returns all rows from the table on the left of the join, whether or not they match rows from the table on the right side of the join. But when rows from the left table don't have a match in the right table, the columns of the right table are set to null. You can thus filter out all rows that did match and return only rows from the left table that didn't match.

Community
  • 1
  • 1
Yawar
  • 10,668
  • 4
  • 46
  • 76
  • this was exactly what i was looking for. this is way faster than the way i was doing it before with multiple selects. – morgan May 19 '14 at 01:47
  • @morgan - you mean a correlated subquery like Notulysses' answer? Yeah, correlated subqueries are usually a lot slower than joins. – Yawar May 19 '14 at 02:50
  • yah. That's basically what i was doing but with more complexity. it was mind-blowingly slow. thanks again for the help. – morgan May 19 '14 at 12:29