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

Question

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.

Answer 1

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) ;

• Ben: Great thanks, although I had to remove the 'AS' parts from the query
• ypercubeᵀᴹ: You don't have to remove the AS. It's optional, it can work either way.