Allow row values (tuples) on the right-hand side of a comparision

Thorsten Kettner

We are allowed to compare tuples (called row values in the SQL standard) with data sets, e.g.

SELECT * FROM emp WHERE (dept, sal) IN (SELECT dept, MAX(sal) FROM emp GROUP BY dept);

We can also use data set literals (or whatever this may be called):

SELECT * FROM emp WHERE (dept, sal) IN ((1, 100), (2, 200));

And if the set only contains one row, we can even use the equality sign:

SELECT * FROM emp WHERE (dept, sal) = ((1, 100));

We cannot use a row value on the right-hand side of the comparision though:

SELECT * FROM emp WHERE (dept, sal) = (1, 100);

=> ORA-00920: invalid relational operator

There it is. I am proposing to allow row values on the right-hand side of a comparision. (IN would be invalid for this of course, because this is no data set. = and <> would be allowed. And as to < ,<= , >, and >=I am making that a separate request :-)

Stew Ashton

Could you please explain further the advantage of

SELECT * FROM emp WHERE (dept, sal) = (1, 100);

over

SELECT * FROM emp WHERE (dept, sal) = ((1, 100));

?

I don't understand your motivation for making this request. I must be missing something. Is it to ease migration from other databases?

Thorsten Kettner

@Stew Ashton: This is just for consistency. It would be weird, did we have to write WHERE id = (5) instead of WHERE id = 5 every time. WHERE (dept, sal) = (1, 100) must work when WHERE (dept, sal) = ((1, 100)) does.

And well, yes, it eases things when coming from another DBMS or when just starting with tuples. One may try WHERE (dept, sal) = (1, 100), see that it doesn't work and dismiss the idea completely, because they don't know that this is possible by adding another pair of parentheses - a pair that would seem superfluous, if we didn't know this to be an Oracle workaround.