MSSQL: IN versus OR
If you're new here, you may want to subscribe to my RSS feed. Thanks for visiting!
IN versus OR
For SQL using IN is very usefull in order to determines if a given value matches any value in a subquery or a list. For example if you want to get certain data from a particular column or field, you can just right below command (in this case my example database is tests , table author and the columns are state and name)
USE tests
SELECT name, state
FROM authors
WHERE state IN (’CH’, ‘MY’, ‘SI’)
This query gives below results:
name state
——– —–
Whiter CH
Greeny CH
Darson CH
Leary CH
Streeght CH
Bannet CH
Deance MY
Strink SI
MacFe CH
Kar SI
Pantel MY
Hunter CA
McBadden CA(13 row(s) affected)
By using OR to produce the same results of the IN query:
USE tests
SELECT name, state
FROM authors
WHERE state = ‘CH’ OR state = ‘SI’ OR state = ‘MY’
Compare OR and IN , eventhough you get the same results using IN and OR , the next importance things to consider is :
- the time to execute the query considering very huge data (example in the author table, there are 928999 rows)
- the index that you need to create to reduce the execution time
Next tips : Using NOT IN to negates the returned value of IN. also NOT IN versus NOT EXIST
