HOW TO TO HANDLE SUBQUERY USING IN, ANY,ALL ,EXIST IN SQL

Let's get a more understanding of using IN, ANY, ALL, and EXISTS in SQL. These are logical operators in SQL. Now the main problem is when to use these operators, at what condition do we have to use this operator?? So let's get to the understanding of each operator.

First we need a table to play with ,so below is the table with salaryDetail table columns (name,age ,salary ),EmployeeDetail table with columns(empid,age,empname)

1.IN

The IN operator checks a value within a set of values separated by commas and retrieves the rows from the table which are matching. The IN returns 1 when the search value presents within the range otherwise returns 0 .It operand on any set of values of the same data type.

Now let's practice the 'IN' operator:

1 . Get salary details whose age is in 25,27

select * from salaryDetail

where Age in (25,27)

so when this query is executed, the operator will check in the matching row in the salary table with people having age 25 ,27,it helps to avoid multiple 'OR' operators.

2. Get the employee id for people having a salary of 2000,6000

To solve this I will be using a subquery,

select EMPID from employeeDetail

where Age in (

select Age from salaryDetail

where Salary in (2000,6000)

)

So from the subquery it will check the table for people having salary 2000,6000.Then age(25,27) will taken from salarydetail table.Then mainquery will display the empid from employeedetail table by filtering the age using the subquery ,for the matching row for the age (23,27) .Output displayed will be (1,4) as the empid

2. ANY

ANY is true: if any of the sub-query values meet the condition.

ANY compares a value to each value in a list or results from a query and evaluates it to true if the result of an inner query contains at least one row. This operates value to a list or a single- column set of values

SYNTAX:

select column1,column2

from tablename

where column1 operator ANY

(select column1

from tablename

where condition);

operator can be =,<=,>=,!=,<,<>

For explanation let us consider a table salaryDetail

customers(Name, Age, Salary)

Consider a Query :

SELECT * FROM salaryDetail

WHERE Age > ANY( SELECT Age FROM salaryDetail

WHERE Salary > 5000)

The sub-query returns 23, 27 i.e, These are the ages for which salary is greater than 5000. The main query is compared with this, if any of the ages in table is greater than any of the values of sub-query(i.e the ages in table should be either greater than 23 or 27) . Now it returns (AMMU, 25, 2000) (ANU, 33, 4000) (KICHU , 27, 6000) (MEENU, 24, 4150).

1. List the employee details if any records in the salary detail table have salary = 4150

select * from employeeDetail

where AGE = any (

select Age from salaryDetail

where Salary = 4150)

2. List the employee details if any records in the salary detail table have salary >4500

select * from employeeDetail

where AGE = any (

select Age from salaryDetail

where Salary > 4500)

3.ALL

ALLis true, if all of the sub-query values meet the condition.ALL is used to select all records of a SELECT STATEMENT. It compares a value to every value in a list or results from a query. The ALL must be preceded by the comparison operators and evaluates to TRUE if the query returns no rows.

SYNTAX:

SELECT column1,column2

FROM tablename

WHERE column1 operator ALL

(SELECT column 1

FROM tablename

WHERE condition);

operator: <,>,<=,>=,!=,<>,=

Consider a Query :

SELECT * FROM SalaryDetail

WHERE Age > ALL( SELECT Age FROM SalaryDetail WHERE Salary > 5000)

The sub-query returns 23, 27 i.e, These are the ages for which salary is greater than 5000. The main query is compared with this, if any of the ages in table is greater than all of the values of sub-query(i.e the ages in table should be greater than both 23 and 27) . Now it returns(ANU, 33, 4000) .

1. List the employee details if all records in the salary detail table have salary = 6000

select * from employeeDetail

where AGE = any (

select Age from salaryDetail

where Salary = 6000)

2. List the employee details if all records in the salary detail table have salary > 4500

select * from employeeDetail

where AGE = any (

select Age from salaryDetail

where Salary > 4500)

4.EXISTS

The EXISTS checks the existence of a result of a Subquery. The EXISTS subquery tests whether a subquery fetches at least one row. When no data is returned then this operator returns 'FALSE'.

Exists is the same as any except for the time consumed will be less as, in ANY the query goes on executing where ever the condition is met and gives results. In case of EXISTS it first has to check throughout the table for all the records that match and then execute it.

To display the employee detail for the employee 'AMMU' if she is having salary details in the salaryDetail table

SELECT * FROM employeeDetail

WHERE EXISTS

(SELECT 1 FROM salaryDetails WHERE name='AMMU' )

• Usually, the SELECT command begins with SELECT * rather than a column name or list of expressions. Here, we are using the SELECT 1in its place of SELECT *, which helps us to enhance the query's performance in PostgreSQL.In PostgreSQL, the SQL commands we will use in the EXISTS condition are very ineffective. Therefore, in advance, the sub-query is RE-RUN for each row in the outer query's table.

• And we have more effective ways to write all the commands, which do not use the EXISTS condition.In PostgreSQL the EXISTS condition can combine with the SELECT, INSERT, UPDATE, and DELETE commands