Manuel M Manuel M - 5 months ago 11
SQL Question

What is the difference when comparing with parentheses: WHERE (a, b)=(1,2)

I stumbled across the follwoing (valid) query in MySQL (also works in Oracle/MSSQL when replacing

=
with
IN
):

SELECT * from mytable WHERE (a, b)=(1,2)


It's the same as

SELECT * from mytable WHERE a=1 and b=2


I think the definition in the MySQL docs is here:

simple_expr:
[...]
| (expr [, expr] ...)
[...]


What is this called? Are there any pros and cons for using it?

Answer

It's SQL feature F641, Row Value Constructors or Row and table constructors.

After reviewing the execution plan of both queries, I can say that in Oracle(Using IN() which is basically the same), the optimizer evaluate both the same way and both are using the indexes :

Separate conditions:

EXPLAIN PLAN FOR
SELECT * FROM dim_remedy_tickets_cache t
where t.tt_id = '1' and t.region_name = 'one';

6   |   0 | SELECT STATEMENT            |                              |     1 |   311 |    30   (0)| 00:00:01 |
7   |   1 |  TABLE ACCESS BY INDEX ROWID| DIM_REMEDY_TICKETS_CACHE     |     1 |   311 |    30   (0)| 00:00:01 |
8   |   2 |   INDEX RANGE SCAN          | DIM_REMEDY_TICKETS_HISTORYPK |     1 |       |    20   (0)| 00:00:01 |

Combined conditions:

EXPLAIN PLAN FOR
SELECT * FROM dim_remedy_tickets_cache t
where (t.tt_id,t.region_name) in  (('1','one'))

6   |   0 | SELECT STATEMENT            |                              |     1 |   311 |    30   (0)| 00:00:01 |
7   |   1 |  TABLE ACCESS BY INDEX ROWID| DIM_REMEDY_TICKETS_CACHE     |     1 |   311 |    30   (0)| 00:00:01 |
8   |   2 |   INDEX RANGE SCAN          | DIM_REMEDY_TICKETS_HISTORYPK |     1 |       |    20   (0)| 00:00:01 |

I assume all RDBMS will evaluate this queries the same.

Row constructors are legal in other contexts. For example, the following two statements are semantically equivalent (and are handled in the same way by the optimizer):

Like @jarlh mentioned in the comments it can be very handy when needed to compare multiple combinations of columns using IN() :

 SELECT * FROM YourTable
 WHERE (col1,col2) IN((1,2),(2,3),(4,4)...)

Instead of:

SELECT * FROM YourTable
WHERE (col1 = 1 and col2 = 2) OR
      (col1 = 2 and col2 = 3) OR
      (col1 = 4 and col2 = 4) OR
      ....

So:

Cons - May be less readable for some people , but basically no cons.

Pros - Less code , and the combination of multiple columns comparison using IN() :