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
|