A subquery is a query within a query. These sub queries are created with SQL statements. These subqueries are embedded within SELECT, FROM or the WHERE clause.

Example: Displays employees from employee table with bonus > 1000. Using IN first all employees are selected and compared to each row of the subquery.

Select first_name from employee Where bonus_id IN (select id from bonus Where bonus_amt > 1000);

What is corrected subquery? What is its purpose?

A query which uses values from the outer query is called as a correlated sub query. The sub query is executed once and uses the results for all the evaluations in the outer query

Purpose:
Many queries can be evaluated by executing the sub query once

What are the main advantages of subqueries?

Advantage of subqueries

The intermediate results need not be stored in the memory.
They allow a structured way of querying the database.
Subqueries provide different ways to perform complex operations

Subqueries with ANY, IN, and SOME

Sub Query Example with IN: Displays employees from employee table with bonus > 1000. Using IN first all employees are selected and compared to each row of the subquery.

Select first_name from employee
Where bonus_id IN (select id from bonus
Where bonus_amt > 1000);

Sub Query Example with ANY: The keyword ANY should be used after a comparison operator. It returns TRUE for ANY of the values in the column that the subquery returns.

Example:
SELECT col_1 from table1 WHERE col_1 ANY (SELECT col_1 FROM table2);

Sub Query Example with SOME: SOME is similar to ANY. Use of SOME is rare. However, to avoid any confusion in understanding the query, SOME is used.

Example:
SELECT col_1 from table1 WHERE col_1 SOME (SELECT col_1 FROM table2);

Example:
SELECT col_1 from table1 WHERE col_1 ANY (SELECT col_1 FROM table2);

Subqueries with ALL

The keyword ALL should be used after a comparison operator. It returns TRUE if the comparison is TRUE for ALL of the values in the column that the subquery returns.

Example:

SELECT col_1 from table1 WHERE col_1 ALL (SELECT col_1 FROM table2);

Subqueries with EXISTS and NOT EXISTS

A subquery with Exist does not really return any data; it returns TRUE or FALSE.

Example: This select statement will return all records from the sales table where there is at least one record in the orders table with the same sales _id.

SELECT *
FROM sales
WHERE EXISTS
(select *
from orders
where sales.sales_id = orders.sales_id);

Example for NOT EXIST: This query will work exactly the opposite to above. I.e except for the sane sales_id all other records will be returned

SELECT *
FROM sales
WHERE NOT EXISTS
(select *
from orders
where sales.sales_id = orders.sales_id);

What are the tricks to optimize Subqueries?

When a MySQL statement has a sub query, it is executed once for every row returned by the outer sub query. To execute the sub query faster, it must have an index. Every column in the sub query is referenced if a concatenated index is used.