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

Hi mcrmg,

Straight from sql books on line
=======================================================
Using Outer Joins
Inner joins return rows only when there is at least one row from both tables that matches the join condition. Inner joins eliminate the rows that do not match with a row from the other table. Outer joins, however, 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. All rows are retrieved from the left table referenced with a left outer join, and all rows from the right table referenced in a right outer join. All rows from both tables are returned in a full outer join

Microsoft® SQL Server™ uses these SQL-92 keywords for outer joins specified in a FROM clause:

LEFT OUTER JOIN or LEFT JOIN
RIGHT OUTER JOIN or RIGHT JOIN
FULL OUTER JOIN or FULL JOIN
SQL Server supports both the SQL-92 outer join syntax and a legacy syntax for specifying outer joins based on using the *= and =* operators in the WHERE clause. The SQL-92 syntax is recommended because it is not subject to the ambiguity that sometimes results from the legacy Transact-SQL outer joins.

Using Left Outer Joins
Consider a join of the authors table and the publishers table on their city columns. The results show only the authors who live in cities in which a publisher is located (in this case, Abraham Bennet and Cheryl Carson).

To include all authors in the results, regardless of whether a publisher is located in the same city, use an SQL-92 left outer join. Here is the query and results of the Transact-SQL left outer join:

USE pubs

SELECT a.au_fname, a.au_lname, p.pub_name

FROM authors a LEFT OUTER JOIN publishers p

    ON a.city = p.city

ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC


The LEFT OUTER JOIN includes all rows in the authors table in the results, whether or not there is a match on the city column in the publishers table. Notice that in the results there is no matching data for most of the authors listed; therefore, these rows contain null values in the pub_name column.

Using Right Outer Joins
Consider a join of the authors table and the publishers table on their city columns. The results show only the authors who live in cities where a publisher is located (in this case, Abraham Bennet and Cheryl Carson). The SQL-92 right outer join operator, RIGHT OUTER JOIN, indicates all rows in the second table are to be included in the results, regardless of whether there is matching data in the first table.

To include all publishers in the results, regardless of whether a city has a publisher located in the same city, use an SQL-92 right outer join. Here is the Transact-SQL query and results of the right outer join:

USE pubs

SELECT a.au_fname, a.au_lname, p.pub_name

FROM authors AS a RIGHT OUTER JOIN publishers AS p
    ON a.city = p.city

ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC


An outer join can be further restricted by using a predicate (such as comparing the join to a constant). This example contains the same right outer join, but eliminates all titles that have sold less than 50 copies:

USE pubs

SELECT s.stor_id, s.qty, t.title

FROM sales s RIGHT OUTER JOIN titles t

    ON s.title_id = t.title_id

    AND s.qty > 50

ORDER BY s.stor_id ASC

For more information about predicates, see WHERE.

Using Full Outer Joins
To retain the nonmatching information by including nonmatching rows in the results of a join, use a full outer join. Microsoft® SQL Server™ provides the full outer join operator, FULL OUTER JOIN, which includes all rows from both tables, regardless of whether or not the other table has a matching value.

Consider a join of the authors table and the publishers table on their city columns. The results show only the authors who live in cities in which a publisher is located (in this case, Abraham Bennet and Cheryl Carson). The SQL-92 FULL OUTER JOIN operator indicates that all rows from both tables are to be included in the results, regardless of whether there is matching data in the tables.

To include all publishers and all authors in the results, regardless of whether a city has a publisher located in the same city, or whether a publisher is located in the same city, use a full outer join. Here is the query and results of the Transact-SQL full outer join:

USE pubs

SELECT a.au_fname, a.au_lname, p.pub_name

FROM authors a FULL OUTER JOIN publishers p
    ON a.city = p.city

ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC