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

Figure 9-1: Three basic joins.

➤ Left: A left join returns all the records in the table on the left of the join and only those
related records from the right. You may also perform a right join if you want the opposite
outcome. You can stick to a left join to keep things simpler. All you have to do is switch the
order of the tables in the left join to achieve the same outcome as a right join.

➤ Full: Returns the union of two tables or datasets.

In the case of a left join or full join, you may get Null columns back for rows that do not
have a relationship in the non-primary table or dataset.

Note

When you define a join, you need to also specify the join criteria in your SQL statement. You accom-
plish this by using the On keyword. To simplify your SQL statement, use an Alias in the join.

Select da.AccountType, da.Operator, ff.Amount
From dbo.DimAccount da
Join dbo.FactFinance ff
On da.AccountKey = ff.AccountKey

This query returns two columns from DimAccount and one from FactFinance . The alias given to
each table can be specified before every column in your Select statement. (You can skip qualifying
a column with an alias if that column name is unique in all the tables in your join.) As a best practice,
you should always pre-qualify your columns with the proper alias and use a standard abbreviation to
define those aliases.

The Where clause
Use a Where clause in the Select statement to filter the result set and conditionally return specific
records. The Where clause is always used in combination with an operator, such as = (equal), <> (not
equal), > (greater than), < (less than), >= (greater than or equal to), <= (less than or equal to),
Between (within general range).
This SQL statement returns only records with account types equal to Assets .

Select da.AccountType, da.Operator, ff.Amount
From dbo.DimAccount da
Join dbo.FactFinance ff
On da.AccountKey = ff.AccountKey
Where da.AccountType='Assets'