Differences in performance between equal (=) and IN with the same value

Question

How do SQL mechanisms differ when we use the equal sign and the IN operator has the same value? Does runtime change?

1st using the equality check operator

WHERE column_value = 'All' 

2nd using the OR operator and one value

 WHERE column_value IN ('All') 

Does the SQL IN mechanism IN to = only one value?

Is there a difference in the same in MySQL and PostgreSQL?

Answer 1

There is no difference between the two statements, and the optimizer converts IN to = when IN has only one element in it.

Although, when you have such a question, just run both statements, run their execution plan and see the differences. You will not find here.

After a lot of searching on the Internet, I found a SQL document to support this (I assume it applies to all DBMSs):

If there is only one value in the parenthesis, this commend is equivalent to

WHERE "column_name" = 'value1

Here is the link to the document .

Here is the plan for executing both queries in Oracle (most DBMSs will handle the same):

 EXPLAIN PLAN FOR select * from dim_employees t where t.identity_number = '123456789' Plan hash value: 2312174735 ----------------------------------------------------- | Id | Operation | Name | ----------------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS BY INDEX ROWID| DIM_EMPLOYEES | | 2 | INDEX UNIQUE SCAN | SYS_C0029838 | ----------------------------------------------------- 

And for IN() :

 EXPLAIN PLAN FOR select * from dim_employees t where t.identity_number in('123456789'); Plan hash value: 2312174735 ----------------------------------------------------- | Id | Operation | Name | ----------------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS BY INDEX ROWID| DIM_EMPLOYEES | | 2 | INDEX UNIQUE SCAN | SYS_C0029838 | ----------------------------------------------------- 

As you can see, they are both the same. This is in the indexed column. The same goes for an unindexed column (just a full table scan).

Answer 2

It makes no difference if you use it with a single value. If you check the table scan, index scan, or index search for the above two queries, you will find that there is no difference between the two queries.

Is there any difference in the same in Mysql and PostgresSQL?

No, that would not make any difference for the two engines (Infact it would be the same for most databases, including SQL Server, Oracle, etc.). Both engines convert IN to =

Answer 3

There are actually no big differences, but if your column_value is indexed, the IN statement cannot read it as an index.

Once I met this problem, so be careful.

Answer 4

Teach a person to fish, etc. Here, how to see for yourself what options your queries will do:

 mysql> EXPLAIN SELECT * FROM sentence WHERE sentence_lang_id = "AMH"\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: sentence type: ref possible_keys: sentence_lang_id key: sentence_lang_id key_len: 153 ref: const rows: 442 Extra: Using where 

And try another:

 mysql> EXPLAIN SELECT * FROM sentence WHERE sentence_lang_id in ("AMH")\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: sentence type: ref possible_keys: sentence_lang_id key: sentence_lang_id key_len: 153 ref: const rows: 442 Extra: Using where 

You can read here about how to interpret the mysql EXPLAIN query results. Recall that we have received the identical conclusion for both requests: the same "execution plan" is created. The type string tells us that the request uses a non-ideal index (in this case, a foreign key), and the ref line tells us that the request is executed by comparing the constant value with this index.

Answer 5

There is no difference for a single IN clause. Below is a demo of using the EMPS table that I have.

 select * from emps where empid in (1) select * from emps where empid=1 

The predicate for the first query in the execution plan:

 [PerformanceV3].[dbo].[Emps].[empID]=CONVERT_IMPLICIT(int,[@1],0) 

The predicate for the second query in terms of execution:

 [PerformanceV3].[dbo].[Emps].[empID]=CONVERT_IMPLICIT(int,[@1],0) 

If you have multiple values ​​in IN Clause, it’s best to convert them to a union

Answer 6

To add a different perspective, one of the main points of rdbms systems is that they will rewrite your request for you and choose the best execution plan for this request and all equivalent ones. This means that as long as the two requests are logically identical, it should always generate the same execution plan for a given rdbms.

However, many queries are equivalent (the same set of results), but only because of limitations that the database itself does not know, so be careful in these cases (for example, for a flag field with numbers 1-6, db doesn't know <3 same as in (1,2) ). But at the end of the day, if you just think about the readability of the and and or operators, it will not affect the performance of how you write them.

Answer 7

You will need to run an execution plan for both and see the results.

I believe that they will have the same execution plan, since it will be executed in the same way as the regular = sign, when only one value is placed inside the IN() operator.

There is no reason why the optimizer will behave differently for a query like this.