Solved

LEFT JOIN vs RIGHT JOIN ???

Posted on 2001-08-02
7
432 Views
Last Modified: 2007-12-19
Someone please explain

LEFT JOIN

vs

RIGHT JOIN

Why do I care if something is LEFT JOINED vs RIGHT JOINED?

I need someone to put this in layman's terms, really dummy it up.

I am trying to progress beyond SELECT FROM WHERE which is about as advanced as I get in SQL knowledge right now.

Thanks,

Tom
0
Comment
Question by:knowlton
7 Comments
 
LVL 1

Accepted Solution

by:
TheNextStep earned 10 total points
ID: 6345707
LEFTJOIN includes ALL records from the first table/query and only those records from the second table/query where the joined fields are equal.

RIGHTJOIN includes ALL records from the second table/query and only those records from the first table/query where the joined fields are equal.

It may help you to try QBE (Query By Example) before getting directly into writing SQL statements.  Access lets you view the SQL statement after it has done the work.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 6345750
The LEFT/RIGHT join is used to show all table-entries from one table and only the matching of the other.
The direction you draw the line will influence the sequence of the "mandatory" tables mentioned in the pop-up and lead to a LEFT or RIGHT.
Effectively the "non-match" wizard delivers only half of the non-match, as having a right-join, the left-join gives also a "non-match" but then seen from the other table.
To get all non-matching records you need to have a right-join AND a left-join with in both the criteria that the non-mandatory table has nulls in it's unique key.
With a union of those two queries you get effectively what another SQL implementation would call a FULL OUTERJOIN.

Normally a JOIN gives automatically all matched, the LEFT "union" RIGHT gives the remaining set of rows.

Clear ?
No, read again and again and ....

Nic;o)
0
 
LVL 12

Expert Comment

by:Paurths
ID: 6345783
i've been reading it 3.526 times now Nico,

i must admit, i s@ck at SQL...

;-) ;-) ;-) ;-) ;-) ;-) ;-) ;-) ;-) ;-)
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 54

Expert Comment

by:nico5038
ID: 6345797
Ricky, too much Hoegaarden causes reading loops.... ;-)

Nic;o)
0
 
LVL 12

Expert Comment

by:Paurths
ID: 6345831
Public Sub Freetime()

Dim strSQL as String
Dim strBrand as String
Dim rst as DAO.Recordset
Dim dbs as DAO.Database

Select Case Today
  Case Friday
      strBrand = "Hoegaarden"
  Case  Saturday
      strBrand = "KasteelBier"
  Case  Sunday
      strBrand = "Jupiler"
End Select

strSQL = "Select Glas, Bottle From Bar where Brand = " & chr(34) & strBrand & chr(34) & ";"

set dbs = CurrentCafe
set rst = dbs.Openrecordset(strSQL)

rst.moveFirst

While dbs = open
 
  rst.Edit
  rst.Drink
  rst.update

  rst.Movenext
Wend

set rst = empty
dbs.close
set dbs = empty

msgbox "Time to go home", vbOkonly + vbInformation, "C ya tomorrow"

End Sub
0
 
LVL 54

Expert Comment

by:nico5038
ID: 6345949
SYNTAX-ERROR rst.Drink UNKNOWN PROPERTY ;-)

(Sorry for the interrupt knowlton)

Nic;o)
0
 
LVL 5

Author Comment

by:knowlton
ID: 6346111
Thanks everyone.

btw - while I was waiting for a reply I found a cool tutorial at:

http://www.w3schools.com/sql/sql_join.asp
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

832 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