How to select non unique values from 1 column if a value doesn't exist in column 2

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.

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, 2014 at 11:52
• You don't have to remove the `AS`. It's optional, it can work either way. Aug 5, 2014 at 12:02