mysql cross join not in?

Question

Using a query like below you can fetch rows in which the color of the id is blue, purple, green, white, and black.

SELECT t1.id, col
FROM extra as e INNER JOIN your_table as t1 USING ( id )
CROSS JOIN your_table as t2 USING ( id )
CROSS JOIN your_table as t3 USING ( id )
CROSS JOIN your_table as t4 USING ( id )
CROSS JOIN your_table as t5 USING ( id )
WHERE t1.color = 'blue' and t2.color = 'purple' and t3.color= 'green' and t4.color= 'white' and t5.color= 'black'

If you try to use != or NOT IN, it doesn't seem to work. How would I write the query so that the colors would contain blue, purple, green, white, but NOT black?

Answer 1

You could do something along the lines of:

select e.id
from   extra as e
where  exists (select null from your_table as t where t.id = e.id and t.color = 'blue')
  and  exists (select null from your_table as t where t.id = e.id and t.color = 'purple')
  and  exists (select null from your_table as t where t.id = e.id and t.color = 'green')
  and  exists (select null from your_table as t where t.id = e.id and t.color = 'white')
  and not exists (select null from your_table as t where t.id = e.id and t.color = 'black')

Or, something like this would probably be more efficient:

select e.id
from   extra as e
where  4 = 
       (select count(*) 
        from   your_table as t 
        where  t.id = e.id 
          and  t.color in ('blue', 'purple', 'green', 'white'))
  and  0 = 
       (select count(*) 
        from   your_table as t 
        where  t.id = e.id 
          and  t.color in ('black'))

Answer 2

I think you are overcomplicating the query, what you are attempting appears to be a standard join like so

select * from cars join colors where cars.colorid = colors.colorid where colors.color != 'black'

Answer 3

I don't know why you're using CROSS JOIN. That's usually for generating Cartesian products. All you need is a plain INNER JOIN or simply JOIN.

I usually use an OUTER JOIN when I want to test for the absence of some data. When no match is found, t5 will be NULL.

SELECT t1.id, col
FROM extra as e 
INNER JOIN your_table as t1 ON ( e.id=t1.id AND t1.color = 'blue' )
INNER JOIN your_table as t2 ON ( e.id=t2.id AND t2.color = 'purple' )
INNER JOIN your_table as t3 ON ( e.id=t3.id AND t3.color = 'green' )
INNER JOIN your_table as t4 ON ( e.id=t4.id AND t4.color = 'white' )
LEFT OUTER JOIN your_table as t5 ON ( e.id=t5.id AND t5.color = 'black' )
WHERE t5.id IS NULL;

You're right that the above technique using joins is faster than using correlated subqueries, and it's also (at least in MySQL) faster than a GROUP BY solution that some people use:

SELECT e.id, col
FROM extra as e
INNER JOIN your_table AS t USING ( id)
WHERE t.color IN ('blue', 'purple', 'green', 'white')
GROUP BY e.id
HAVING COUNT(*) = 4;

(This query doesn't solve the "not black" problem, I'm just illustrating the technique.)