Wednesday, November 27

Outer Joins- Left Outer Join / Left Join

Outer Joins return all rows from at least one of the tables or views mentioned in the FROM clause, as long as those rows meet any WHERE or HAVING search conditions. 

Left Outer Join / Left Join

The LEFT JOIN keyword returns all rows from the left table (table1), with the matching
rows in the right table (table2). The result is NULL in the right side when there is no match.

Syntax-LEFT JOIN
SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name=table2.column_name;

Eg: Using AdventureWorksDW2008R2 Database

  select d.AccountKey, f.Financekey,
  d.AccountDescription, f.amount from DimAccount d
  LEFT OUTER JOIN FactFinance f
  on d.AccountKey=f.AccountKey order by AccountKey asc;

Output:

You counld see that NULL result set are displayed when no match is found from right
table FactFinance for the corresponding rows of the left table DimAccount .