SQL ... where constant in columns

Question

Is there something wrong querying like this:

select * from category_cross_selling
where 'SOMEID' in (f_categories_from, f_categories_to);

instead of like this?:

select * from category_cross_selling
where f_categories_from = 'SOMEID' or f_categories_to = 'SOMEID';

I do this sometimes, but I don't know if it is bad practice or if I break the optimizer engine of MySQL or I don't know, but people have told me to stop doing it.

I like it because it is clear that it is the same id that I am looking for in any columns, and you don't duplicate constants, thus avoiding bugs.

EDIT: Notice that the reason I asked this question is because normally in IN searches, you put the column in the left side and N constants in the right side

check this case http://stackoverflow.com/questions/782915/mysql-or-vs-in-performance — RoMEoMusTDiE, May 17 '17 at 22:34
I asked because normally you se IN used in the opposite way, whichis `table.column IN ('constant1', 'constant2')` — santiago arizti, May 17 '17 at 22:39
@maSTAShuFu I don't think that is valid syntax, and I tried it and got this: `You have an error in your SQL syntax...` — santiago arizti, May 17 '17 at 22:43
@maSTAShuFu no, the last suggestion will not work, that's a syntax error — Shadow, May 17 '17 at 22:47
yes.. that's why you need to check the execution from your queries both of them are syntactical correct — RoMEoMusTDiE, May 17 '17 at 22:53
I wouldn't be surprised if they're implemented identically, so use whichever one seems to be more readable. — Barmar, May 17 '17 at 23:09

score 0 · Answer 1 · answered May 17 '17 at 22:51

0

Since both syntax are valid, the only reason not to use your version with in operator would be if the or version performed better. I would test both versions each time and see which wins.

answered May 17 '17 at 22:51

Shadow

33,525
10
51
64

score 0 · Answer 2 · answered May 17 '17 at 23:28

The two should have the same performance -- which is not good (I'll get to that in a moment). The in version has the advantage of being shorter and less prone to error, so don't be confused by it.

Probably the most performant way to write the query is:

select *
from category_cross_selling
where f_categories_from = 'SOMEID'
union all
select *
from category_cross_selling
where f_categories_from <> 'SOMEID' and -- may need to take `NULL` into account
      f_categories_to = 'SOMEID';

This version can take advantage of indexes on category_cross_selling(f_categories_from) and category_cross_selling(f_categories_to, f_categories_from). Both indexes are needed, each for one of the subqueries.

interesting... and in the other queries (the ones in the original question) why would mysql not use those same indexes? — santiago arizti, May 17 '17 at 23:51
@santiagoarizti . . . Roughly speaking, MySQL only uses one index per `select`. — Gordon Linoff, May 18 '17 at 01:36

Bharat Prasad Satyal · Answer 3 · 2017-05-17T22:59:49.310

Enable statistics running below queries.

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

Now click on Include Actual Execution Plan(or Ctrl+M). Now run both queries at same time. You would be able to see actual execution plan and estimated execution plan for both queries in Execution plan section. Also look at messages section to see how many logical reads has been done and other parts. From this you can see what is the difference plans for those queries. If both are same there is no difference at all.

Update

I have seen the properties of table scan in Argument section from execution plan for both queries and found that even if you write 'SOMEID' in (f_categories_from, f_categories_to) SQL engine will convert that into f_categories_from = 'SOMEID' or f_categories_to = 'SOMEID' internally as below. So there is no difference at all.

For 'SOMEID' in (f_categories_from, f_categories_to):

Argument: OBJECT:([Database].[dbo].[TestTable]), WHERE:('SOMEID'=[TestTable].[f_categories_to] OR 'SOMEID'=[TestTable].[f_categories_from])

For f_categories_from = 'SOMEID' or f_categories_to = 'SOMEID':

Argument: OBJECT:([Database].[dbo].[TestTable]), WHERE:([TestTable].[f_categories_from]='SOMEID' OR [TestTable].[f_categories_to]='SOMEID')

The question is about mysql, not ms sql. – Shadow May 17 '17 at 22:54 — Shadow, May 17 '17 at 22:54

SQL ... where constant in columns

3 Answers3