SQL ALL operator in DB2 for i SQL

SQL ALL operator in DB2 for i SQL

ALL operator

The SQL ALL operator allows us to perform a comparison between a single column value or a range of other values.

ALL operator is used to return all records of the SELECT statement.

The result of the ALL operator is true only if the condition satisfies all values in the range.

It returns a Boolean value TRUE when the subquery does not return any row.

ALL syntax

ALL Syntax With SELECT

SELECT ALL column_name(s)
FROM table_name
WHERE condition

ALL Syntax With WHERE or HAVING

ColumnName Operator ALL (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 ALL
  (SELECT column_name
  FROM table_name
  WHERE condition)

Examples using SQL ALL operator

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

Select ALL the students from the student table

STUDENT table data:

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

ALL Query

SELECT ALL *
FROM student
WHERE TRUE  

Output:

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

As the condition will always be TRUE so all the student get selected from student table.

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

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 = ALL           
  (SELECT subjectid             
  FROM subject                  
  WHERE subjectname = 'PHYSICS')

Output:

ROLLNO   NAME                      SUBJECTID
     1   Ankur                           201

First It executes the Sub-select query and select those subjectid's from subject table where subjectname is "PHYSICS". Therefore it selects SubjectId 201 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) ALL of these subject id returned from the Sub-select query. Therefore, in this case both subjectid 201 available in the student table and hence, 1 row get retrieved and displayed.

Returns all the student from student table using ALL statement in subquery where subject = 'COMPUTER'

SELECT *                         
FROM student                     
WHERE subjectid = ALL            
  (SELECT subjectid              
  FROM subject                   
  WHERE subjectname = 'COMPUTER')

There are no records selected in subquery as COMPUTER subject is not available in SUBJECT table and hence ALL (subquery) does not return any row which returns TRUE and that going to select all the records in the STUDENT table.

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