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

Question

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 1

It can be very handy when needed to compare multiple columns to multiple combination of values by 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
      ....

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):

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() :