Solved

LEFT JOIN vs RIGHT JOIN ???

Posted on 2001-08-02
7
428 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
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…

930 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

10 Experts available now in Live!

Get 1:1 Help Now