datetime tune date into pad dynamic cursor money percent sp job isnumeric while over update
FREE TRIAL  SQL 2012 PROGRAMMING  SEARCH
SQL Server Scripts SQL 2005 SQL 2008 Articles
SQL JOBS NEWS FORMAT DEV JOBS
How to apply FULL JOIN in queries?

Execute the following SQL Server T-SQL script in Management Studio Query Editor to demonstrate the use of Full Outer Join which returns no matches from both tables in addition to matches:

-- SQL full join - SQL inner join - SQL full outer join

USE AdventureWorks;

GO

 

SELECT   C.Name  AS Category,

         SC.Name AS Subcategory,

         PM.Name AS Model,

         P.Name  AS Product,

         P.ListPrice

FROM     Production.Product AS P

         FULL JOIN Production.ProductModel AS PM

           ON PM.ProductModelID = P.ProductModelID

         FULL JOIN Production.ProductSubcategory AS SC

           ON SC.ProductSubcategoryID = P.ProductSubcategoryID

         INNER JOIN Production.ProductCategory AS C

           ON C.ProductCategoryID = SC.ProductCategoryID

ORDER BY Category,

         Subcategory;

GO

Partial results:

Category Subcategory Model Product ListPrice
Accessories Bike Racks Hitch Rack - 4-Bike Hitch Rack - 4-Bike 120
Accessories Bike Stands All-Purpose Bike Stand All-Purpose Bike Stand 159
Accessories Bottles and Cages Water Bottle Water Bottle - 30 oz. 4.99
Accessories Bottles and Cages Mountain Bottle Cage Mountain Bottle Cage 9.99
Accessories Bottles and Cages Road Bottle Cage Road Bottle Cage 8.99
Accessories Cleaners Bike Wash Bike Wash - Dissolver 7.95

 

Relates article:

Better Alternatives to a FULL OUTER JOIN

 

Exam Prep 70-461
Exam 70-461