Types of Sub-query

Overview

A subquery could be a query that's settled inside another query, and it can be utilized to recover data that will be utilized within the main query. There are two sub-queries which we'll be learning in this lesson.

The two main types of subqueries are:

1. Correlated subquery:

A correlated subquery is a type of SQL subquery evaluated for each row of the outer query. The subquery references one or more columns from the outer query and uses these values to filter its result set. The result set of the subquery is then used to evaluate the outer query.

In other words, a correlated subquery is a subquery that is dependent on the outer query. It cannot be evaluated independently, and its result set is influenced by the values of the outer query.

Here's an example of a correlated subquery:

SELECT *
FROM employees e
WHERE e.salary > (
    SELECT AVG(salary)
    FROM employees
    WHERE department = e.department
);

In this example, the subquery is correlated because it references the department column from the outer query. The subquery calculates the average salary of employees in the same department as the current row of the outer query. This result is then compared to the current employee's salary in the outer query. The query returns all employees whose salary is higher than the average salary of their department.

Correlated subqueries can be slower than non-correlated subqueries because they must be evaluated once for each row in the outer query. However, they can be very useful in certain situations, such as when filtering data based on values in related tables.

2. Non-correlated subquery:

A non-correlated subquery is a type of subquery in SQL that can be evaluated independently of the outer query. It does not reference any columns from the outer query, and its result set can be calculated just once before the outer query is executed.

Here's an example of a non-correlated subquery:

SELECT * FROM employees 
WHERE salary > ( SELECT AVG(salary) FROM employees );

In this example, the subquery is non-correlated because it does not reference any columns from the outer query. The subquery calculates the average salary of all employees in the employees table. This result is then compared to each employee's salary in the outer query. The query returns all employees whose salary exceeds the average salary.

Non-correlated subqueries can be speedier than correlated subqueries since they only got to be evaluated once. They can be valuable in circumstances where you would like to calculate an esteem or total work based on the complete table, and after that filter, the result is set is based on this value.

In any case, it's imperative to note that non-correlated subqueries can sometimes be less adaptable than connected subqueries since they can't reference columns from the external inquiry. Subsequently, it's critical to utilize a suitable sort of subquery for your particular needs.

Execution order of Sub-Query

The execution order of a subquery depends on the type of subquery and its location in the main query. Let's consider an example of a sales and customer database to explain the execution order of subqueries.

Suppose we have two tables, one for sales and another for customers. The sales table contains information about sales transactions, including the customer ID, product ID, and sale amount. The customers table contains information about the customers, including the customer ID and customer name.

Sales Table:

Customers Table:

1. Correlated Subquery:

A correlated subquery is a subquery that references a column from the outer query. It is executed once for each row returned by the outer query.

For example, let's say we want to retrieve the total sales amount for each customer. We can use a correlated subquery in the main query as follows:

SELECT Customer_Name, (SELECT SUM(Sale_Amount) FROM Sales WHERE 
Sales.Customer_ID = Customers.Customer_ID) as Total_Sales FROM Customers;

In this example, the subquery is executed for each customer in the outer query. For each customer, the subquery retrieves the sales amounts from the sales table where the customer ID matches the customer ID in the outer query. Then the subquery calculates the total sales amount for each customer, and the main query retrieves the customer name and total sales amount for each customer.

2. Non-Correlated Subquery:

A non-correlated subquery is a subquery that does not reference any column from the outer query. It is executed only once, and its result is used in the main query.

For example, let's say we want to retrieve the customer's name who made the highest sale. We can use a non-correlated subquery in the main query as follows:

SELECT Customer_Name, Sale_Amount FROM Customers, Sales WHERE 
Customers.Customer_ID = Sales.Customer_ID AND 
Sale_Amount = (SELECT MAX(Sale_Amount) FROM Sales);

In this example, the subquery is executed only once and retrieves the maximum sale amount from the sales table. Then, the main query retrieves the customer name and sale amount from the sales and customers tables where the customer ID matches the customer ID in the sales table, and the sale amount is equal to the maximum sale amount.

Conclusion

A subquery may be a query settled inside another query that recovers information utilized within the main query. There are two sorts of subqueries in SQL: correlated and non-correlated. Correlated subqueries are assessed for each outer query row and reference one or more columns from the outer query. Non-correlated subqueries can be assessed independently of the outer query and don't reference any columns from the outer query. The execution order of a subquery depends on the sort of subquery and its location within the main query.

Key Takeaways

1. There are two types of subqueries in SQL: correlated and non-correlated subqueries.
2. Correlated subqueries are evaluated for each row of the outer query and use one or more columns from the outer query to filter their own result set. The result set of the subquery is then used to evaluate the outer query.
3. Non-correlated subqueries can be evaluated independently of the outer query and do not reference any columns from the outer query. Their result set can be calculated once before the outer query is executed.
4. Correlated subqueries can be slower than non-correlated subqueries since they must be assessed once for each row within the outer query. They can be exceptionally valuable in certain situations, such as when you got to filter information based on values in related tables.
5. Non-correlated subqueries can be quicker than correlated subqueries since they only have to be assessed once. They can be valuable in circumstances where you wish to calculate an esteem or aggregate function based on the complete table and, after that filter, the result set based on this esteem.
6. The execution order of a subquery depends on the sort of subquery and its area within the main query. Correlated subqueries are executed for each push returned by the outer query. In contrast, non-correlated subqueries are executed as they were once, and their result is utilized within the main query.

Quiz

1. What is a subquery?  
   1. A query that is nested inside another query.  
   2. A query that joins two tables.
   3. A query that retrieves data from a single table.  
   4. None of the above.

Answer: a. A query that is nested inside another query.

2. What is a correlated subquery? 
   1. A subquery that is evaluated independently of the outer query.  
   2. A subquery that references one or more columns from the outer query. 
   3. A subquery that calculates the average value of a column. 
   4. None of the above.

Answer: b. A subquery that references one or more columns from the outer query.

3. What is a non-correlated subquery? 
   1. A subquery that is evaluated independently of the outer query.  
   2. A subquery that references one or more columns from the outer query. 
   3. A subquery that calculates the average value of a column. 
   4. None of the above.

Answer: a. A subquery that is evaluated independently of the outer query.

4. Which type of subquery can be slower? 
   1. Correlated subquery. 
   2. Non-correlated subquery.  
   3. Both are equally fast. 
   4. None of the above.

Answer: a. Correlated subquery.

5. When is a correlated subquery useful? 
   1. When you need to calculate a value or aggregate function based on the entire table. 
   2. When you need to filter data based on values in related tables. 
   3. When you need to retrieve data from a single table. 
   4. None of the above.

Answer: b. When you need to filter data based on values in related tables.