Determine which Table goes on Right and Left for JOINS

Question

I am really confused on RIGHT OUTER JOINs and LEFT OUTER JOINs. I am really confused on how to use them properly. The only join I know properly in the INNER JOIN. I am going to ask some silly questions, but I have ask them in order for me to understand them properly.

How do i know which table would go on the right and on the left. Is that determined by which join it is?
I am asking all this questions because I am using the AdventureWorks Databse and I was doing a LEFT JOIN on the query below

Select SalesLT.Customer.CompanyName, SalesLT.SalesOrderHeader.SubTotal, SalesLT.SalesOrderHeader.TaxAmt
FROM SalesLT.Customer
LEFT OUTER JOIN  SalesLT.SalesOrderHeader
ON  SalesLT.Customer.CustomerID  = SalesLT.SalesOrderHeader.CustomerID

This is the result I get

A Bike Store                NULL    NULL
Progressive Sports          NULL    NULL
Advanced Bike Components    NULL    NULL
Modular Cycle Systems       NULL    NULL
Metropolitan Sports Supply  NULL    NULL
Aerobic Exercise Company    NULL    NULL
Associated Bikes            NULL    NULL
Rural Cycle Emporium        NULL    NULL
Sharp Bikes                 NULL    NULL
 Bikes and Motorbikes       NULL    NULL

In the same query, I replaced the Left Outer join Join with RIght Outer Join and I got the below result

Professional Sales and Service  39785.3304  3182.8264
Remarkable Bike Store           6634.2961   530.7437
Bulk Discount Store             88812.8625  7105.029
Coalition Bike Company          2415.6727   193.2538
Futuristic Bikes                246.7392    19.7391
Channel Outlet                  550.386     44.0309
Aerobic Exercise Company        2137.231    170.9785
Vigorous Sports Store           1059.31     84.7448

I am really confused. Please explain to me what's happening hereee. It could be because I have not done the join properly. If I am wrong, correct me.

THANK YOU

I'm struggling to understand what *you* mean by "I know the definition of all the joins" if you then cannot demonstrate an understanding of joins. — Damien_The_Unbeliever, Nov 13 '14 at 13:53
possible duplicate of [Difference between INNER and OUTER joins](http://stackoverflow.com/questions/38549/difference-between-inner-and-outer-joins) — Tab Alleman, Nov 13 '14 at 13:55
The `Left` table is the table that is defined in the from clause. `Select * from student left join locker...` will return students with or without an assigned locker. Your query returns all customers whether they have a SalesOrderheader or not — CSharper, Nov 13 '14 at 14:02
Can you at least explain me, where am I going wrong with this query? It does not explain why when I use a RIGHT OUTER JOIN, its acting like an inner Join=. Please explain to me @Damien_The_Unbeliever — mfredy, Nov 13 '14 at 14:03
So for the right outer join, i need to put the right table in the from clause@ @CSharper — mfredy, Nov 13 '14 at 14:04

score 5 · Accepted Answer · answered Nov 13 '14 at 14:08

If a JOIN find matches for all rows then yes, it will look like an INNER JOIN. The OUTER part of joins is about what happens when a match cannot be found.

And the LEFT or RIGHT is saying which table's rows we always want to retain. So in a LEFT join, you'll always get all rows from the table to the left of the join, but for rows with no match on the right, we get NULLs. And for a RIGHT join, we always get all rows from the table to the right.

And as I say, if you're doing a LEFT join and every row in the left table has at least one matching row in the right table, the result will look the same as an INNER JOIN.

THANKK YOUU!! I UNDERSTAND IT A LOT BETTERR – mfredy Nov 13 '14 at 14:13 — mfredy, Nov 13 '14 at 14:13

score 4 · Answer 2 · answered Nov 13 '14 at 14:13

Return all students who have lockers

Select * from Student s 
inner join locker l on s.StudentId = l.StudentId

Return all students whether they have a locker or not.

Select * from Student s 
left join locker l on s.StudentId = l.StudentId

Return all students who have a locker, and all lockers if they have a student or not

Select * from Student s 
right join locker l on s.StudentId = l.StudentId

Determine which Table goes on Right and Left for JOINS

2 Answers2