SQL Sub Query

It is basically a query within another query. The outer query is called as main query and inner query is called subquery. SubQuery is always executed first, and the result of subquery is passed on the main query.

A nested query is also known as a subquery.

There are two types of subquery – Correlated and Non-Correlated.

• A subquery can use values from the outer query, in that case it is known as a correlated subquery.

• While non-correlated subquery can be considered as independent query and the output of subquery are substituted in the main query.

---

1. Sub queries with SELECT Statements:

Sub queries with select statements are most frequently used. Now consider the following example:

EMPLOYEE table:

The subquery with a SELECT statement will be:

SELECT * FROM employee
WHERE id IN (SELECT id FROM employee WHERE salary > 15000);

After executing the above query we will get the following result:

---

2. Sub queries with INSERT Statement:

In insert statement, data returned from the subquery is used to insert into the another table.

Example:

Consider a table 'employee_extra' which is similar as 'employee'.

Now we can use following query to copy the complete 'employee' table into the 'employee_extra' table.

INSERT INTO employee_extra 
SELECT * FROM employee;

After executing the above query we will get the following result:

NOTE:- First you have to create table2 in which you have to copy table1 with exact same details.

---

3. Sub queries with the UPDATE Statement:

When a sub query is used with the Update statement, then either single or multiple columns in the table is updated.

Example:

UPDATE employee
SET salary = salary* 0.5
WHERE age IN (SELECT age FROM employee_extra WHERE age >= 29);

After executing the above query we will get the following result:

---

4. Sub queries with the DELETE Statement:

Delete statement in Sub query can be used just like other statements discussed above.

Example:

DELETE FROM employee
WHERE age IN (SELECT age FROM employee_extra WHERE age >= 29);

After executing the above query we will get the following result:

---

Miscellaneous:

• Sub query can be placed in a number of SQL clauses like WHERE clause, FROM clause, HAVING clause.

• You can use Sub query with SELECT, UPDATE, INSERT, DELETE statements along with the operators like =, <, >, >=, <=, IN, BETWEEN, etc.

• ORDER BY command cannot be used in subquery. GROUP BY command can be used instead of it.

SELECT DEPTNO,MIN(SAL)
FROM EMP 
GROUP BY DEPTNO
HAVING MIN(SAL) < 
(SELECT MIN(SAL) FROM EMP WHERE DEPTNO = 10); 

• But sub-queries have performance issues.  Using a join instead of a sub-query can at times give you up to 500 times performance boost.

• Another problem with Sub query is if the Inner Query returns NULL to the Outer Query the Result of the QUERY is NULL.

• Inline View: When we use SQL Sub query in the FROM clause of the SELECT statement it is called as an Inline View. In - Line Views are used to simplify complex queries by removing join operations and filtering several separate queries into a single query.

SELECT *
FROM emp e,(SELECT deptno FROM dept) d
WHERE e.deptno = d.deptno;

---

Thanks for visiting us. And get in touch with us by signing up.

#programming #sql #blog #subquery