Multi Row Subqueries in SQL

Price: INR 8,999.00
Price: INR 15,999.00
Was: INR 16,999.00

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