4

Let's say I have two identical tables, A and B, with the row "x".

I want to select all elements in A, where the value of x in A is not in any value of x of B.

How do I do that?

TravisG
  • 2,373
  • 2
  • 30
  • 47
  • 1
    Sounds like `select '*' from A where not exists (select '*' from b where b.rowb = a.rowa)` – wildplasser May 24 '12 at 09:18
  • Give examples. Data that shows what to return, when to return it, and when not to return it. – MatBailie May 24 '12 at 09:27
  • I won't add an answer because there are already answers suggesting `LEFT JOIN`, `NOT IN`, `NOT EXISTS` and `EXCEPT` (the 4 methods I would have suggested). Since you've not specified which DBMS you are using here is a link to another question regarding efficency of each method on various DBMS http://stackoverflow.com/questions/2246772/whats-the-difference-between-not-exists-vs-not-in-vs-left-join-where-is-null – GarethD May 24 '12 at 09:45

5 Answers5

4

You could also do something like this:

SELECT * FROM TableA
LEFT JOIN TableB on TableA.X = TableB.X
WHERE TableB.X IS NULL

(For the very straightforward example in your question, a NOT EXISTS / NOT IN approach is probably preferable, but is your real query is more complex, this is an option you might want to consider; if, for instace, you want som information from TableB where there is a match, but also want to know where there isn't one)

David Hedlund
  • 128,221
  • 31
  • 203
  • 222
  • Could you explain, in a little more detail, why this works? I don't get why the WHERE condition works. Why is TableB.X ever NULL? I have no NULL Values in my table. – TravisG May 24 '12 at 09:32
  • @TravisG: This is how a `LEFT JOIN` works. In an `INNER JOIN` you would only receive the records where there is a `TableA.x <-> TableB.x` match. When doing a left (outer) join from `TableA`, you're saying that you want all the records from `TableA`, joined up with `TableB` where there is a match. For the records that doesn't match, every field from `TableB` will be reported as null in your select, even if the field in itself is marked as not nullable. – David Hedlund May 24 '12 at 09:36
2

I'm having some trouble to understand what you need.
Anyway try this:

SELECT * FROM tableA 
WHERE x not IN (SELECT x FROM tableB)
Marco
  • 56,740
  • 14
  • 129
  • 152
2
select *
from TableA
except
select *
from TableB
Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
1

The fastest is the Left Join

SELECT * FROM A LEFT JOIN B ON A.X = B.X WHERE B.X IS NULL
cmilhench
  • 666
  • 5
  • 17
  • `LEFT JOIN` is not always fastest. It is DBMS specific. http://stackoverflow.com/questions/2246772/whats-the-difference-between-not-exists-vs-not-in-vs-left-join-where-is-null – GarethD May 24 '12 at 09:39
1

use it :

select * from a where x not in (select x from b)
Botz3000
  • 39,020
  • 8
  • 103
  • 127
Saranya Jothiprakasam
  • 294
  • 2
  • 6
  • 17