Sub-query

  • A sub-query is a query that is nested is nested inside a SELECT, INSERT, UPDATE or DELETE statement or inside another sub-query.

Sub-query Guidelines:-

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.

  1. SELECT agent_name, agent_code, phone_no
  2. FROM agents
  3. WHERE agent_code =
  4. (SELECT agent_code
  5. FROM agents
  6. 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.

  1. SELECT ord_num,ord_amount,ord_date,
  2. cust_code, agent_code
  3. FROM orders
  4. WHERE agent_code IN(
  5. 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.

SELECT e.EmployeeID
FROM HumanResources.Employee e
WHERE e.ContactID IN
(
SELECT c.ContactID
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’  );

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s