bytes
tutorials
sql
types of subqueries in sql
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:
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.
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:
Sale ID | Customer ID | Product ID | Sale Amount |
---|---|---|---|
1 | 1001 | 1 | 100 |
2 | 1002 | 2 | 200 |
3 | 1001 | 3 | 150 |
4 | 1003 | 2 | 175 |
5 | 1002 | 1 | 125 |
Customers Table:
Customer ID | Customer Name |
---|---|
1001 | John Smith |
1002 | Jane Doe |
1003 | Mike Johnson |
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.
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
Quiz
Answer: a. A query that is nested inside another query.
Answer: b. A subquery that references one or more columns from the outer query.
Answer: a. A subquery that is evaluated independently of the outer query.
Answer: a. Correlated subquery.
Answer: b. When you need to filter data based on values in related tables.