How can I get all of the values from 1 column, which do not (or do) have a specific value in one of their rows?

For instance, consider example table:

| ColA | ColB |
| aaa1 | zzz1 |
| aaa1 | zzz2 |
| aaa1 | zzz1 |
| aaa2 | zzz3 |

I want to get each of the values from ColA, where the set group by ColA does not contain a row with a specific value in ColB. i.e.

select ColA from test group by ColA not containing ColB like 'zzz2'

Unfortunately, as far as I know there isn't a containing keyword in MySQL. So what can I do instead? Is there some way to tell having to filter by another column?


This is an anti-semijoin. Can be written with NOT IN, LEFT JOIN / WHERE IS NULL or NOT EXISTS:

select distinct ColA       -- if there is another table where ColA is unique
from test as a             -- use it here, instead of table test
                           -- and remove the distinct
where not exists
      ( select 1 
        from test as b
        where b.ColB = 'zzz2' 
          and b.ColA = a.ColA
      ) ;

You could also use GROUP BY with a CASE expression:

select ColA
from test
group by ColA
having count(case when ColB = 'zzz2' then 1 end) = 0 ;
  • Yes, that's it exactly. Is there a speed difference between the two versions?
    – Benubird
    Mar 4 '15 at 14:31
  • Different execution plans, so different efficiency, depending on the distribution of data as well (i.e. if 95% of the ColA vlaues have a related ColB='zzz2' or if only 5% of them have) Mar 4 '15 at 14:34

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.