Structured Query Language 132022 Pierce College CIS 261

Structured Query Language 1/3/2022 Pierce College CIS 261 SQL 1

Module 9: Subqueries 1/3/2022 Pierce College CIS 261 SQL 2

Subqueries “SQL supports writing queries within queries, or nesting queries. The outermost query is a query whose result set is returned to the caller and is known as the outer query. The inner query is a query whose result is used by the outer query and is known as a subquery. The inner query acts in place of an expression that is based on constants or variables and is evaluated at run time. Unlike the results of expressions that use constants, the result of a subquery can change, because of changes in the queried tables. When you use subqueries, you avoid the need for separate steps in your solutions that store intermediate query results in variables. “ Microsoft SQL Server T-SQL Fundamentals, page 129 1/3/2022 Pierce College CIS 261 SQL 3

Subquery Basics “Every subquery has an outer query that it belongs to. Self-contained subqueries are subqueries that are independent of the outer query that they belong to. Correlated subqueries are subqueries that refer to attributes from the table that appears in the outer query. This means that the subquery is dependent on the outer query and cannot be invoked independently. A scalar subquery is a subquery that returns a single value—regardless of whether it is selfcontained. Such a subquery can appear anywhere in the outer query where a single-valued expression can appear (such as WHERE or SELECT). A multivalued subquery is a subquery that returns multiple values as a single column, regardless of whether the subquery is self-contained. Some predicates, such as the IN predicate, operate on a multivalued subquery. ” Microsoft SQL Server T-SQL Fundamentals, page 129 1/3/2022 Pierce College CIS 261 SQL 4

Subquery examples Declaring and setting a variable based on the result of a subquery: DECLARE @Min. Age date = (SELECT MAX(Birth. Date) FROM [Human. Resources]. [Employee]) SELECT @Min. Age AS Youngest. Employee. BD; 1/3/2022 Pierce College CIS 261 SQL 5

Subquery examples Declaring and setting a variable based on the result of a subquery: DECLARE @Min. Age date = (SELECT MAX(Birth. Date) FROM [Human. Resources]. [Employee]) SELECT @Min. Age AS Youngest. Employee. BD; 1/3/2022 Pierce College CIS 261 SQL 6

Subquery examples Declaring and setting a variable based on the result of a scalar value subquery, and then using that variable as part of a WHERE clause in another query: DECLARE @Max. Order. ID int = (SELECT MAX([Sales. Order. ID]) FROM [Sales]. [Sales. Order. Header]) SELECT [Sales. Order. ID], [Sales. Order. Number] FROM [Sales]. [Sales. Order. Header] WHERE [Sales. Order. ID] = @Max. Order. ID 1/3/2022 Pierce College CIS 261 SQL 7

Subquery examples Using a scalar value subquery in a WHERE clause: SELECT [Sales. Order. ID], [Sales. Order. Number] FROM [Sales]. [Sales. Order. Header] WHERE [Sales. Order. ID] = (SELECT MAX([Sales. Order. ID]) FROM [Sales]. [Sales. Order. Header]) Note that this returns EXACTLY the same results as the previous slide, without the overhead of the variable declaration. 1/3/2022 Pierce College CIS 261 SQL 8

Subquery examples Using a multi-value subquery in a WHERE clause: SELECT [Sales. Order. ID], [Sales. Order. Number] FROM [Sales]. [Sales. Order. Header] WHERE [Sales. Order. ID] IN (SELECT [Sales. Order. ID] FROM [Sales]. [Sales. Order. Header]) Note that this is actually quite redundant, because the query would have returned the same results with no WHERE clause. It is used for demonstration purposes. Note that SQL Server uses a DISTINCT clause in the subquery automatically when it sees performance value. 1/3/2022 Pierce College CIS 261 SQL 9

Subquery examples Using a CTE as a subquery in a WHERE clause: ; WITH CTE AS (SELECT DISTINCT Order. ID FROM Orders WHERE Customer. ID = 'VINET') SELECT * FROM ORDERS WHERE Order. ID IN (SELECT Order. ID FROM CTE) 1/3/2022 Pierce College CIS 261 SQL 10

Correlated Subquery example “Correlated subqueries are subqueries that refer to attributes from the table that appears in the outer query. This means that the subquery is dependent on the outer query and cannot be invoked independently. Logically, it’s as if the subquery is evaluated separately for each outer row. ” Microsoft SQL Server T-SQL Fundamentals SELECT * FROM Sales. Order. Header AS O 1 WHERE Sales. Order. ID =( SELECT MAX(O 2. Sales. Order. ID) FROM Sales. Order. Header AS O 2 WHERE O 2. Customer. ID = O 1. Customer. ID); 1/3/2022 Pierce College CIS 261 SQL 11

This work is licensed under a Creative Commons Attribution 4. 0 International License. Exceptions: 1) Materials identified as copyrighted or derived from another source. 2) Materials extracted from the Office of the National Coordinator (ONC) Health Information Technology Workforce Curriculum, which carries a more limited CC-BY SA license. Led by Bellevue College, the Health e. Workforce Consortium was formed to elevate Health Information Technology workforce development locally and nationally and provide career paths into this promising field for veterans and others. The nine-college consortium includes Bellevue College, Bellingham Technical College, Clark College, Clover Park Technical College, Northern Virginia Community College, Pierce College, Renton Technical College, Spokane Community College, and Whatcom Community College. The Health Information and Management Systems Society (HIMSS) is also a primary partner. This workforce solution is 100% funded by an $11. 7 m grant awarded by the U. S. Department of Labor's Employment and Training Administration. The solution was created by the grantee and does not necessarily reflect the official position of the U. S. Department of Labor. The Department of Labor makes no guarantees, warranties, or assurances of any kind, express or implied, with respect to such information, including any information on linked sites and including, but not limited to, accuracy of the information or its completeness, timeliness, usefulness, adequacy, continued availability or ownership.