A SQL subquery as the term suggested is a query nested within another query. These subqueries can be present in the FROM clause, WHERE clause, or the SELECT clause. Subqueries are a powerful tool for combining data available in two tables into a single result.
What is a SQL Subquery?
An SQL subquery is nothing but a query inside another query. We use a subquery to fetch data from two tables. A subquery is often also referred to as an inner query, while the statement containing a subquery is also called an outer query or outer select. We can implement subqueries with the SELECT, INSERT, UPDATE, and DELETE statements along with the operators like =, <, >, >=, <=, IN, BETWEEN, etc.
General Rules of Subqueries
There are some rules which are followed while using subqueries. They are:
- A subquery must be enclosed in parentheses.
- Subqueries that return over one row can only be used with multiple value operators such as the IN operator.
- SQL Server allows you to nest subqueries up to 32 levels.
Subqueries With SELECT Statement
Subqueries are commonly used with the SELECT statement. The basic form is shown below -
(SELECT [DISTINCT] subquery_select_argument
FROM {table_name | view_name}
{table_name | view_name} ...
[WHERE search_conditions]
[GROUP BY aggregate_expression [, aggregate_expression] ...]
[HAVING search_conditions])
Example:
Consider the ‘products’ table having the following records.
Now, let us implement the following SQL subquery with SELECT statement.
This will produce the following result.
Subqueries With FROM Statement
A FROM clause is used to specify the subquery statement in SQL.
Note: The result of the evaluation is stored in a temporary variable.
Example:
The following SQL query fetches the result where quantity in stock from products_bkp table where the quantity in stock is greater than average quantity in stock
Subqueries With an INSERT statement
Consider the table ‘products_bkp’ with a similar structure to the ‘products’ table.
Now, to copy the complete ‘products’ table into the ‘products_bkp’ table, you can use the SQL subquery with INSERT statement in the following way.
This will copy the data into the ‘products_bkp’ table.
Subqueries With an UPDATE Statement
You can set a new column value in an UPDATE statement equal to the result returned by a single row subquery.
Example:
If you want to update the unit price of products_bkp table where the unit price will be greater than the minimum unit price from the products table, we can use the following SQL query:
This will produce the following result.
Subqueries With the DELETE Statement
We can use the DELETE statement with subqueries to delete the records.
Example:
The following SQL query will delete the data from the products_bkp table where the unit price is less than the maximum unit price from the table products.
This will produce the following result.
Gain expertise in the latest Business analytics tools and techniques with the Post Graduate Program in Business Analysis. Enroll now!
Conclusion
In this article, we learned about SQL subqueries and how to use subqueries to form flexible SQL statements. We worked on several examples to understand how we can combine two statements to get the best result from a subquery.
If you wish to learn more about MySQL, then check out our SQL certification course.
This SQL certification course provides you with a very thorough understanding of all you need to begin working with SQL databases in your applications. The course will enable you to correctly structure your database, author efficient, and error-free SQL statements and clauses, and manage SQL databases for scalable growth.
If you have any questions, ask them in our comments section of this article. Our experts will answer them for you promptly!