Advanced SELECT Clauses


Understanding DISTINCT

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 select columns.

Removes rows already operated on by WHERE, HAVING, and GROUP BY clause.

Some 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   StoreID
FROM  Sales.Customer ;

StoreID

1234
570
902
1800
700

Using aliases to refer to columns

Column aliases using AS

SELECT  SalesOrderID,  UnitPrice, OrderQty  AS  Quantity
FROM  Sales.SalesOrderDetail ;

Column aliases using =

SELECT  SalesOrderID,  UnitPrice,   Quantity  =  OrderQty
FROM  Sales.SalesOrderDetail ;


Accidental column aliases

SELECT  SalesOrderID,  UnitPrice  Quantity
FROM  Sales.SalesOrderDetail ;


Using aliases to refer to tables

Create tables aliases in the FROM clause using AS

SELECT  SalesOrderID, ProductID
FROM  Sales.SalesOrderDetail  AS SalesOrders ;

Tables aliases without AS

SELECT  SalesOrderID, ProductID
FROM  Sales.SalesOrderDetail  SalesOrders ;

Using tables aliases in the SELECT clause

SELECT  SalesOrders.SalesOrderID, SalesOrders.ProductID
FROM  Sales.SalesOrderDetail  AS SalesOrders ;

T-SQL 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
      Evaluated 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 clause
     ORDER BY clause 

Writing simple CASE expressions

 Keyword
 Expression component
 SELECT
 <select  list>
 CASE
 <value to compare>
 WHEN
 <value to match>
 THEN
 <result>
 END
 N/A
 FROM
 <table  source>

SELECT ProductID, Name, ProductSubCategoryID,
     CASE  ProductSubCategoryID
          WHEN 1 THEN  'Beverages'
          ELSE  'Unknown Category'
     END
FROM  Production.Product