Discussions
Categories
- 17.9K All Categories
- 3.4K Industry Applications
- 3.3K Intelligent Advisor
- 63 Insurance
- 535.8K On-Premises Infrastructure
- 138.1K Analytics Software
- 38.6K Application Development Software
- 5.6K Cloud Platform
- 109.3K Database Software
- 17.5K Enterprise Manager
- 8.8K Hardware
- 71K Infrastructure Software
- 105.2K Integration
- 41.5K Security Software
Allow row values (tuples) on the right-hand side of a comparision

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 :-)
Comments
-
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?
-
@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.