I am using IN operator in a non conventional manner, with a literal value on the left side and columns on the right side
So instead of:
(table1.id = 123 OR table2.id = 123 OR table3.id = 123)
I use:
123 IN (table1.id, table2.id, table3.id)
It works, however, I feel that it smells a bit bad because it is not a common usage, so the database implementation might not be tuned for it. Usually the left side is a column name and the right side is variable size list of dynamic literal values.
I am using postgreSQL and didn’t notice any issues.
Does anyone can share from his experience if this usage can cause an issue?
2
Answers
You can never rule out the possibility of bugs, but I also wouldn’t worry about them preemptively in this case. I was surprised to see that the most obvious optimization, using a BitmapOr, was still applied to the IN list when there were multiple columns from the same table. (When they are not in the same table, I don’t foresee either way of writing it getting optimized very well.)
I would probably avoid this way of writing it for the sake of the people who need to read my code, not for the sake of the computer. But if the constant involved were much longer than ‘123’, maybe the avoidance of repetition would be worth the unconventional coding.
according to my experience using the IN operator with a literal value on the left side and columns on the right side is not a common usage, but it should work correctly in most database systems, including PostgreSQL. The database optimizer should be able to handle this type of query efficiently.
In PostgreSQL, the IN operator is designed to accept a list of values or a subquery on the right side. In your case, you are providing column names on the right side, which is unconventional but valid.
As long as your query is returning the expected results and performing well, there should be no immediate issues or performance concerns with using the IN operator in this manner.
However, it’s worth noting that this usage might make the SQL query less readable and less intuitive for other developers who are not familiar with this unconventional pattern. It’s generally recommended to follow standard SQL practices for better code maintainability and collaboration.
If you have concerns about the performance or potential issues with this usage, you can consider testing the query with large data sets or consulting with a database expert to evaluate any potential drawbacks specific to your application and database environment.