SQL ANY Clause

The SQL ANY clause allows you to compare a series of columns with some other value. In this example, the other value is a literal. I tested it on Oracle 11.1.0.6.0. First I created a table showing what I ate for each meal on a given day:

SQL> create table meals
  2  (day       varchar2(3),
  3   breakfast varchar2(10),
  4   lunch varchar2(10),
  5   dinner    varchar2(10))
  6  /

Table created.

SQL>

Then I added some imaginary data (I don't really eat nut roast):

SQL> insert into meals values
  2  ('MON', 'WEETABIX', 'PIZZA', 'KIPPERS')
  3  /

1 row created.

SQL> insert into meals values
  2  ('TUE', 'CORNFLAKES', 'LASAGNE', 'BEEF')
  3  /

1 row created.

SQL> insert into meals values
  2  ('WED', 'PORRIDGE', 'LAMB', 'NUT ROAST')
  3  /

1 row created.

SQL> select * from meals
  2  /

DAY BREAKFAST  LUNCH      DINNER
--- ---------- ---------- ----------
MON WEETABIX   PIZZA      KIPPERS
TUE CORNFLAKES LASAGNE    BEEF
WED PORRIDGE   LAMB       NUT ROAST

SQL>

Finally, I used the ANY clause to check whether I ate a particular food for breakfast, lunch or dinner:

SQL> select * from meals
  2  where 'WEETABIX' = any(breakfast, lunch, dinner)
  3  /

DAY BREAKFAST  LUNCH      DINNER
--- ---------- ---------- ----------
MON WEETABIX   PIZZA      KIPPERS

SQL> select * from meals
  2  where 'LASAGNE' = any(breakfast, lunch, dinner)
  3  /

DAY BREAKFAST  LUNCH      DINNER
--- ---------- ---------- ----------
TUE CORNFLAKES LASAGNE    BEEF

SQL> select * from meals
  2  where 'NUT ROAST' = any(breakfast, lunch, dinner)
  3  /

DAY BREAKFAST  LUNCH      DINNER
--- ---------- ---------- ----------
WED PORRIDGE   LAMB       NUT ROAST

SQL>