mysqlsqldatabasesearchmysql-select-db

MYSQL Difference between ( field1, field2 ) = (x,y) vs field1= x AND field2 = Y

I have a table studentDB with below fields

select sid, sname, sDOB from Student

I have seen this is many place, what is difference between (xx,yy) vs AND, and which one is more performance wise better

select * from Student where (sid,sname) = (101,'foo')

vs

select * from Student where sid = 101 AND sname = 'foo'

And also what is name of this operator () when using for matching?

Solution

  • Row constructor comparison is legal in SQL syntax, but in versions of MySQL 5.5 and earlier, this syntax did not support index optimization, so it was not encouraged.

    See https://dev.mysql.com/doc/refman/8.0/en/row-constructor-optimization.html

    By now, all supported versions of MySQL do support index optimization for row constructor comparison. The older versions that lack this feature are past their end-of-life, so you shouldn't be using those old versions anyway. Therefore there's no reason to avoid this syntax anymore.