{"id":988,"date":"2011-09-26T21:29:34","date_gmt":"2011-09-26T21:29:34","guid":{"rendered":"http:\/\/craftydba.com\/?p=988"},"modified":"2011-09-26T21:55:24","modified_gmt":"2011-09-26T21:55:24","slug":"selecting-data-part-4","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=988","title":{"rendered":"Selecting Data – Part 4"},"content":{"rendered":"

Today, I am continuing our exploration of the SELECT <\/a>reserved word by talking about sub-queries. A sub-query <\/a>is a query that is nested inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another sub-query. Sub-queries can be used anywhere an expression is allowed or can be nested inside another sub-query.<\/p>\n

I will be using the AdventureWorks <\/a>Sample database supplied by Microsoft during this talk.<\/p>\n

-- Use the correct database\r\nUSE AdventureWorks2008R2\r\nGO\r\n<\/span><\/pre>\n<\/p>\n
One usage a sub-query is with comparison operators <\/a>in the where clause.  The example below returns all the sales people by name and id who are above average sellers.<\/p>\n
-- All sales people who have above average sales - 8 rows\r\nSELECT \r\n    P.BusinessEntityID as Id, \r\n    cast(P.LastName as char(20)) as LastName,\r\n    cast(P.FirstName as char(20)) as FirstName\r\nFROM \r\n    Sales.SalesPerson S1 INNER JOIN Person.Person P\r\n    ON S1.BusinessEntityID = P.BusinessEntityID\r\nWHERE \r\n    S1.SalesYtd > (SELECT AVG(S2.SalesYTD) FROM Sales.SalesPerson S2)\r\n<\/span><\/pre>\n<\/p>\n
Another usage of a sub-query is with the IN <\/a>(inclusion) clause.  In short, the sub-query or inner select statement, returns a list of values in which column or field has to match one value in the list.  The example below shows all female sales agents.<\/p>\n
-- List all the female sales agents - 7 rows\r\nSELECT \r\n    P.BusinessEntityID as Id, \r\n    cast(P.LastName as char(20)) as LastName,\r\n    cast(P.FirstName as char(20)) as FirstName\r\nFROM \r\n    Person.Person P\r\nWHERE \r\n    P.PersonType = 'SP' AND\r\n    P.BusinessEntityID IN\r\n    ( Select BusinessEntityID from HumanResources.Employee Where Gender = 'F' )\r\n<\/span><\/pre>\n<\/p>\n
The opposite of inclusion is exclusion.  This condition is tested with the NOT IN <\/a>(exclusion) clause.  The example below shows all male sales agents.<\/p>\n
-- List all the male sales agents - 10 rows\r\nSELECT \r\n    P.BusinessEntityID as Id, \r\n    cast(P.LastName as char(20)) as LastName,\r\n    cast(P.FirstName as char(20)) as FirstName\r\nFROM \r\n    Person.Person P\r\nWHERE \r\n    P.PersonType = 'SP' AND\r\n    P.BusinessEntityID NOT IN\r\n    ( SELECT BusinessEntityID FROM HumanResources.Employee WHERE Gender = 'F' )\r\n<\/span><\/pre>\n<\/p>\n
The TSQL code below shows the number of distinct employees and total rate change records.  This information is relevant to the existence and non-existence query examples coming up later.<\/p>\n
-- Total distinct employees\r\nSELECT COUNT(DISTINCT H.BusinessEntityID) as TotalEmployees\r\nFROM HumanResources.EmployeePayHistory H \r\n\r\n-- Total rate change records\r\nSELECT COUNT(H.BusinessEntityID) AS TotalRateChgs\r\nFROM HumanResources.EmployeePayHistory H \r\n<\/span><\/pre>\n<\/p>\n
We are going to use the EXIST <\/a>clause to determine all employees who have had at least on rate change in the past.  Existence is determined by the correlated <\/a>sub-query that does not return a empty set, no rows.<\/p>\n
-- Show employees who have had rate changes - 13 rows\r\nSELECT \r\n    P.BusinessEntityID as Id, \r\n    cast(P.LastName as char(20)) as LastName,\r\n    cast(P.FirstName as char(20)) as FirstName\r\nFROM \r\n    Person.Person P\r\nWHERE EXISTS \r\n    (\r\n    SELECT COUNT(*) \r\n    FROM HumanResources.EmployeePayHistory H \r\n    WHERE P.BusinessEntityID = H.BusinessEntityID\r\n    HAVING COUNT(*) > 1\r\n    )\r\n<\/span><\/pre>\n<\/p>\n
The NOT EXIST <\/a>clause determines if a empty set is returned by the sub-query.  The next business problem is to find all the employees who have not had a rate change.   One would think that add the word NOT to the TSQL statement above would solve our problem.  However, it returns any non-matches including contacts who are not employees.  We have to add an additional EXISTS clause to weed out all the non-employees.<\/p>\n
-- Show employees who have had stayed at the same rate - 277 rows\r\nSELECT \r\n    P.BusinessEntityID as Id, \r\n    cast(P.LastName as char(20)) as LastName,\r\n    cast(P.FirstName as char(20)) as FirstName\r\nFROM \r\n    Person.Person P\r\nWHERE \r\n    -- Includes non-employees + employees with one record change\r\n    NOT EXISTS \r\n    (\r\n    SELECT COUNT(*) \r\n    FROM HumanResources.EmployeePayHistory H \r\n    WHERE P.BusinessEntityID = H.BusinessEntityID\r\n    HAVING COUNT(*) > 1\r\n    )\r\n    \r\n    -- Restrict to just employees\r\n    AND EXISTS \r\n    (\r\n    SELECT *\r\n    FROM HumanResources.EmployeePayHistory H \r\n    WHERE P.BusinessEntityID = H.BusinessEntityID\r\n    )\r\n<\/span><\/pre>\n<\/p>\n
Please note, queries that test exclusion or non existence always execute long than queries that test inclusion or existence.  The reason for this is that a condition is meet when the first match is fond.  On the other hand, all values have to be checked when testing for negativity.<\/p>\n
Sub-queries can be used any where an expression <\/a>can be used.  The following example shows the list price of mountain bikes, the average prices of a mountain bike, and the difference from the average price for the given mountain bike.  In short, two sub-queries are used as expressions.<\/p>\n
-- Show all mountain bikes, list, avg, and dif - 32 rows\r\nSELECT Name, ListPrice, \r\n(SELECT AVG(ListPrice) FROM Production.Product) AS Average, \r\n    ListPrice - (SELECT AVG(ListPrice) FROM Production.Product)\r\n    AS Difference\r\nFROM Production.Product\r\nWHERE ProductSubcategoryID = 1;\r\n<\/span><\/pre>\n<\/p>\n
The ANY<\/a>, SOME and ALL keywords were added to the SQL Server in the 2005 edition.  The following example finds all customers who have not been given a territory id which is assigned to a sales person.  This includes all rows in which territory id is null.<\/p>\n
-- Same thing as IN clause, returns 19820 customers are not assigned a territory\r\nSELECT COUNT(DISTINCT CustomerID) as Customers_Wo_Territory\r\nFROM Sales.Customer\r\nWHERE TerritoryID <> ANY (SELECT TerritoryID FROM Sales.SalesPerson);\r\n<\/span><\/pre>\n<\/p>\n
The SOME <\/a>is a great way to test for at least one condition is meet.  The Adventure Works database has 32 mountain bikes in its product line.  You have been tasked to find all Customer’s who have purchased a mountain bike and return their customer id.  The query below does just that.<\/p>\n
-- 4300 Unique Customers who order at least one mountain bike\r\nSELECT DISTINCT CustomerId FROM Sales.SalesOrderHeader H \r\nWHERE H.SalesOrderId IN\r\n( \r\n    -- 6185 Unique Orders\r\n    SELECT DISTINCT SalesOrderId FROM Sales.SalesOrderDetail D \r\n    WHERE D.ProductId = \r\n        -- 32 Mountain Bikes\r\n        SOME\r\n        ( SELECT ProductId FROM Production.Product where name like '%mountain-%' )\r\n)\r\n<\/span><\/pre>\n<\/p>\n
Another cool feature of subqueries is that they can be used with inserts, updates and deletes – data manipulation statements<\/a>.  <\/p>\n
Let\u2019s make believe that your manager wanted to have a fire sale on the mountain bikes to reduce stock.  He has asked you to reduce the price by 50 percent.  You begin the transaction, you update 32 records in the product table.  He comes by your desk within minutes and tells you that he wants to wait 2 weeks before making this change.  Therefore, you rollback the changes. The TSQL below executes the series of tasks.<\/p>\n
-- Used with INS, UPD, DEL\r\nBEGIN TRAN\r\nUPDATE Production.Product\r\nSET ListPrice = ListPrice \/ 2\r\nWHERE ProductID IN\r\n    (SELECT ProductID \r\n     FROM Production.Product\r\n     WHERE ProductSubcategoryID = 1);\r\nROLLBACK\r\n<\/span><\/pre>\n<\/p>\n
Last but not least, I am going to end the discussion with the ALL keyword.  I worked on this query for a while since real life examples lend themselves to using this keyword in a stored proceedure.  For instance, the books on line example show a stored proceedure called DaysToBuild which takes an @OrderID and @NumberOfDays as input.  If a order can be build in the number of days, a true statement is shown; otherwise a false statement is shown.<\/p>\n
Since this is not a real example of using the ALL <\/a>keyword in a select statement, I decided to introduce two powerful techniques.  First, common table expressions <\/a>(CTE’s) were added to SQL Server in the 2005 edition.  They return back a dynamic view of the data.  Second, a tally table <\/a>is a table that contains numbers 1 to n.  Armed with these two CTE’s, I am able to identify all the customers who have ordered 30 or more of mountain bikes.<\/p>\n
Again, this example is artificial since we could get the answer by adding a HAVING clause to the mountain bikes SELECT statement to return the results with one query.<\/p>\n
-- Count of mountain bike models by customer\r\nWITH cte_MOUNTAIN_BIKES(CustomerID, UniqueBikes)\r\nAS\r\n(\r\n-- 4300 rows\r\nSELECT H.CustomerID, COUNT(DISTINCT D.ProductId) AS UniqueBikes\r\nFROM Sales.SalesOrderHeader H \r\nJOIN Sales.SalesOrderDetail D on H.SalesOrderID = D.SalesOrderID\r\nJOIN Production.Product P ON D.ProductID = P.ProductID\r\nWHERE P.name like '%mountain-%' \r\nGROUP BY H.CustomerID\r\n),\r\n\r\n-- A tally table contains values 1 .. 30\r\ncte_TALLY_TABLE(n) AS\r\n(\r\nSELECT N\r\nFROM ( SELECT ROW_NUMBER() OVER ( ORDER BY SalesOrderID )\r\nFROM Sales.SalesOrderHeader ) D ( N )\r\nWHERE N < 30 \r\n)\r\n\r\n-- Return all customers (7 rows) who have ordered 30 or more different bikes-\r\nSELECT * FROM cte_MOUNTAIN_BIKES \r\nWHERE UniqueBikes >= ALL (SELECT N FROM cte_TALLY_TABLE)\r\nORDER BY UniqueBikes\r\nGO\r\n<\/span><\/pre>\n<\/p>\n
The lesson learn from today’s talk is that sub-queries are very powerful for selecting data.  The IN, EXIST, ANY, SOME and ALL keywords allow a SQL developer to test for all types of conditions in the result set.  Data manipulation queries such as INSERTS, UPDATES and DELETES can use sub-queries to pin point what rows to update.  Like views, deterministic sub-queries can be updatable but have rules <\/a>that need to be followed.<\/p>\n","protected":false},"excerpt":{"rendered":"
Today, I am continuing our exploration of the SELECT reserved word by talking about sub-queries. A sub-query is a query that is nested inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another sub-query. Sub-queries can be used anywhere an expression is allowed or can be nested inside another sub-query. I will be using the AdventureWorks Sample database supplied by Microsoft during this talk. — Use the correct database USE AdventureWorks2008R2 GO One usage a sub-query is with comparison operators in the where clause. The example below returns all…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9],"tags":[111,110,115,31,116,108,12,45,15,109,107,112,28,113,114,29],"class_list":["post-988","post","type-post","status-publish","format-standard","hentry","category-db-dev","tag-all","tag-any","tag-correlated-sub-query","tag-database-developer","tag-dml-with-sub-queries","tag-exists","tag-free-code","tag-in","tag-john-f-miner-iii","tag-not-exists","tag-not-in","tag-some","tag-sql-server","tag-sub-query","tag-sub-query-expression","tag-tsql"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/988","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=988"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/988\/revisions"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=988"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=988"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=988"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}