SQL ANY Operators in DB2 for i SQL

SQL ANY Operators in DB2 for i SQL
SQL ANY Operators in DB2 for i SQL, sql, sql tutorial, db2 for i sql, ibmi db2
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:

          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')) 


    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.
  • Post a Comment

    © AS400 and SQL Tricks. All rights reserved. Developed by Jago Desain