0

I have a table containing (among other things) 2 integer values, neither of which are unique. See below for example data:

---------------
| a    | b    |
---------------
| 1    | 1    |
---------------
| 1    | 2    |
---------------
| 2    | 1    |
---------------
| 2    | 2    |
---------------
| 2    | 3    |
---------------

How can I query column a, where certain values don't exist in the corresponding column b. E.g. for value 3, I'd want it to return the value 1 for column a I've come up with:

SELECT a FROM table WHERE a NOT IN (SELECT a FROM table where b = 3)

But this doesn't feel like the best/most efficient way.

0

You query will need DISTINCT, so it doesn't return the values multiple times:

SELECT DISTINCT a 
FROM table 
WHERE a NOT IN (SELECT a FROM table WHERE b = 3) ;

Also beware that NOT IN may return unexpected results if your columsn are nullable. It's safer to use NOT EXISTS or LEFT JOIN / IS NULL:

SELECT DISTINCT a 
FROM table AS x
WHERE NOT EXISTS (SELECT 1 FROM table AS y WHERE y.b = 3 AND y.a = x.a) ;
  • Great thanks, although I had to remove the 'AS' parts from the query – Ben Aug 5 '14 at 11:52
  • You don't have to remove the AS. It's optional, it can work either way. – ypercubeᵀᴹ Aug 5 '14 at 12:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.