SQL EXPRESSION STUDY 1

Content List:

• Case When
• CROSS JOIN (U-SQL)
• Cross Apply And Outer Apply
• Querying XML fields using t-sql

一：Case When

case expression has two formats:

• simple case expression

    CASE input_expression   
         WHEN when_expression THEN result_expression [ ...n ]   
         [ ELSE else_result_expression ] 
  

• searched case expression

    CASE  
         WHEN Boolean_expression THEN result_expression [ ...n ]   
         [ ELSE else_result_expression ]   
  

Arguments:

1. WHEN when_expression

Is a simple expression to which input_expression is compared when the simple CASE format is used.

2. THEN result_expression

Is the expression returned when input_expression equals when_expression evaluates to TRUE, or Boolean_expression evaluates to TRUE.

3. ELSE else_result_expression

Is the expression returned if no comparison operation evaluates to TRUE. If this argument is omitted and no comparison operation evaluates to TRUE, CASE returns NULL

NOTE:

1. Simple CASE expression:

Returns the result_expression of the first input_expression = when_expression that evaluates to TRUE.

2. Searched CASE expression

Returns result_expression of the first Boolean_expression that evaluates to TRUE.

Examples

1. Using a SELECT statement with a simple CASE expression

Within a SELECT statement, a simple CASE expression allows for only an equality check; no other comparisons are made. The following example uses the CASE expression to change the display of product line categories to make them more understandable.

USE AdventureWorks2012;  
GO  
SELECT   ProductNumber, Category =  
      CASE ProductLine  
         WHEN 'R' THEN 'Road'  
         WHEN 'M' THEN 'Mountain'  
         WHEN 'T' THEN 'Touring'  
         WHEN 'S' THEN 'Other sale items'  
         ELSE 'Not for sale'  
      END,  
   Name  
FROM Production.Product  
ORDER BY ProductNumber;  
GO  

2. Using a SELECT statement with a searched CASE expression

Within a SELECT statement, the searched CASE expression allows for values to be replaced in the result set based on comparison values. The following example displays the list price as a text comment based on the price range for a product.

USE AdventureWorks2012;  
GO  
SELECT   ProductNumber, Name, "Price Range" =   
      CASE   
         WHEN ListPrice =  0 THEN 'Mfg item - not for resale'  
         WHEN ListPrice < 50 THEN 'Under $50'  
         WHEN ListPrice >= 50 and ListPrice < 250 THEN 'Under $250'  
         WHEN ListPrice >= 250 and ListPrice < 1000 THEN 'Under $1000'  
         ELSE 'Over $1000'  
      END  
FROM Production.Product  
ORDER BY ProductNumber ;  
GO  

3. To left join query, using the syntax of the case after on (Similar example appeares in my own project)

    select  * from AIRPORTORDER  as A
    inner join RANGECODE  as B
    on 
        case when  A.AIRPORDER_GOORBACK='2' then  A.AIRPORDER_ENDCODE
             when  A.AIRPORDER_GOORBACK='1' then  A.AIRPORDER_STARTCODE
        end =B.RANGECODE_NO
    
    GO

二：CROSS JOIN (U-SQL)

A cross join returns the Cartesian product of rows from the rowsets in the join. In other words, it will combine each row from the first rowset with each row from the second rowset.
Note that this is potentially an expensive and dangerous operation since it can lead to a large data explosion. It is best used in scenarios where a normal join cannot be used and very selective predicates（判断） are being used in the WHERE clause to limit the number of produced rows.

三：Cross Apply And Outer Apply

The Apply operator joins two table valued expression, the table on right is evaluated every time for each row of the table on the left which is actually a table-valued function. The final outcome contains all the selected columns from left side table and then from the right side table.There are two types of APPLY operators

• Cross Apply

The Cross Apply returns rows form the outer table (table on the left of the Apply operator) that produces matching values from the table-valued function (which is on the right side of the operator).

The Cross Apply is equivalent to Inner Join, but it works with a table-valued function.

Example: To view the working of the Cross Apply operator first we shall create two tables namely EmployeeDetails and EmpSalary.

Below is the schema and create query for EmployeeDetails

CREATE TABLE EmployeeDetails (
      EmpId int PRIMARY KEY,
      EmpFirstName VARCHAR(50),
      EmpLastName VARCHAR(50),
      Department VARCHAR(50),
      DepartID INT
    )

The data in EmployeeDetails table is provided below

Now Create another table EmpSalary

CREATE TABLE EmpSalary (
      EmpID INT,
      EmpFullName VARCHAR(80),
      EmpSalary INT,
      EmpWorkingYears INT,
      DepartID INT
    )

Here is the data in EmpSalary table

Now we will create a user defined function of sql server with name fn_Salaryinc, this function returns output with increased salary by Rs.5000 on the basis of Departid column.

CREATE FUNCTION fn_Salaryinc (@DepartmentID int)
    RETURNS TABLE
    AS
    RETURN
    (
      SELECT 
        EmpID, EmpFullName,
        EmpSalary+5000 AS Salaryinc
      FROM
        Empsalary
      WHERE
       DepartID = @DepartmentID 
  
    )
    GO

Use funtion fn_Salaryinc to get increased salary.

SELECT EmpID, Salaryinc FROM fn_Salaryinc(2)

After applying the above function the salary got increased by 5000. The below table shows the output for the function fn_Salaryinc

Now since the function is giving the desired results we will write a Cross apply Query

 SELECT
      e.EmpFirstName,
      e.EmpLastName,
      f.Salaryinc
    FROM
      EmployeeDetails AS e
    CROSS APPLY
      fn_Salaryinc  (e.DepartID) AS f

The output of the above query which shows the functioning for Cross Apply operator

We are getting repetitive employees as we are running the function, and it gets data once for each Departid.

• Outer Apply

Actually，Outer Apply operator is similar with Cross Apply operator，using the Outer Apply operator we are able to return all the rows from the outer table no matter if function returns any row.

四：Querying XML fields using t-sql

Example

This creates a table with one column of the xml datatype and inserts one row with a (simple) XML document:

declare @demo table(field1 xml)
create table demo (field1 xml)
insert into @demo (field1) 
values ('<document>
<header>Alphabet</header>
<items>
<item id="a">a is for apple</item>
<item id="b">b is for balloon</item>
</items>
</document>')

As you can observe the document structure is like this:

<document>
  <header>Alphabet</header>
  <items>
    <item id="a">a is for apple</item>
    <item id="b">b is for balloon</item>
  </items>
</document>

Now the cool thing is that there are several methods that can be executed on the Xml datatype, such as:
Query、Value、Exists、Modify、Nodes
In this brief introduction I’ll highlight the usage of the Value 、 Query and Nodes methods. All methods are well documented in the MSDN library.

• Query

select field1.query('/document/header') A from @demo

select field1.query('/document/items/item[@id="a"]') B from @demo

• Value

value (XQuery, SQLType)
select field1.value('(/document/items/item)[1]', 'nvarchar(max)') C from @demo

• nodes

nodes (XQuery) as Table(Column)
A nodes() method invocation with the query expression /document/items/itemwould return a rowset with three rows, each containing a logical copy of the original XML document, and with the context item set to one of the <item> nodes:
You can then query this rowset by using xml data type methods. The following query extracts the subtree of the context item for each generated row:

SELECT T2.Loc.query('.') D
FROM   @demo
CROSS APPLY field1.nodes('/document/items/item') as T2(Loc)     

study links

1.Case When
2.CROSS JOIN (U-SQL)
3.Cross Apply And Outer Apply With Examples
4.Querying XML fields using t-sql