1,105,416 Community Members

RIGHT JOIN, LEFT JOIN, ms access

Member Avatar
castajiz_2
Posting Whiz in Training
235 posts since Apr 2012
Reputation Points: 35 [?]
Q&As Helped to Solve: 22 [?]
Skill Endorsements: 3 [?]
 
0
 

Hi guys
I have a little mess up in my head going on i dont really know why.
My question is how come a right join AND a left join were made to handle database queries, why both of them, isn t one enough?
for instance let s take the left join.
syntax:

SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name

and we get all the rows from table_name1 and rows from the other table which do have a match
Now if we SWITCH the tables
syntax:

 SELECT column_name(s)
    FROM table_name2
    LEFT JOIN table_name1
    ON table_name1.column_name=table_name2.column_name

and we get all the rows from table_name2 which we could have done with RIGHT JOIN as well but we would have to switch tables.

Once again WHY isn t a for example Left join enough why does a right join exist? VICE VERSA.

Member Avatar
code739
Posting Whiz in Training
213 posts since May 2012
Reputation Points: 17 [?]
Q&As Helped to Solve: 30 [?]
Skill Endorsements: 6 [?]
 
0
 
Member Avatar
JorgeM
IT Addict
6,414 posts since Dec 2011
Reputation Points: 581 [?]
Q&As Helped to Solve: 963 [?]
Skill Endorsements: 172 [?]
Moderator
Featured
Sponsor
 
1
 

yes you are correct that in the scenario you described, you can just simply swap table1 and 2 in your statement and you can get away with just using the right or the left. however, in the bigger picture, you have to consider that you will first set up your database design, relationships and then data will be populated. You may be flexible in what type of data gets entered, allowing null values and you may need to create reports that include these null values so the left and right joins would be necessary where you dont have records that match. You may want to include all of the rows in table1 even if there isnt a match in table2 and vice versa. You arent going to be modifying these queries after the program is developed.

If its just running manual queries in the DB interface, then yes you can get comfortable with a left or right join and you control how to include the table names in your query.

Question Answered as of 1 Year Ago by JorgeM and code739
Member Avatar
castajiz_2
Posting Whiz in Training
235 posts since Apr 2012
Reputation Points: 35 [?]
Q&As Helped to Solve: 22 [?]
Skill Endorsements: 3 [?]
 
0
 

thanks man!

Member Avatar
adam_k
Veteran Poster
1,056 posts since Jun 2011
Reputation Points: 239 [?]
Q&As Helped to Solve: 212 [?]
Skill Endorsements: 17 [?]
 
0
 

Look at it this way:

select a.column1, b.column2, c.column3 
from tablea a 
left join tableb b
on a.id = b.a_id 
right join tablec c 
on a.id = c.a_id
and b.id = c.b_id

Keep adding tables and changing directions. If we only had left or right it wouldn't be possible.

You
This question has already been solved: Start a new discussion instead
Post:
Start New Discussion
Tags Related to this Article