A subquery in sql is a query that is nested inside another query, also known as an inner query. It is used to retrieve data that will be used as input for the main query, also known as the outer query.
The inner query is executed first and returns a temporary result set that is used by the outer query. Subqueries can be used in various parts of SQL statements such as the SELECT clause, FROM clause, WHERE clause, and HAVING clause.
Syntax of a subquery:
SELECT column_name(s) FROM table_name WHERE column_name operator (SELECT column_name FROM table_name WHERE condition);
operator can be any comparison operator such as
Let’s consider an example to understand the use of subqueries. We have two tables
customers table has columns
orders table has columns
The task is to find the names of customers who have placed an order.
SELECT customerName FROM customers WHERE customerID IN (SELECT customerID FROM orders);
The inner query retrieves the
customerID of customers who have placed an order. The outer query then retrieves the names of these customers by using the
IN operator and the result of the inner query. The result of this query would be a list of customer names who have placed an order.
Another example of a subquery is to find the average salary of employees who work in departments with more than 5 employees.
SELECT AVG(salary) FROM employees WHERE departmentID IN (SELECT departmentID FROM employees GROUP BY departmentID HAVING COUNT(*) > 5);
Here, the inner query retrieves the
departmentID of departments with more than 5 employees by using the
GROUP BY clause and the
HAVING clause. The outer query then retrieves the average salary of employees who work in these departments.
It’s important to note that subqueries can also be nested, meaning that one subquery can be used within another subquery. The result of an inner query can be used as input for another inner query, and so on. The most outer query is then used to retrieve the final result.
In conclusion, subqueries can greatly simplify complex SQL queries by allowing us to break down a large query into smaller, more manageable pieces. They can be used to retrieve data from multiple tables or to retrieve data based on conditions that are dependent on the result of another query.
Also check WHAT IS GIT ? It’s Easy If You Do It Smart
You can also visite the Git website (https://git-scm.com/)