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. 
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.
There are some rules which are followed while using subqueries. They are:

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.

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

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.

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.

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.

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!
Software Development Course typically range from a few weeks to several months, with fees varying based on program and institution.
Cohort Starts: 15 Apr, 2024
Cohort Starts: 16 Feb, 2024
Cohort Starts: 28 Feb, 2024
Cohort Starts: 19 Mar, 2024
Business Intelligence Career Guide: Your Complete Guide to Becoming a Business Analyst
Business Analyst Interview Questions
How to Become a Business Analyst
Data Analyst Resume Guide
Role of a Business Analyst
Top Business Analyst Skills
© 2009 -2024- Simplilearn Solutions
Follow us!
Company
Work with us
Discover
For Businesses
Learn On the Go!
Trending Post Graduate Programs
Trending Bootcamp Programs
Trending Master Programs
Trending Courses
Trending Categories
Trending Resources

source