Microsoft SQL Server Subqueries Subqueries
Example
A subquery is a query within another SQL query. A subquery is also called inner query or inner select and the statement containing a subquery is called an outer query or outer select.
Note
- Subqueries must be enclosed within parenthesis,
- An ORDER BY cannot be used in a subquery.
- The image type such as BLOB, array, text datatypes are not allowed in subqueries.
Subqueries can be used with select, insert, update and delete statement within where, from, select clause along with IN, comparison operators, etc.
We have a table named ITCompanyInNepal on which we will perform queries to show subqueries examples:
Examples: SubQueries With Select Statement
with In operator and where clause:
SELECT *
FROM ITCompanyInNepal
WHERE Headquarter IN (SELECT Headquarter
FROM ITCompanyInNepal
WHERE Headquarter = 'USA');
with comparison operator and where clause
SELECT *
FROM ITCompanyInNepal
WHERE NumberOfEmployee < (SELECT AVG(NumberOfEmployee)
FROM ITCompanyInNepal
)
with select clause
SELECT CompanyName,
CompanyAddress,
Headquarter,
(Select SUM(NumberOfEmployee)
FROM ITCompanyInNepal
Where Headquarter = 'USA') AS TotalEmployeeHiredByUSAInKathmandu
FROM ITCompanyInNepal
WHERE CompanyAddress = 'Kathmandu' AND Headquarter = 'USA'
Subqueries with insert statement
We have to insert data from IndianCompany table to ITCompanyInNepal. The table for IndianCompany is shown below:
INSERT INTO ITCompanyInNepal
SELECT *
FROM IndianCompany
Subqueries with update statement
Suppose all the companies whose headquarter is USA decided to fire 50 employees from all US based companies of Nepal due to some change in policy of USA companies.
UPDATE ITCompanyInNepal
SET NumberOfEmployee = NumberOfEmployee - 50
WHERE Headquarter IN (SELECT Headquarter
FROM ITCompanyInNepal
WHERE Headquarter = 'USA')
Subqueries with Delete Statement
Suppose all the companies whose headquarter is Denmark decided to shutdown their companies from Nepal.
DELETE FROM ITCompanyInNepal
WHERE Headquarter IN (SELECT Headquarter
FROM ITCompanyInNepal
WHERE Headquarter = 'Denmark')
