subquery in SQL Server | subquery Command in SQL - sql - sql tutorial - learn sql

• A Subquery or Inner query or a Nested query is a query within another SQL query and embedded within the WHERE clause.
• A subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved.
• Subqueries can be used with the SELECT, INSERT, UPDATE, and DELETE statements along with the operators like =, <, >, >=, <=, IN, BETWEEN, etc.
• A subquery is a query that is nested inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another subquery.
• A subquery can be used anywhere an expression is allowed.
• In this example, a subquery is used as a column expression named Max Unit Price in a SELECT statement.
• A subquery is a SQL statement that has another SQL query embedded in the WHERE or the HAVING clause.

sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

Syntax

• The syntax for a subquery when the embedded SQL statement is part of the WHERE condition is as follows:

SELECT "column_name1"
FROM "table_name1"
WHERE "column_name2" [Comparison Operator]
(SELECT "column_name3"
FROM "table_name2"
WHERE "condition");

• [Comparison Operator] could be equality operators such as =, >, <, >=, <=. It can also be a text operator such as "LIKE".
• The portion in red is considered as the "inner query," while the portion in green is considered as the "outer query."

sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

Syntax Examples

• We use the following tables for our examples.

Table product_Information

product_Name	Sales	Txn_Date
Los Angeles	1500	Jan-05-1999
San Diego	250	Jan-07-1999
Los Angeles	300	Jan-08-1999
Boston	700	Jan-08-1999

Table Geography

Region_Name	Product_Name
East	Boston
East	New York
West	Los Angeles
West	San Diego

sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

Example 1: Simple subquery

• To use a subquery to find the sales of all stores in the West region, we use the following SQL statement:

SELECT SUM (Sales) FROM product_Information
WHERE product _Name IN
(SELECT product _Name FROM Geography
WHERE product _Name = 'West');

sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

Result:

SUM (Sales)
2050

• In this example, instead of joining the two tables directly and then adding up only the sales amount for stores in the West region, we first use the subquery to find out which stores are in the West region, and then we sum up the sales amount for these stores.
• Notice that in this example, the inner query and the outer query are independent of each other. This type of subquery is called a simple subquery.

sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

Example 2: Correlated subquery

• If the inner query is dependent on the outer query, we will have a correlated subquery. An example of a correlated subquery is shown below:

SELECT SUM (a1.Sales) FROM product _Information a1
WHERE a1. product _Name IN
(SELECT product _Name FROM Geography a2
WHERE a2. product _Name = a1.Store_Name);

sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

Result:

SUM (Sales)
2750

• Here, the inner query is used to make sure that SQL only sums up sales amount from stores that appear in both the Store _Information and the Geography tables.
• Notice the WHERE clause in the inner query, where the condition involves a table from the outer query.

This tutorial provides more the basic needs and informations on sql tutorial , pl sql tutorial , mysql tutorial , sql server , sqlcode , sql queries , sql , sql formatter , sql join , w3schools sql , oracle tutorial , mysql , pl sql , learn sql , sql tutorial for beginners , sql server tutorial , sql query tutorial , oracle sql tutorial , t sql tutorial , ms sql tutorial , database tutorial , sql tutorial point , oracle pl sql tutorial , oracle database tutorial , oracle tutorial for beginners , ms sql server tutorial , sql tutorial pdf