02 | advanced select statements brian alderman | mct, ceo / founder of microtechpoint tobias...
TRANSCRIPT
Click to edit Master subtitle style
02 | Advanced SELECT Statements
Brian Alderman | MCT, CEO / Founder of MicroTechPointTobias Ternstrom | Microsoft SQL Server Program Manager
Course Topics
Querying Microsoft SQL Server 2012 Jump Start
01 | Introducing SQL Server 2012 SQL Server types of statements; other SQL statement elements; basic SELECT statements
02 | Advanced SELECT Statements DISTINCT, Aliases, scalar functions and CASE, using JOIN and MERGE; Filtering and sorting data, NULL values
03 | SQL Server Data Types Introduce data types, data type usage, converting data types, understanding SQL Server function types
04 | Grouping and Aggregating Data Aggregate functions, GROUP BY and HAVING clauses, subqueries; self-contained, correlated, and EXISTS; Views, inline-table valued functions, and derived tables
| Lunch Break Eat, drink, and recharge for the afternoon session
Advanced SELECT clauses (DISTINCT, aliases, CASE, and scalar functions)Query multiple tables using JOIN statementsFiltering and sorting data
Module Overview
Advanced SELECT Clauses
Understanding DISTINCT
Specifies that only unique rows can appear in the result setRemoves duplicates based on column list results, not source tableProvides uniqueness across set of selected columnsRemoves rows already operated on by WHERE, HAVING, and GROUP BY clausesSome queries may improve performance by filtering out duplicates prior to execution of SELECT clause
SELECT DISTINCT syntax
SELECT DISTINCT <column list>
FROM <table or view>
SELECT DISTINCT <column list>
FROM <table or view>
SELECT DISTINCT StoreID FROM Sales.Customer; SELECT DISTINCT StoreID FROM Sales.Customer;
StoreID-------12345709021898710
StoreID-------12345709021898710
Using aliases to refer to columns
Column aliases using AS
Column aliases using =
Accidental column aliases
SELECT SalesOrderID, UnitPrice, OrderQty AS Quantity
FROM Sales.SalesOrderDetail;
SELECT SalesOrderID, UnitPrice, OrderQty AS Quantity
FROM Sales.SalesOrderDetail;
SELECT SalesOrderID, UnitPrice, Quantity = OrderQty
FROM Sales.SalesOrderDetail;
SELECT SalesOrderID, UnitPrice, Quantity = OrderQty
FROM Sales.SalesOrderDetail;
SELECT SalesOrderID, UnitPrice Quantity
FROM Sales.SalesOrderDetail;
SELECT SalesOrderID, UnitPrice Quantity
FROM Sales.SalesOrderDetail;
Using aliases to refer to tables
Create table aliases in the FROM clause using AS
Table aliases without AS
Using table aliases in the SELECT clause
SELECT SalesOrderID, ProductID
FROM Sales.SalesOrderDetail AS SalesOrders;
SELECT SalesOrderID, ProductID
FROM Sales.SalesOrderDetail AS SalesOrders;
SELECT SalesOrderID, ProductID
FROM Sales.SalesOrderDetail SalesOrders;
SELECT SalesOrderID, ProductID
FROM Sales.SalesOrderDetail SalesOrders;
SELECT SalesOrders.SalesOrderID, SalesOrders.ProductID
FROM Sales.SalesOrderDetail AS SalesOrders;
SELECT SalesOrders.SalesOrderID, SalesOrders.ProductID
FROM Sales.SalesOrderDetail AS SalesOrders;
T-SQL CASE expressions
Simple CASECompares one value to a list of possible values and returns first matchIf no match, returns value found in optional ELSE clauseIf no match and no ELSE, returns NULL
Searched CASEEvaluates a set of predicates, or logical expressionsReturns value found in THEN clause matching first expression that evaluates to TRUE
T-SQL CASE expressions return a single (scalar) valueCASE expressions may be used in:
SELECT column list (behaves as calculated column requiring an alias)WHERE or HAVING clausesORDER BY clause
Writing simple CASE expressions
SELECT ProductID, Name, ProductSubCategoryID, CASE ProductSubCategoryID WHEN 1 THEN 'Beverages' ELSE 'Unknown Category' ENDFROM Production.Product
SELECT ProductID, Name, ProductSubCategoryID, CASE ProductSubCategoryID WHEN 1 THEN 'Beverages' ELSE 'Unknown Category' ENDFROM Production.Product
Keyword Expression component
SELECT <select list>
CASE <value to compare>
WHEN <value to match>
THEN <result>
END N/A
FROM <table source>
Using basic SELECT clauses
Demo
JOIN Statements
Overview of JOIN types
JOIN types in FROM clause specify the operations performed on the virtual table:
Join Type Description
Cross Combines all rows in both tables (creates Cartesian product).
Inner Starts with Cartesian product; applies filter to match rows between tables based on predicate.
Outer Starts with Cartesian product; all rows from designated table preserved, matching rows from other table retrieved. Additional NULLs inserted as placeholders.
Understanding INNER JOINS
Returns only rows where a match is found in both tablesMatches rows based on attributes supplied in predicate
ON clause in SQL-92 syntax Why filter in ON clause?
Logical separation between filtering for purposes of JOIN and filtering results in WHERETypically no difference to query optimizer
If JOIN predicate operator is =, also known as equi-join
INNER JOIN Syntax
List tables in FROM Clause separated by JOIN operatorTable order does not matter, and aliases are preferred
SELECT SOH.SalesOrderID, SOH.OrderDate, SOD.ProductID, SOD.UnitPrice, SOD.OrderQtyFROM Sales.SalesOrderHeader AS SOH JOIN Sales.SalesOrderDetail AS SOD ON SOH.SalesOrderID = SOD.SalesOrderID;
SELECT SOH.SalesOrderID, SOH.OrderDate, SOD.ProductID, SOD.UnitPrice, SOD.OrderQtyFROM Sales.SalesOrderHeader AS SOH JOIN Sales.SalesOrderDetail AS SOD ON SOH.SalesOrderID = SOD.SalesOrderID;
FROM t1 JOIN t2 ON t1.column = t2.column
FROM t1 JOIN t2 ON t1.column = t2.column
Understanding OUTER JOINS
Returns all rows from one table and any matching rows from second tableOne table’s rows are “preserved”
Designated with LEFT, RIGHT, FULL keywordAll rows from preserved table output to result set
Matches from other table retrievedAdditional rows added to results for non-matched rows
NULLs added in place where attributes do not matchExample: Return all customers and for those who have placed orders, return order information. Customers without matching orders will display NULL for order details.
OUTER JOIN examplesCustomers that did not place orders:
SELECT CUST.CustomerID, CUST.StoreID, ORD.SalesOrderID, ORD.OrderDateFROM Sales.Customer AS CUSTLEFT OUTER JOIN Sales.SalesOrderHeader AS ORDON CUST.CustomerID = ORD.CustomerIDWHERE ORD.SalesOrderID IS NULL;
SELECT CUST.CustomerID, CUST.StoreID, ORD.SalesOrderID, ORD.OrderDateFROM Sales.Customer AS CUSTLEFT OUTER JOIN Sales.SalesOrderHeader AS ORDON CUST.CustomerID = ORD.CustomerIDWHERE ORD.SalesOrderID IS NULL;
Understanding CROSS JOINS
Combine each row from first table with each row from second tableAll possible combinations are displayed Logical foundation for inner and outer joins
INNER JOIN starts with Cartesian product, adds filterOUTER JOIN takes Cartesian output, filtered, adds back non-matching rows (with NULL placeholders)
Due to Cartesian product output, not typically a desired form of JOINSome useful exceptions:
Generating a table of numbers for testing
CROSS JOIN Example
Create test data by returning all combinations of two inputs:
SELECT EMP1.BusinessEntityID, EMP2.JobTitleFROM HumanResources.Employee AS EMP1 CROSS JOIN HumanResources.Employee AS EMP2;
SELECT EMP1.BusinessEntityID, EMP2.JobTitleFROM HumanResources.Employee AS EMP1 CROSS JOIN HumanResources.Employee AS EMP2;
Understanding Self-Joins
Why use self-joins?Compare rows in same table to each other
Create two instances of same table in FROM clauseAt least one alias required
Example: Return all employees and the name of the employee’s manager
Self-Join examplesReturn all employees with ID of employee’s manager when a manager exists (INNER JOIN):
Return all employees with ID of manager (OUTER JOIN). This will return NULL for the CEO:
SELECT EMP.EmpID, EMP.LastName, EMP.JobTitle, EMP.MgrID, MGR.LastNameFROM HR.Employees AS EMPLEFT OUTER JOIN HR.Employees AS MGR ON EMP.MgrID = MGR.EmpID ;
SELECT EMP.EmpID, EMP.LastName, EMP.JobTitle, EMP.MgrID, MGR.LastNameFROM HR.Employees AS EMPLEFT OUTER JOIN HR.Employees AS MGR ON EMP.MgrID = MGR.EmpID ;
SELECT EMP.EmpID, EMP.LastName, EMP.Title, MGR.MgrID
FROM HumanResources.Employee AS EMPLEFT OUTER JOIN HumanResources.Employee AS MGRON EMP.MgrID = MGR.EmpID;
SELECT EMP.EmpID, EMP.LastName, EMP.Title, MGR.MgrID
FROM HumanResources.Employee AS EMPLEFT OUTER JOIN HumanResources.Employee AS MGRON EMP.MgrID = MGR.EmpID;
Using JOINS to view data from multiple tables
Demo
Filtering and Sorting Data
Using the ORDER BY clause
ORDER BY sorts rows in results for presentation purposesUse of ORDER BY guarantees the sort order of the resultLast clause to be logically processedSorts all NULLs together
ORDER BY can refer to:Columns by name, alias or ordinal position (not recommended)Columns not part of SELECT list unless DISTINCT clause specified
Declare sort order with ASC or DESC
ORDER BY clause examples
ORDER BY with column names:
ORDER BY with column alias:
ORDER BY with descending order:
SELECT SalesOrderID, CustomerID, OrderDateFROM Sales.SalesOrderHeaderORDER BY OrderDate;
SELECT SalesOrderID, CustomerID, OrderDateFROM Sales.SalesOrderHeaderORDER BY OrderDate;
SELECT SalesOrderID, CustomerID, YEAR(OrderDate) AS OrderYearFROM Sales.SalesOrderHeaderORDER BY OrderYear;
SELECT SalesOrderID, CustomerID, YEAR(OrderDate) AS OrderYearFROM Sales.SalesOrderHeaderORDER BY OrderYear;
SELECT SalesOrderID, CustomerID, OrderDateFROM Sales.SalesOrderHeaderORDER BY OrderDate DESC;
SELECT SalesOrderID, CustomerID, OrderDateFROM Sales.SalesOrderHeaderORDER BY OrderDate DESC;
Filtering data in the WHERE clause
WHERE clauses use predicatesMust be expressed as logical conditionsOnly rows for which predicate evaluates to TRUE are acceptedValues of FALSE or UNKNOWN are filtered out
WHERE clause follows FROM, precedes other clausesCan’t see aliases declared in SELECT clause
Can be optimized by SQL Server to use indexes
WHERE clause syntax
Filter rows for customers in territory 6
Filter rows for orders in territories greater than or equal to 6
Filter orders within a range of dates
SELECT CustomerID, TerritoryIDFROM Sales.CustomerWHERE TerritoryID = 6;
SELECT CustomerID, TerritoryIDFROM Sales.CustomerWHERE TerritoryID = 6;
SELECT CustomerID, TerritoryIDFROM Sales.CustomerWHERE TerritoryID >= 6;
SELECT CustomerID, TerritoryIDFROM Sales.CustomerWHERE TerritoryID >= 6;
SELECT CustomerID, TerritoryID, StoreIDFROM Sales.CustomerWHERE StoreID >= 1000 AND StoreID <= 1200;
SELECT CustomerID, TerritoryID, StoreIDFROM Sales.CustomerWHERE StoreID >= 1000 AND StoreID <= 1200;
Filtering data in the SELECT clause
TOP allows you to limit the number or percentage of rows returned Works with ORDER BY clause to limit rows by sort order
If ORDER BY list is not unique, results are not deterministic (no single correct result set)Modify ORDER BY list to ensure uniqueness, or use TOP WITH TIES
Added to SELECT clause:SELECT TOP (N) | TOP (N) Percent
With percent, number of rows rounded upSELECT TOP (N) WITH TIES
Retrieve duplicates where applicable (nondeterministic)TOP is proprietary to Microsoft SQL Server
Filtering using TOP
Filter rows for customers to display top 20 TotalDue items
Filter rows for customers to display top 20 TotalDue items with ties
Filter rows for customers to display top 1% of TotalDue items
SELECT TOP (20) SalesOrderID, CustomerID, TotalDueFROM Sales.SalesOrderHeaderORDER BY TotalDue DESC;
SELECT TOP (20) SalesOrderID, CustomerID, TotalDueFROM Sales.SalesOrderHeaderORDER BY TotalDue DESC;
SELECT TOP (20) WITH TIES SalesOrderID, CustomerID, TotalDueFROM Sales.SalesOrderHeaderORDER BY TotalDue DESC;
SELECT TOP (20) WITH TIES SalesOrderID, CustomerID, TotalDueFROM Sales.SalesOrderHeaderORDER BY TotalDue DESC;
SELECT TOP (1) PERCENT SalesOrderID, CustomerID, TotalDueFROM Sales.SalesOrderHeaderORDER BY TotalDue DESC;
SELECT TOP (1) PERCENT SalesOrderID, CustomerID, TotalDueFROM Sales.SalesOrderHeaderORDER BY TotalDue DESC;
Handling NULL in queries
Different components of SQL Server handle NULL differently
Query filters (ON, WHERE, HAVING) filter out UNKNOWNsCHECK constraints accept UNKNOWNSORDER BY, DISTINCT treat NULLs as equals
Testing for NULLUse IS NULL or IS NOT NULL rather than = NULL or <> NULLSELECT CustomerID, StoreID, TerritoryID
FROM Sales.CustomerWHERE StoreID IS NULLORDER BY TerritoryID
SELECT CustomerID, StoreID, TerritoryIDFROM Sales.CustomerWHERE StoreID IS NULLORDER BY TerritoryID
Sorting and filtering data
Demo
Summary
The SELECT statement requires columns specified (* all columns) and the FROM clause to identify what view or table the rows of data are being pulled from
Clauses like DISTINCT provide control over what items are returned in the result set.
Aliases are used to define the names of columns or tables being referenced in the SELECT statement
CASE statements are used for performing comparisons in a list of values and returns first match
Summary
JOINS are used to display content from multiple tables in a single result set. INNER, OUTER, CROSS, and SELF JOINS can all be used to create the desire result set
ORDER BY is used to sort the rows returned in the result set
WHERE is used to filter the rows returned in the result set
The TOP clause is used to define the number of rows returned in the result set by specifying the number of rows or a percentage of rows returned.
©2013 Microsoft Corporation. All rights reserved. Microsoft, Windows, Office, Azure, System Center, Dynamics and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.