SQL ANY Operators in DB2 for i SQL

SQL ANY Operators in DB2 for i SQL

ANY operator

The SQL ANY operator allows us to perform a comparison between a single column value or a range of other values. It is used to verify if any single row of a sub-query satisfies the where condition.

It returns a boolean value. Returns TRUE if any of the subquery values meet the condition.

ANY syntax

ColumnName Operator ANY (SubQuery)

where, ColumnName is the name of the column in the main SELECT query.

Operator is a comparison operator such as =, <, >, <=, >=, or <>.

SubQuery is a SELECT statement that returns a single column of values.

SELECT column_name(s)
FROM table_name
WHERE column_name operator ANY
  (SELECT column_name
  FROM table_name
  WHERE condition)

Example using SQL ANY operator

Refer the Structure, data of STUDENT and SUBJECT table from here.

Find the Students from the STUDENT table if it finds ANY records in the SUBJECT table has taken subject as "PHYSICS" or "CHEMISTRY".

STUDENT table data:

ROLLNO   NAME                      SUBJECTID
     1   Ankur                           201
     2   Rahul                           202
     3   Raman                           203
     4   Vimal                           204
     5   Samar                           290

SUBJECT table data:

SUBJECTID   SUBJECTNAME           SUBJECTTEACHER
      201   PHYSICS               Anupam        
      202   CHEMISTRY             Nakul         
      203   MATHS                 Amit          
      204   BIOLOGY               Mohan         
      205   HINDI                 Venkatesh     
      206   ENGLISH               Venugopal     
      207   SANSKRIT              Krishnakant   

SELECT *                                        
FROM student                                    
WHERE subjectid = ANY                           
  (SELECT subjectid                             
  FROM subject                                  
  WHERE subjectname in ('PHYSICS','CHEMISTRY')) 

Output:

ROLLNO   NAME                      SUBJECTID
     1   Ankur                           201
     2   Rahul                           202

How SQL ANY works in above Example

First It executes the Sub-select query and select those subjectid's from subject table where subjectname is "PHYSICS" or "CHEMISTRY". Therefore it selects SubjectId 201 and 202 from subject table as per the available records in SUBJECT table.

Now, it executes the main select and select those rows from the student table where subject id equals(as per the used operator) ANY of these subject id returned from the Sub-select query. Therefore, in this case both subjectid 201 and 202 available in the student table and hence, both rows get retrieved and displayed.