SQL Efficiency - [=] vs [in] vs [like] vs [matches]

Question

Just out of curiosity, I wondered if there were differences in speed / efficiency when using [ = ] versus [ in ] versus [ like ] versus [ matches ] (for only one value) syntax for sql.

 select field from table where field = value; 

against

 select field from table where field in (value); 

against

 select field from table where field like value; 

against

 select field from table where field matches value; 

Answer 1

A will add to this an existing and additional query.

But performance depends on the optimizer of this SQL engine.

In oracle, you have a big difference between IN and EXISTS, but not required in SQL Server.

Another thing you should consider is the selectivity of the column you are using. In some cases, it’s better to show which is better.

---

But you must remember that IN does not have the right to (an argument without a search), so it will not use the index to resolve the request, LIKE and = are sargable and support the index

---

The best? You need to spend some time to test it in your environment.

Answer 2

it depends on the underlying SQL engine. For example, in MS-SQL (according to the output of the query planner), IN clauses are converted to =, so there would be no difference

Answer 3

typically, the in operator is used when comparing multiple values. The engine scans the list for each value to see if it matches. if there is only one element, then there is no time difference with respect to the "=" operator.

the like expression is different in that it uses pattern matching to find the right values, and as such, it takes a bit more work at the back end. For one value there would not be a significant time difference, because you have only one possible match, and the comparison will be the same type of comparison as for "=" and "in".

in principle, no, or at least the difference is so insignificant that you would not notice.

Answer 4

The best practice for any question about what will be faster is to measure. SQL engines are notoriously difficult to predict. You can look at the EXPLAIN PLAN output to understand this, but in the end, only a performance measurement on real data will tell you what you need to know.

Theoretically, the SQL engine could implement all three of them in exactly the same way, but they may not be.