Introduction to SQL - Microsoft Business Intelligence Tools for Excel Analysts - page 185

Wildcard
Character(s)

What It Does Used with Like Result

[ ] and %

Indicates any single char-
acter within a specified
range combined with any
string

Returns all rows where
AccountType starts with a
letter between a and k

Like '[a-k]%'

[ ^] and %

Indicates any single char-
acter not within a speci-
fied range combined with
any string

Returns all rows where
AccountType does not start
with the letter a , b , or c

Like '[^abc]%'

Subqueries
Just as the name implies, a subquery is a query within a query. It can be nested anywhere an SQL
expression can be used. In most cases, you can accomplish your objective without the use of subque-
ries, but they tend to be a style preference. In the following sections, we show you when you'd use a
subquery.

In a From clause
The subquery in this example returns all the records that exist both in FactFinance and
DimScenario .

Select da.AccountType, sub.ScenarioName,sum(sub.Amount)
From dbo.DimAccount da
Left Join (
Select ff.Amount, ff.AccountKey,ds.ScenarioName
From dbo.FactFinance ff
Join dbo.DimScenario ds
On ff.ScenarioKey=ds.ScenarioKey
) sub
On da.AccountKey=sub.AccountKey
Group By da.AccountType, sub.ScenarioName

The result set is left joined to DimAccount . The final output returns all rows from DimAccount and
only those rows that match from the subquery.

Correlated subqueries
A correlated subquery means that the subquery runs once for every row in the main query. You can
put correlated subqueries in several places inside a Select statement. This subquery runs once
for every row in FactFinance and is restricted by the relationship on ScenarioKey with the
outer query.