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

Question

Just out of curiosity, I was wondering if there are any speed/efficiency differences in using [=] versus [in] versus [like] versus [matches] (for only 1 value) syntax for sql.

select field from table where field = value;

versus

select field from table where field in (value);

versus

select field from table where field like value;

versus

select field from table where field matches value;

Answer 1

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

Answer 2

I will add to that also exists and subquery.

But the performance depends on the optimizer of the given SQL engine.

In oracle you have a lot of differences between IN and EXISTS, but not necessarily in SQL Server.

The other thing that you have to consider is the selectivity of the column that you use. Some cases show that IN is better.

---

But you have to remember that IN is non-sargable (non search argument able) so it will not use the index to resolve the query, the LIKE and = are sargable and support the index

---

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