- 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’ );
- CHAR (n), TEXT (n), VARCHAR (n).
Alphanumeric data either fixed at n symbols or up to n symbols. It’s not possible to do arithmetic on this data.
- REAL, FLOAT, NUMBER, NUMERIC and DECIMAL.
These are numbers with decimal places.
- INTEGER, LONG, INT, SMALLINT.
These are all whole numbers. They vary in the how big a number they can hold.
- MONEY, CURRENCY.
These are numeric types with decimal places for holding monetary values.
- BINARY, LONGBINARY, GENERAL, IMAGE, OLEOBJECT.
These can hold complete files, such as pictures or media. They are not of a fixed in size, and the upper limit on their size is large.
- DATE, TIME, DATETIME.
These can hold date and time values. These are hybrid types. Although you think of them as text type fields, it is possible to do arithmetic and numeric comparisons on them.