Are SQL tuples, when used as predicates, just a syntactic sugar for expanded logical AND's?

user_2409095 0
Viewed 101

Are SQL tuples, when used as predicates, just a syntactic sugar for expanded logical AND's or there is more to it?

eg.

with tmp1(c1,c2,c3) as (
  select * from (
    values(1,'a','apple')
    ,(2,'b','ball')
    ,(3,'c','cat')
    ,(4,'d','dog')
    ,(5,'e',null)
  )
)
select * from tmp1 where (c1,c2,c3) = (1,'a','apple');

is equivalent to

select * from tmp1 where c1 = 1 and c2 = 'a' and c3 = 'apple';

and similarly for IN clause or JOIN's

I did check for NULL awareness(if it would get translated to the form x IS NOT DISTINCT FROM y) but atleast DB2 isn't

select * from tmp1 where (c1,c2,c3) = (5,'e',null); -- Empty resultset
db2 sql
1 Answers

When you are strictly considering scalar equality, yes, it's equivalent.

There are other not-so-simple cases:

  • When you consider predicates with a set operator like IN or NOT IN then it's a more powerful construct, as in:

    (a, b, c) in (select x, y, z...)
(a, b, c) not in (select x, y, z...)

  • When the predicate includes a tuple inequality. This is specially useful in query pagination. For example:

    (a, b, c) >= (x, y, z)

    without tuples it the syntax would be much more verbose, as in:

    a > x OR a = x AND (b > y OR b = y AND c >= z)
user_64361910
Related