02 | Advanced SELECT Statements
Download
1 / 34

Brian Alderman | MCT, CEO / Founder of MicroTechPoint - PowerPoint PPT Presentation


  • 111 Views
  • Uploaded on

02 | Advanced SELECT Statements. Brian Alderman | MCT, CEO / Founder of MicroTechPoint Tobias Ternstrom | Microsoft SQL Server Program Manager. Course Topics. Advanced SELECT clauses (DISTINCT, aliases, CASE, and scalar functions) Query multiple tables using JOIN statements

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' Brian Alderman | MCT, CEO / Founder of MicroTechPoint' - rania


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

02 | Advanced SELECT Statements

Brian Alderman | MCT, CEO / Founder of MicroTechPoint

Tobias Ternstrom | Microsoft SQL Server Program Manager



Module overview

Advanced SELECT clauses (DISTINCT, aliases, CASE, and scalar functions)

Query multiple tables using JOIN statements

Filtering and sorting data

Module Overview


Advanced SELECT functions)Clauses


Understanding distinct
Understanding DISTINCT functions)

  • Specifies that only unique rows can appear in the result set

  • Removes duplicates based on column list results, not source table

  • Provides uniqueness across set of selected columns

  • Removes rows already operated on by WHERE, HAVING, and GROUP BY clauses

  • Some queries may improve performance by filtering out duplicates prior to execution of SELECT clause


Select distinct syntax
SELECT functions) DISTINCT syntax

SELECT DISTINCT <column list>

FROM <table or view>

SELECT DISTINCTStoreID

FROMSales.Customer;

StoreID

-------

1234

570

902

1898

710


Using aliases to refer to columns
Using aliases to refer to columns functions)

  • Column aliases using AS

  • Column aliases using =

  • Accidental column aliases

SELECTSalesOrderID,UnitPrice,OrderQtyASQuantity

FROMSales.SalesOrderDetail;

SELECTSalesOrderID,UnitPrice,Quantity =OrderQty

FROMSales.SalesOrderDetail;

SELECTSalesOrderID,UnitPriceQuantity

FROMSales.SalesOrderDetail;


Using aliases to refer to tables
Using aliases to refer to tables functions)

  • 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.SalesOrderDetailASSalesOrders;

SELECT SalesOrderID, ProductID

FROMSales.SalesOrderDetailSalesOrders;

SELECT SalesOrders.SalesOrderID, SalesOrders.ProductID

FROMSales.SalesOrderDetailASSalesOrders;


T sql case expressions
T-SQL functions)CASE expressions

  • Simple CASE

    • Compares one value to a list of possible values and returns first match

    • If no match, returns value found in optional ELSE clause

    • If no match and no ELSE, returns NULL

  • Searched CASE

    • Evaluates a set of predicates, or logical expressions

    • Returns value found in THEN clause matching first expression that evaluates to TRUE

  • T-SQL CASE expressions return a single (scalar) value

  • CASE expressions may be used in:

    • SELECT column list (behaves as calculated column requiring an alias)

    • WHERE or HAVING clauses

    • ORDER BY clause


Writing simple case expressions
Writing simple functions) CASE expressions

  • SELECTProductID, Name, ProductSubCategoryID,

  • CASE ProductSubCategoryID

  • WHEN1 THEN'Beverages'

  • ELSE'Unknown Category'

  • END

  • FROMProduction.Product


Using basic select clauses

Using basic SELECT clauses


JOIN functions)Statements


Overview of join types
Overview of JOIN types functions)

  • JOIN types in FROM clause specify the operations performed on the virtual table:


Understanding inner joins
Understanding functions)INNER JOINS

  • Returns only rows where a match is found in both tables

  • Matches 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 WHERE

    • Typically no difference to query optimizer

  • If JOIN predicate operator is =, also known as equi-join


Inner join syntax
INNER JOIN Syntax functions)

  • List tables in FROM Clause separated by JOIN operator

  • Table order does not matter, and aliases are preferred

FROM t1 JOIN t2

ON t1.column = t2.column

SELECTSOH.SalesOrderID,

SOH.OrderDate,

SOD.ProductID,

SOD.UnitPrice,

SOD.OrderQty

FROMSales.SalesOrderHeaderASSOH

JOINSales.SalesOrderDetailAS SOD

ONSOH.SalesOrderID= SOD.SalesOrderID;


Understanding outer joins
Understanding functions) OUTER JOINS

  • Returns all rows from one table and any matching rows from second table

  • One table’s rows are “preserved”

    • Designated with LEFT, RIGHT, FULL keyword

    • All rows from preserved table output to result set

  • Matches from other table retrieved

  • Additional rows added to results for non-matched rows

    • NULLs added in place where attributes do not match

  • Example: 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 examples
OUTER JOIN examples functions)

  • Customers that did not place orders:

SELECTCUST.CustomerID, CUST.StoreID, ORD.SalesOrderID, ORD.OrderDate

FROMSales.CustomerAS CUST

LEFT OUTER JOIN Sales.SalesOrderHeaderAS ORD

ONCUST.CustomerID = ORD.CustomerID

