Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Is it possible to compare tuples in oracle-compatible sql?

Tags:

sql

oracle

tuples

I'm not 100% if tuples is the term for what I'm talking about but I'm looking at something like this:

Table grades
user    grade
------------
Jim     B
Bill    C
Tim     A
Jim     B+

I know I can do:

SELECT COUNT(*)
FROM grades
WHERE (
   (user = 'Jim' AND grade = 'B')
   OR (user = 'Tim' AND grade = 'C')
);

But is there a way to do something more like this?

SELECT COUNT(*)
    FROM grades
    WHERE (user, grade) IN (('Jim','B'), ('Tim','C'));

EDIT: As a side note, I'd only tested with:

(user, grade) = ('Tim','C')

And that fails, so I assumed IN would fail as well, but I was wrong (thankfully!).

like image 922
user126715 Avatar asked Mar 24 '11 20:03

user126715


1 Answers

The query you posted should be valid syntax

SQL> ed
Wrote file afiedt.buf

  1  with grades as (
  2    select 'Jim' usr, 'B' grade from dual
  3    union all
  4    select 'Bill', 'C' from dual
  5    union all
  6    select 'Tim', 'A' from dual
  7    union all
  8    select 'Jim', 'B+' from dual
  9  )
 10  select *
 11    from grades
 12   where (usr,grade) in (('Jim','B'),
 13                         ('Tim','C'),
 14*                        ('Tim','A'))
SQL> /

USR  GR
---- --
Jim  B
Tim  A
like image 98
Justin Cave Avatar answered Nov 03 '22 01:11

Justin Cave