- A sub-query is a query that is nested is nested inside a SELECT, INSERT, UPDATE or DELETE statement or inside another sub-query.
There are some guidelines to consider when using subqueries :
– A sub-query must be enclosed in parentheses.
– A sub-query must be placed on the right side of the comparison operator.
– Sub-queries cannot manipulate their results internally, therefore ORDER BY clause cannot be added in to a sub-query. You can use a ORDER BY clause in the main SELECT statement (outer query) which will be last clause.
– Use single-row operators with single-row sub-queries.
– If a sub-query (inner query) returns a null value to the outer query, the outer query will not return any rows when using certain comparison operators in a WHERE clause.
Types of Sub-query:-
A single row sub-query returns zero or one row to the outer SQL statement. You can place a sub-query in a WHERE clause, a HAVING clause, or a FROM clause of a SELECT statement.
- SELECT agent_name, agent_code, phone_no
- FROM agents
- WHERE agent_code =
- (SELECT agent_code
- FROM agents
- WHERE agent_name = ‘Alex’);
Multiple row sub-query returns one or more rows to the outer SQL statement. You may use the IN, ANY, or ALL operator in outer query to handle a sub-query that returns multiple rows.
- SELECT ord_num,ord_amount,ord_date,
- cust_code, agent_code
- FROM orders
- WHERE agent_code IN(
- SELECT agent_code FROM agents
Co-related Sub-query is a inner sub-query which is referenced by main outer query such that inner query is considered as being executed repeatedly.
FROM HumanResources.Employee e
WHERE e.ContactID IN
FROM Person.Contact c
WHERE MONTH(c.ModifiedDate) = MONTH(e.ModifiedDate)
Adding Subqueries to the SELECT Clause
You can add a subquery to a SELECT clause as a column expression in the SELECT list. The subquery must return a scalar (single) value for each row returned by the outer query. For example, in the following SELECT statement, I use a subquery to define the TotalQuantity column:
SELECT SalesOrderNumber, SubTotal, OrderDate, (
SELECT SUM(OrderQty) FROM Sales.SalesOrderDetail WHERE SalesOrderID = 43659 ) AS TotalQuantity FROM Sales.SalesOrderHeader WHERE SalesOrderID = 43659;
Adding Subqueries to the FROM Clause
The subquery examples in the previous section each return a single value, which they must do in order to be used in the SELECT clause. However, not all subquery results are limited in this way. A subquery can also be used in the FROM clause to return multiple rows and columns. The results returned by such a subquery are referred to as aderived table. A derived table is useful when you want to work with a subset of data from one or more tables without needing to create a view or temporary table. For instance, in the following example, I create a subquery that retrieves product subcategory information from the ProductSubcategory table, but only for those products that include the word “bike” in their name:
SELECT p.ProductID, p.Name AS ProductName, p.ProductSubcategoryID AS SubcategoryID,
ps.Name AS SubcategoryName FROM Production.Product p INNER JOIN ( SELECT ProductSubcategoryID, Name FROM Production.ProductSubcategory WHERE Name LIKE ‘%bikes%’ ) AS ps ON p.ProductSubcategoryID = ps.ProductSubcategoryID;
Adding Subqueries to the WHERE Clause
SELECT BusinessEntityID, FirstName, LastName FROM Person.Person WHERE BusinessEntityD = (SELECT BusinessEntityID FROM HumanResources.Employee WHERE NationalIDNumber = ‘895209680’ );