Solved

JOIN, INNER JOIN, OUTER JOIN, RIGHT JOIN, LEFT JOIN

Posted on 2004-04-11
18
250,896 Views
Last Modified: 2011-08-18
Hi,

Can EE give me some examples?  Thanks
0
Comment
Question by:mcrmg
  • 5
  • 2
  • 2
  • +8
18 Comments
 
LVL 26

Assisted Solution

by:Alan Warren
Alan Warren earned 100 total points
ID: 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

0
 
LVL 67

Accepted Solution

by:
sirbounty earned 70 total points
ID: 10803042
>>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.net/manifold/manuals/5_userman/mfd50OUTER_LEFT_RIGHT_FULL_JOIN_Opera.htm
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 10803051
0
 

Author Comment

by:mcrmg
ID: 10803066
RIGHT JOIN, LEFT JOIN
I got this part.  Thanks




INNER JOIN, OUTER JOIN,

Are they the same as full join??
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 10803076

The INNER JOIN returns all rows from both tables where there is a match. If there are rows in Employees that do not have matches in Orders, those rows will not be listed.

Alan

0
 

Author Comment

by:mcrmg
ID: 10803081
How about OUTTER??  Thx
0
 
LVL 26

Assisted Solution

by:Alan Warren
Alan Warren earned 100 total points
ID: 10803093
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
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 10803117
Hi mcrmg,

You should find all you need to know about SQL Joins here:
This is the link you want, save me posting all the contents.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_09_0zqr.asp

Alan
0
 
LVL 44

Assisted Solution

by:Arthur_Wood
Arthur_Wood earned 50 total points
ID: 10804560
Access DOES NOT support the Key words OUTER and FULL.  so this example from alanwarren:

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

is not relevant to Access,but does apply to SQL Server.

AW
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Expert Comment

by:v-strom2002
ID: 11031323
Hi,

I have to work with MS Access 2000. This version doesn't support (full) outer join.
How can I substitute this with a combination of left/right/inner join. Performance isn't a problem (small tables). Also temporary tables are possible.

0
 

Expert Comment

by:sherman6789
ID: 11311265
Hello,

This entire thread contain very valuable information.  I am adding a printout of it to my "Hall of Access Information" which includes info that may need later.

Can the joins be used for two table when one is not an Access table?  For example, if one is an Excel tables to be joined withe the Access table.  Both have the same field names in the same order.

WRS
0
 

Expert Comment

by:dcruzdf
ID: 12335455
If you need to use a combination of LEFT and RIGHT JOINs to yield a FULL JOIN, here's what I did

Firstly use a UNION ALL between the RIGHT and LEFT, then encapsulate it with a DISTINCT * on the resulting table.  Suffice to say the only difference between the two queries in the UNION should be the LEFT or RIGHT JOIN operater

Here's an example on what I was working on

SELECT DISTINCT * FROM (

      SELECT       s.CountryName AS CountryNme,
            s.[Sampled Date] AS SampledReceiptedDate,
            s.WgtSampled,
            s.ItemsSampled,
            s.IPK,
            s.CountOfBag,
            r.sum_bags,
            r.sum_weight
      FROM qryMailSampledSep AS s
      RIGHT JOIN qryMailReceiptedSep AS r
      ON (s.[Sampled Date] = r.[receipt Date])
      AND (s.CountryName = r.Country)

      UNION ALL

      SELECT       s.CountryName AS CountryNme,
            s.[Sampled Date] AS SampledReceiptedDate,
            s.WgtSampled,
            s.ItemsSampled,
            s.IPK,
            s.CountOfBag,
            r.sum_bags,
            r.sum_weight
      FROM qryMailSampledSep AS s
      LEFT JOIN qryMailReceiptedSep AS r
      ON (s.[Sampled Date] = r.[receipt Date])
      AND (s.CountryName = r.Country);

) as tbl

ORDER BY SampledReceiptedDate, CountryNme

Lemme know how you go
0
 

Expert Comment

by:sherman6789
ID: 12335471
October 17, 2004


Thanks  dcruzdf.  

Your information is very helpful to me and will probably be useful to others also.

WRSherman
0
 
LVL 4

Expert Comment

by:billcute
ID: 12335783
Question well addressed.
0
 

Expert Comment

by:TriangleIL
ID: 14652811
I have tried the above mentioned exercise to get an OUTER join to work in Access JET sql.  This expression gives the following error "Join expression not supported"


SELECT DISTINCT * FROM (
SELECT * FROM (gp LEFT JOIN lp ON gp.name = lp.name) LEFT JOIN temp ON gp.name = temp.name UNION ALL

 ( SELECT * FROM (gp RIGHT JOIN temp ON gp.name = temp.name) RIGHT JOIN lp ON gp.name = lp.name) );

Any ideas?  Thanks
0
 

Expert Comment

by:dudesmash
ID: 20870482
Thanks for all the explanations
0
 
LVL 10

Expert Comment

by:LukeChung-FMS
ID: 25822474
Here's a paper I wrote on Microsoft Access Query Tips and Techniques that you may find useful: http://www.fmsinc.com/tpapers/queries/index.html
There are several examples of multi-table joins including implied joins using criteria. It also has links to other papers on Update Queries, Non-Updateable Queries, Crosstab Queries, etc.
Hope this helps.
0
 
LVL 10

Expert Comment

by:LukeChung-FMS
ID: 26040265
Here's a recent paper we published on the Not-In query and using LEFT JOIN and RIGHT JOIN to get them. Hope it's helpful:
http://www.fmsinc.com/MicrosoftAccess/query/outer-join/index.html
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now