Forum Stats

  • 3,750,412 Users
  • 2,250,174 Discussions
  • 7,866,971 Comments

Discussions

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

Thorsten Kettner
Thorsten Kettner Member Posts: 38 Red Ribbon
edited Aug 19, 2021 2:35PM in Database Ideas - Ideas

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

Thorsten Kettner
1 votes

Active · Last Updated

Comments

  • Stew Ashton
    Stew Ashton Member Posts: 2,861 Gold Trophy

    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?