0

Actually I have a list of values and I just want to know the ones that doesnt exist on a specific table.

Table 'ball' has values green and red on colunm colors.

Te query to return the registered values is:

Select * from ball where colors in ('green', 'red', 'blue', 'gray')

In this case I need BLUE and GRAY to be returned.

fast edit: I have 5k+ results do compare this way.

Dan-SP
  • 800
  • 2
  • 8
  • 18

2 Answers2

2

You need to use a UNION:

SELECT colors FROM 
 (select 'green'as colors
  union
  select 'red'
  union
  select 'blue'
  union
  select 'gray') all_colors
WHERE NOT EXISTS (SELECT ball.colors FROM ball WHERE ball.colors = all_colors.colors)

Alternatively, if you have some table (or can make one) that contains all your colors, you'd be able to skip the UNION and just use that table.

PinnyM
  • 35,165
  • 3
  • 73
  • 81
0

Assuming you have a table that stores every available ball color, you can do it like so:

SELECT all_colors.color
FROM all_colors LEFT JOIN balls ON balls.color = all_colors.color
WHERE balls.color IS NULL

You can additionally create indexes for the color columns on each table to further speed things up.

Some reading on left outer joins: http://en.wikipedia.org/wiki/Join_(SQL)#Left_outer_join

Manny
  • 967
  • 1
  • 6
  • 17
  • Generally speaking, `NOT EXISTS` will perform better than `LEFT JOIN` in a situation like this. YMMV... – PinnyM Feb 07 '14 at 19:54
  • @PinnyM Well, it seems to be dependent on the engine. MySQL appears to prefer `LEFT JOIN`, according to [this answer](http://stackoverflow.com/a/2246793/512334) anyway. But I agree, YMMV. – Manny Feb 07 '14 at 20:01
  • @Dan-SP I would strongly suggest having something that will house that color data, otherwise how can you know what's missing if you can't programmatically identify all of your options? – Manny Feb 07 '14 at 20:02
  • @Manny I just need to perform it once.. Its for data loading. – Dan-SP Feb 07 '14 at 20:04
  • @Dan-SP Can you give a bit more detail around this problem? Why do you need to know about the unused colors? – Manny Feb 07 '14 at 20:07
  • @Manny I need to load some data to my development bd but Im haveing problems with parent keys. Knowing the registers that are missing, I can export from production, load it and process my desired data properly. – Dan-SP Feb 07 '14 at 20:11
  • What if you figure out what values are referenced in production and export and load that in first? For example, run something like this against production to get the values being used: `SELECT DISTINCT color from balls` – Manny Feb 07 '14 at 20:18
  • @Manny Yeah.. Looks like I have no options.. hehe.. Since its 400k+ results, I owuld like to take it easier but I dont see how – Dan-SP Feb 07 '14 at 20:21
  • @Dan-SP Yeah, that's another thing. This is getting off topic, but as I'm sure you know, you have to be careful with potentially intensive queries against production. Another option would be to grab a backup and restore that to your dev. Depends on your shop of course and if an option like that is available. I'm not sure what else to suggest! – Manny Feb 07 '14 at 20:24
  • Regarding that great comparison reference above - it is a bit dated (2009 using PostgreSQL 8.4 and MySQL 5.1). Are you aware of any recent comparisons to this effect? – PinnyM Feb 10 '14 at 21:53
  • @PinnyM Unfortunately, no. I'm curious myself but it's low priority for me to research at the moment. – Manny Feb 10 '14 at 21:57