Database Reference
In-Depth Information
Advanced SQL Concepts
Now that we've covered the basics, we review some of the more advanced functions in SQL, includ-
ing data manipulation statements.
The Union operator
As the name implies, the
Union
operator allows you to combine the result set of two
Select
statements
into one output. There are two variations on this operator:
Union
and
Union All
. The difference between
the two is that
Union
returns a distinct output of the result set. Here's how to use the
Union All
operator:
Select 'First Row'
Union All
Select 'Second Row'
Union All
Select 'Third Row'
The
Select
statements with
Union
or
Union All
operators can include all the options available
to regular
Select
statements. The only restriction is that the result set in each
Select
statement
must have the same structure.
Case expression
The
Case
expression is referred to as a
Scalar
function in SQL. There are several other useful functions
in this category and they all follow a similar concept — they are applied on a single value in your result set
(or the data you are querying) to manipulate that value. Here's the basic structure of a
Case
expression:
Case
When <condition>
Then <output>
Else <output>
End
You can use the
Case
expression in the
Select
statement in various places, including the columns
list, the join, or the
Where
clause. This query changes the values of
AccountType
from
Balances
to
Balance
and leaves all other values the same.
Select Case
When AccountType='Balances'
Then 'Balance'
Else AccountType
End ModifiedAccountType
From dbo.DimAccount
Search WWH ::
Custom Search