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?