Multi Row Subqueries in SQL

Multiple row subquery returns one or more rows to the outer SQL statement.

You may use the IN, ANY, or ALL operator in outer query to handle a subquery that returns multiple rows.

 

Using IN operator with a Multiple Row Subquery

IN operator is used to checking a value within a set of values. The list of values may come from the results returned by a subquery.

 

Example – Find the Employees Names who are working in departments IT , Marketing and Sales.

SQL> Select First_name from employees

          Where Department_Id IN (

                                                      Select Department_Id From Departments

                                                      Where Department_Name

                                                      IN (IT, Marketing, Sales));

 

Using NOT IN operator with a Multiple Row Subquery

NOT IN operator is used to check a value within a set of values. The list of values may come from the results returned by a subquery.

 

Example – Find the Employees Names who are not working in departments IT , Marketing and Sales.

SQL> Select First_name from employees

          Where Department_Id NOT IN (

                                                      Select Department_Id From Departments

                                                      Where Department_Name

                                                      IN (IT, Marketing, Sales));

 

Using ANY with a Multiple Row Subquery

You can use the ANY operator to compare a value with any value in a list. You must place an =, <>, >, <, <= or >= operator before ANY in your query. The following example uses ANY to check if any of the agent who belongs to the country ‘UK’.

 

Example –

SQL> SELECT Employee_Id, First_Name, Salary

          FROM Employees   

          WHERE Salary < ANY 

                         (SELECT Salary 

                          FROM Employees  

                          WHERE Job_Id = ‘IT_PROG’ ))

       AND  Job_Id <> ‘IT_PROG’; 

 

0 Comment

Leave a Reply