Sub Queries
In this article let’s see how Sub Queries work.
What is a Sub Query?
*Sub Query is a query within a Query.
*This is also called as nested queries.
*Sub Query has 2 parts:
1)Outer Query
2)Inner Query
*The output of the inner query is become input of outer query.
*There are 2 types of sub queries:
1)Single row query <=,>=,|=
2)Multi row query IN,ANY,ALL
Example:
Query: Display employees whose salary is less than the salary of X .
Note: We have not mentioned the salary of the X, hence we should know the salary of X first.
Command:
SELECT SALARY FROM EMPLOYEES WHERE FIRST_NAME =’X’; — 100//X’s salary
SELECT SALARY FROM EMPLOYEES WHERE SALARY<100;
Instead of writing 2 queries we can write one query as:
Command:
SELECT SALARY FROM EMPLOYEES WHERE SALARY<(SELECT SALARY FROM EMPLOYEES WHERE FIRST_NAME =’X’); //Inner query will execute first.
Example 2:
Query: To know the 2nd maximum salary from employees table.
Command:
SELECT SALARY FROM EMPLOYEES;
SELECT MAX(SALARY) FROM EMPLOYEES; //gives the max salary
SELECT MAX(SALARY) FROM EMPLOYEES WHERE SALARY < (SELECT MAX(SALARY) FROM EMPLOYEES); //this gives the 2nd max salary from the table.
Also,
SELECT MAX(SALARY) FROM EMPLOYEES WHERE SALARY < SELECT MAX(SALARY) FROM EMPLOYEES WHERE SALARY < (SELECT MAX(SALARY) FROM EMPLOYEES)); //this gives 3rd max salary
Single row Sub Query:
Query:
1) Find the salary of the employee whose salary is greater than the salary of employee whose EMPLOYEE_ID is 150.
we can write this as:
Command:
SELECT SALARY FROM EMPLOYEE WHERE EMPLOYEE_ID=150; // salary of employee with id 150 is 1000
SELECT SALARY FROM EMPLOYEE WHERE SALARY>1000; //the output of the above query is input for this query.
Note: We have written 2 queries for one statement.Instead of this we can write the 1st statement as sub query as shown below.
Command:
SELECT SALARY FROM EMPLOYEES WHERE SALARY>(SELECT SALARY FROM EMPLOYEES WHERE EMPLOYEE_ID=150);
2)Display the employees who all are earning the highest salary.
We can write this as:
Command:
SELECT * FROM EMPLOYEE WHERE SALARY=(SELECT MAX(SALARY) FROM EMPLOYEES);
Multi row sub query: If the sub query is written in multiple rows and in multiple values that is multi row sub query.
Single row sun query: If inner query returns single value at a time then it is called single row sub query.
Multi Row Sub Query:
1)Display employees whose salary is equal to the salary of at least one employee in department id 30.
Command:
SELECT * FROM EMPLOYEE WHERE SALARY IN( SELECT SALARY FROM EMPLOYEE WHERE DEPARTMENT_ID=30);
2)Display the employees whose salary is greater than at least on employee in department id 30
Command:
SELECT * FROM EMPLOYEE WHERE SALARY>ANY (SELECT SALARY FROM EMPLOYEES WHERE DEPARTMENT_ID=30);
That is all about Sub queries. In the next part, we will discuss about Integrity Constraints in SQL
