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

Good tute here: http://www.w3schools.com/sql/sql_join.asp

RIGHT JOIN, LEFT JOIN
I got this part.  Thanks




INNER JOIN, OUTER JOIN,

Are they the same as full join??

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

How about OUTTER??  Thx

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

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.

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

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

October 17, 2004


Thanks  dcruzdf.  

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

WRSherman

Question well addressed.

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

Thanks for all the explanations

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.

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