EXIST or IN or ANY
Russell Morris
- 3 minutes read - 608 wordsUse of IN and EXISTS and ANY
So, there are various different ways to compare you datasets against a list of values, you can use IN, EXISTS or ANY keywords. The only problem is which to use and when. Please note that the contents of this post is based on my experiences - you may find this works differently with your datasets.
Please note that this post applies to Postgresql only, given that different engines treat these commands in different ways.
IN keyword
The IN keyword compares the parameters in the column to be compared against and the values in the parameter list, it will match with this all values in your dataset that matches the parameters that you feed it.
The SQL would look something like this:
SELECT *
FROM my_table
WHERE a IN (x, x, x)
So the above SQL will look at the column a and the compare this with the parameters held in there. Alternatively if you wanted to go the other way it would be:
SELECT *
FROM my_table
WHERE a NOT IN (x, x, x)
You can even extend this by using a SUBQUERY - which would look like this:
SELECT *
FROM my_table
WHERE a IN (SELECT name
FROM my_other table)
So as you can see the IN statement seems to be pretty effective when you have a list of values that you are comparing against.
EXISTS keyword
The EXIST keyword works slightly differently then the IN keyword. So this is seeing if the VALUE you are comparing exists inside the dataset its comparing against. In this way when scans a dataset it will return TRUE or the first value it finds.
The SQL in this case would look something like this:
SELECT *
FROM my_table
WHERE EXISTS (SELECT 1
FROM my_other_table
WHERE name=a)
Again if you chose to go where the VALUE does not EXISTS you can go the other way:
SELECT *
FROM my_table
WHERE NOT EXISTS (SELECT 1
FROM my_other_table
WHERE name=a)
In the same way as any query you can have additional WHERE clauses on both the MAIN and SUBQUERY. You can use a CTE to create the command for the EXISTS, which then gives you the same flexibility as the IN command.
ANY (Interchangable with SOME) keyword
The ANY keyword works similar to IN but with a but of a twist. When you use ANY you also but in place a comparative operator for example =,<,>,<>,!= etc. So this will compare the entire dataset that you are comparing to, and then see if it fits combination of the operand and contents of the ANY parameters
The SQL in this case would look something like this:
SELECT *
FROM my_team
WHERE age > ANY(8,9)
So this would return and rows from my_team where they are older then 8 and 9. So you could make this into a an IN command by..
SELECT *
FROM my_team
WHERE age > ANY(8,9)
So what should I use?
So what would I use? the question is down to the context of this question.
- If you are feeding one or two parameters I would probably use the IN command.
- If you are feeding a static set of parameters I would probably use the EXISTS command.
- If you are feeding 500 + parameters (yep seen this) I would be tempted to use the EXISTS command with a CTE making a list of the parameters
At the end of the day these commands are very flexible and all will suit most needs. But its important to test and check execution plans for the queries before you make a final decision. Hope that this helps.