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

sqlite

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.

Best Answer

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