>>table {LEFT | RIGHT | FULL} [OUTER] JOIN table ON condition
The LEFT JOIN, RIGHT JOIN, and FULL JOIN operators have these parts:
Part
Description
table
The names of the tables from which records are combined.
condition
A Boolean expression restricting the records in the resulting table.
Remarks
Use a LEFT JOIN operator to create a left outer join. Left outer joins include all of the records from the first (left) of two tables, even if there are no matching values for records in the second (right) table.
Use a RIGHT JOIN operator to create a right outer join. Right outer joins include all of the records from the second (right) of two tables, even if there are no matching values for records in the first (left) table.
Use a FULL JOIN operator to create a full outer join. Full outer joins include all of the records from both tables, even if there are no matching values for records in the other table.
The following example shows how we could join the "Categories" and "Products" tables using the "Category ID" column to produce a list of all categories, including those that contain no products. The GROUP BY clause and the First aggregate function are used to collapse multiple records for products sharing the same category):
SELECT [Category Name], First([Product Name]) FROM [Categories] LEFT JOIN [Products] ON [Categories].[Category ID] = [Products].[Category ID] GROUP BY [Category Name];
The following example shows how we could join the "Categories" and "Products" tables using the "Category ID" column to produce a list of all products, including those that contain no categories:
SELECT [Category Name], [Product Name] FROM [Categories] RIGHT JOIN [Products] ON [Categories].[Category ID] = [Products].[Category ID];
To combine records disallowing NULL values on any side of a join, use an INNER JOIN operator.
We can use more than one column in a join condition.
When nesting any of the LEFT JOIN, RIGHT JOIN, and FULL JOIN operators with one or more of the INNER JOIN operators, for performance purposes it is recommended that we put LEFT JOIN, RIGHT JOIN, and FULL JOIN operators inside the INNER JOIN operators.
Examples
This example uses the LEFT JOIN operator and the GROUP BY clause to select employees and the number of orders they processed in June, 1991:
SELECT [First Name] & " " & [Last Name] AS [Name], Count([Order Date]) AS [Order Count] FROM [Employees] LEFT JOIN [Orders] ON [Orders].[Employee ID] = [Employees].[Employee ID] AND [Order Date] >= #6/1/1991# AND [Order Date] < #7/1/1991# GROUP BY [First Name] & " " & [Last Name];
This example uses the LEFT JOIN operator, several INNER JOIN operators, and the GROUP BY clause to show whether a given product has or has not been processed at least once by Andrew Fuller:
SELECT [Product Name], "Yes" AS [Served] FROM [Products] LEFT JOIN ([Order Details] INNER JOIN ([Orders] INNER JOIN [Employees] ON [Orders].[Employee ID] = [Employees].[Employee ID] AND [First Name] = "Andrew" AND [Last Name] = "Fuller") ON [Order Details].[Order ID] = [Orders].[Order ID]) ON [Order Details].[Product ID] = [Products].[Product ID] GROUP BY [Product Name];
<<ref: http://exchange.manifold.n
Main Topics
Browse All Topics
by: alanwarrenPosted on 2004-04-11 at 18:14:18ID: 10803034
HI mcrmg
Using Northwind.mdb tables Orders and Customers
Inner Join eg:
SELECT DISTINCTROW Orders.OrderID, Orders.CustomerID, Orders.EmployeeID, Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate, Orders.ShipVia, Orders.Freight, Orders.ShipName, Orders.ShipAddress, Orders.ShipCity, Orders.ShipRegion, Orders.ShipPostalCode, Orders.ShipCountry, Customers.CompanyName, Customers.Address, Customers.City, Customers.Region, Customers.PostalCode, Customers.Country
FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
Left Join eg:
SELECT DISTINCTROW Orders.OrderID, Orders.CustomerID, Orders.EmployeeID, Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate, Orders.ShipVia, Orders.Freight, Orders.ShipName, Orders.ShipAddress, Orders.ShipCity, Orders.ShipRegion, Orders.ShipPostalCode, Orders.ShipCountry, Customers.CompanyName, Customers.Address, Customers.City, Customers.Region, Customers.PostalCode, Customers.Country
FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
Right Join eg:
SELECT DISTINCTROW Orders.OrderID, Orders.CustomerID, Orders.EmployeeID, Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate, Orders.ShipVia, Orders.Freight, Orders.ShipName, Orders.ShipAddress, Orders.ShipCity, Orders.ShipRegion, Orders.ShipPostalCode, Orders.ShipCountry, Customers.CompanyName, Customers.Address, Customers.City, Customers.Region, Customers.PostalCode, Customers.Country
FROM Customers RIGHT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
Alan