6

I have two tables TABLE1 which looks like:

id      name     address
1       mm     123
2       nn     143

and TABLE2 w/c looks like:

name     age
mm      6
oo      9

I want to get the non existing names by comparing the TABLE1 with the TABLE2.

So basically, I have to get the 2nd row, w/c has a NN name that doesn't exist in the TABLE2, the output should look like this:

id      name     address
2      nn      143

I've tried this but it doesn't work:

SELECt  w.* FROM TABLE1 W INNER JOIN TABLE2 V
  ON W.NAME <> V.NAME

and it's still getting the existing records.

kenorb
  • 155,785
  • 88
  • 678
  • 743
Blair Yumi
  • 289
  • 4
  • 6
  • 13

2 Answers2

6

An INNER JOIN doesn't help here.

One way to solve this is by using a LEFT JOIN:

SELECT w.* 
FROM TABLE1 W 
LEFT JOIN TABLE2 V ON W.name = V.name
WHERE ISNULL(V.name);
Bjoern
  • 15,934
  • 4
  • 43
  • 48
2

The relational operator you require is semi difference a.k.a. antijoin.

Most SQL products lacks an explicit semi difference operator or keyword. Standard SQL-92 doesn't have one (it has a MATCH (subquery) semijoin predicate but, although tempting to think otherwise, the semantics for NOT MATCH (subquery) are not the same as for semi difference; FWIW the truly relational language Tutorial D successfully uses the NOT MATCHING semi difference).

Semi difference can of course be written using other SQL predicates. The most commonly seen are: outer join with a test for nulls in the WHERE clause, closely followed by EXISTS or IN (subquery). Using EXCEPT (equivalent to MINUS in Oracle) is another possible approach if your SQL product supports it and again depending on the data (specifically, when the headings of the two tables are the same).

Personally, I prefer to use EXISTS in SQL for semi difference join because the join clauses are closer together in the written code and doesn't result in projection over the joined table e.g. 

SELECT *
  FROM TABLE1 W
 WHERE NOT EXISTS (
                   SELECT * 
                     FROM TABLE2 V
                    WHERE W.NAME = V.NAME
                  );

As with NOT IN (subquery) (same for the outer join approach), you need to take extra care if the WHERE clause within the subquery involves nulls (hint: if WHERE clause in the subquery evaluates UNKNOWN due to the presence of nulls then it will be coerced to be FALSE by EXISTS, which may yield unexpected results).

UPDATE (3 years on): I've since flipped to preferring NOT IN (subquery) because it is more readable and if you are worried about unexpected results with nulls (and you should be) then stop using them entirely, I did many more years ago.

One way in which it is more readable is there is no requirement for the range variables W and V e.g. 

SELECT * FROM TABLE1 WHERE name NOT IN ( SELECT name FROM TABLE2 );
onedaywhen
  • 55,269
  • 12
  • 100
  • 138
  • I like how explicit is it to say `WHERE NOT EXISTS`, but it seems like the subquery method would be inefficient. I picture a JOIN being done once, then one more pass to exclude non-matches; the subquery has to be run once for each record in the outer query. I'm not sure if that actually makes much difference for SQL engines, but it seems like it would. – Nathan Long Jan 11 '12 at 16:30
  • @NathanLong: one could similarly use logic to counter-argue that `NOT EXISTS` has the ability to short-circuit: as soon as the first (non) matching row is found then it can stop, no point proceeding; and the optimizer can use statistics to target searches. But this is mere psychology: I suggest you don't try to second guess an optimizer too much. If two constructs are semantically the same then there's no logical reason why the optimizer shouldn't choose identical execution plans. – onedaywhen Jan 12 '12 at 06:54
  • @onedaywhen (after three years!) BTW: the subquery in the **not exists** should be`(SELECT * FROM TABLE2 V WHERE W.NAME = V.NAME)` – wildplasser Nov 01 '14 at 15:49