Solved

LEFT JOIN vs RIGHT JOIN ???

Posted on 2001-08-02
7
427 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
Comment Utility
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
Comment Utility
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
Comment Utility
i've been reading it 3.526 times now Nico,

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

;-) ;-) ;-) ;-) ;-) ;-) ;-) ;-) ;-) ;-)
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 54

Expert Comment

by:nico5038
Comment Utility
Ricky, too much Hoegaarden causes reading loops.... ;-)

Nic;o)
0
 
LVL 12

Expert Comment

by:Paurths
Comment Utility
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
Comment Utility
SYNTAX-ERROR rst.Drink UNKNOWN PROPERTY ;-)

(Sorry for the interrupt knowlton)

Nic;o)
0
 
LVL 5

Author Comment

by:knowlton
Comment Utility
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

743 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

13 Experts available now in Live!

Get 1:1 Help Now