OUTER JOIN - FULL

An outer join will show records from both tables, and fill in NULLs for missing matches on either side.
This is not used that often.

This only works in SQL Server - not possible in Access
FULL OUTER JOIN

SELECT              Employees.LastName 
                    ,Employees.City 
                    ,Suppliers.CompanyName 
                    ,Suppliers.City 
FROM                EMPLOYEES 
FULL JOIN           SUPPLIERS 
ON                  Employees.City = Suppliers.City 
--FULL OUTER JOIN   Suppliers on Employees.City = Suppliers.City 
-- the above 2 lines are the same but only work in SQL Server   
ORDER BY            Suppliers.CompanyName, Employees.LastName 

This works in both SQL Server and Access
EQUIVALENT FULL OUTER JOIN becuase This is not possible in Access

SELECT              Employees.LastName 
                    ,Employees.City 
                    ,Suppliers.CompanyName 
                    ,Suppliers.City 
FROM                Employees 
INNER JOIN          Suppliers 
ON                  Employees.City = Suppliers.City 
UNION ALL           SELECT      Employees.LastName 
                                ,Employees.City 
                                ,Suppliers.CompanyName 
                                ,Suppliers.City 
                    FROM        Employees 
                    LEFT JOIN   Suppliers 
                    ON          Employees.City = Suppliers.City 
                    WHERE       Suppliers.City IS NULL 
UNION ALL           SELECT      Employees.LastName 
                                ,Employees.City 
                                ,Suppliers.CompanyName 
                                ,Suppliers.City 
                    FROM        Employees 
                    RIGHT JOIN  Suppliers 
                    ON          Employees.City = Suppliers.City 
                    WHERE       Employees.City IS NULL 
ORDER BY            Suppliers.CompanyName 
                    ,Employees.LastName 

=--this only works in SQL Server - not possible in Access
=--FULL Outer Join

SELECT              Employees.LastName 
                    ,Employees.City 
                    ,Suppliers.CompanyName 
                    ,Suppliers.City 
FROM                Employees 
FULL JOIN           Suppliers 
ON                  Employees.City = Suppliers.City 
--FULL Outer Join   Suppliers on Employees.City = Suppliers.City 
-- the above 2 lines are the same but only work in SQL Server 
ORDER BY            Suppliers.CompanyName 
                    ,Employees.LastName 

An outer join will show records from both tables, and displays nulls for missing matches on either side.
This returns all the orphans in both tables.
This is not used that often.

ANSI Standard

SELECT              Customers.CustomerID, 
                    Customers.CompanyName, 
                    Orders.OrderID 
FROM                Customers 
FULL OUTER JOIN     Orders 
ORDER BY            Customers.CustomerID 

Alternative
The only way to do this before was to write a left outer join SQL statement and UNION it with a right outer join SQL statement

SELECT              Customers.CustomerID, 
                    Customers.CompanyName, 
                    Orders.OrderID 
FROM                Customers 
LEFT JOIN           Orders 
ON                  Customers.CustomerID = Orders.CustomerID 
UNION               SELECT      Customers.CustomerID 
                                ,Customers.CompanyName 
                                ,Orders.OrderID 
                    FROM        Customers 
                    RIGHT JOIN  Orders 
                    ON          Customers.CustomerID = Orders.CustomerID 
                    ORDER BY    Customers.CustomerID