WHEREORD.SalesOrderIDIS NULL;


Understanding cross joins
Understanding CROSS functions) JOINS

  • Combine each row from first table with each row from second table

  • All possible combinations are displayed

  • Logical foundation for inner and outer joins

    • INNER JOIN starts with Cartesian product, adds filter

    • OUTER JOIN takes Cartesian output, filtered, adds back non-matching rows (with NULL placeholders)

  • Due to Cartesian product output, not typically a desired form of JOIN

  • Some useful exceptions:

    • Generating a table of numbers for testing


Cross join example
CROSS JOIN Example functions)

  • Create test data by returning all combinations of two inputs:

SELECTEMP1.BusinessEntityID, EMP2.JobTitle

FROMHumanResources.EmployeeAS EMP1

CROSS JOIN HumanResources.EmployeeAS EMP2;


Understanding self joins
Understanding Self functions)-Joins

  • Why use self-joins?

    • Compare rows in same table to each other

  • Create two instances of same table in FROM clause

    • At least one alias required

  • Example: Return all employees and the name of the employee’s manager


Self join examples
Self-Join examples functions)

  • Return 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:

SELECTEMP.EmpID,EMP.LastName,

EMP.JobTitle,EMP.MgrID,MGR.LastName

FROM HR.Employees ASEMP

LEFT OUTER JOIN HR.Employees ASMGR

ONEMP.MgrID= MGR.EmpID;

SELECTEMP.EmpID,EMP.LastName,

EMP.Title,MGR.MgrID

FROMHumanResources.EmployeeASEMP

LEFT OUTER JOIN HumanResources.EmployeeASMGR

ONEMP.MgrID= MGR.EmpID;


Using joins to view data from multiple tables

Using JOINS to view data from multiple tables


Filtering and functions)Sorting Data


Using the order by clause
Using functions) the ORDER BY clause

  • ORDER BY sorts rows in results for presentation purposes

    • Use of ORDER BY guarantees the sort order of the result

    • Last clause to be logically processed

    • Sorts 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 c lause examples
ORDER BY functions)clause examples

  • ORDER BY with column names:

  • ORDER BY with column alias:

  • ORDER BY with descending order:

SELECTSalesOrderID, CustomerID, OrderDate

FROMSales.SalesOrderHeader

ORDER BY OrderDate;

SELECTSalesOrderID, CustomerID, YEAR(OrderDate) AS OrderYear

FROM Sales.SalesOrderHeader

ORDER BY OrderYear;

SELECTSalesOrderID, CustomerID, OrderDate

FROMSales.SalesOrderHeader

ORDER BY OrderDateDESC;


Filtering data in the where clause
Filtering data in the WHERE clause functions)

  • WHERE clauses use predicates

    • Must be expressed as logical conditions

    • Only rows for which predicate evaluates to TRUE are accepted

    • Values of FALSE or UNKNOWN are filtered out

  • WHERE clause follows FROM, precedes other clauses

    • Can’t see aliases declared in SELECT clause

  • Can be optimized by SQL Server to use indexes


Where clause syntax
WHERE clause functions) 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

SELECTCustomerID, TerritoryID

FROMSales.Customer

WHERETerritoryID = 6;

SELECTCustomerID, TerritoryID

FROMSales.Customer

WHERETerritoryID >= 6;

SELECTCustomerID, TerritoryID, StoreID

FROMSales.Customer

WHEREStoreID >= 1000 ANDStoreID <= 1200;


Filtering data in the select clause
Filtering functions)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 up

    • SELECT TOP (N) WITH TIES

      • Retrieve duplicates where applicable (nondeterministic)

  • TOP is proprietary to Microsoft SQL Server


Filtering using top
Filtering using TOP functions)

  • Filter rows for customers to display top 20 TotalDue items

  • Filter rows for customers to display top 20 TotalDueitems with ties

  • Filter rows for customers to display top 1% of TotalDue items

SELECTTOP(20) SalesOrderID, CustomerID, TotalDue

FROMSales.SalesOrderHeader

ORDER BY TotalDueDESC;

SELECTTOP(20) WITH TIES SalesOrderID, CustomerID, TotalDue

FROMSales.SalesOrderHeader

ORDER BY TotalDueDESC;

SELECT TOP (1) PERCENT SalesOrderID, CustomerID, TotalDue

FROMSales.SalesOrderHeader

ORDER BY TotalDueDESC;


Handling null in queries
Handling NULL in queries functions)

  • Different components of SQL Server handle NULL differently

    • Query filters (ON, WHERE, HAVING) filter out UNKNOWNs

    • CHECK constraints accept UNKNOWNS

    • ORDER BY, DISTINCT treat NULLs as equals

  • Testing for NULL

    • Use IS NULL or IS NOT NULL rather than = NULL or <> NULL

SELECT CustomerID, StoreID, TerritoryID

FROMSales.Customer

WHEREStoreIDIS NULL

ORDER BY TerritoryID


Sorting and filtering data

Sorting and filtering data


Summary
Summary functions)

  • 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


Summary1
Summary functions)

  • 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.


ad