When is a Left table 'Left' and a right table 'Right'

[vincej]

HI - For years I have avoided using Joins prefering the more obvious 'where' clause. Time I caught up .. and so in reading the literature on Joins I ask the obvious question: how do you tell if a table is Left or right .. I mean you can hardly put them on the desk to look ! All of the literature I have read assumes you just 'know by magic'.

 

Sorry if this is a silly question - but the obvious can defeat us !

[kicken]

It's their position relative to the JOIN.

 

FROM tableA JOIN tableB

 

tableA is the left table

tableB is the right table.

 

When you specify LEFT, RIGHT, or INNER join it determines what happens when rows don't match up.

 

For an INNER JOIN if a match can't be found in tableB for a row in table A then the row is dropped from the result set.  Likewise only rows from tableB that match a row in tableA are kept, the others are dropped.

 

For a LEFT join then all the rows from the LEFT table (tableA) are returned.  If a matching row cannot be found in the right table, those columns are filled will NULL values.

 

For a RIGHT join then all the rows from the RIGHT table (tableB) are returned.  If a matching row cannot be found in the left table, those columns are filled with NULL values.

 

There's also a  CROSS JOIN where all the rows from both tables are kept.  There's no condition for this type of join, each row from tableA is matched up to all rows in table B.

 

[cpd]

It's their position relative to the JOIN.

 

FROM tableA JOIN tableB

 

tableA is the left table

tableB is the right table.

 

When you specify LEFT, RIGHT, or INNER join it determines what happens when rows don't match up.

 

For an INNER JOIN if a match can't be found in tableB for a row in table A then the row is dropped from the result set.  Likewise only rows from tableB that match a row in tableA are kept, the others are dropped.

 

For a LEFT join then all the rows from the LEFT table (tableA) are returned.  If a matching row cannot be found in the right table, those columns are filled will NULL values.

 

For a RIGHT join then all the rows from the RIGHT table (tableB) are returned.  If a matching row cannot be found in the left table, those columns are filled with NULL values.

 

There's also a  CROSS JOIN where all the rows from both tables are kept.  There's no condition for this type of join, each row from tableA is matched up to all rows in table B.

 

That's the clearest explanation of JOINS I've ever read. Deserves a sticky!

[vincej]

Thanks CPD that's excellent - next silly question: when constructing  a query, how do you decide which table should be left and which right .. you could put them either way around , no ?

 

cheers !

[vincej]

Sorry Kicken ! I am grateful for you r clear answer and my follow-up was meant for you ! 

[Muddy_Funster]

it's common to stick with one direction of join, and left is the most popular, so you would normaly code a query to use only left joins (and any inner / cross joins that were also required) using the layout that kicken explained.

[kicken]

Thanks CPD that's excellent - next silly question: when constructing  a query, how do you decide which table should be left and which right .. you could put them either way around , no ?

 

You could put them in either direction for the most part.  I find it easier to conceptualize a query using left joins.  I usually will have one table that would be considered the source table of the data, and another that has additional information.  I make the source table the left and the additional table the right.  For example, if I wanted a list of all students and their invoices I'd do:

select * from students left join invoices on students.StudentId=invoices.InvoiceId

 

As conceptually to me I want to first grab a list of all the students, then look into the invoices for any matching rows.

 

Use which ever form makes the most sense to you though when your writing your queries.

 

[vincej]

Thanks Kicken, OK - this makes sense. as I said at the outset, I have used the old fashioned 'where' clause for years, and conceptually it always seems to be easier to grasp.  I have been playing around for a day or two with the 4 different join types on a simple 2 table situation, and so far I have not yet really been able to see a significant benefit to using JOIN. In fact if you use Codeigniter, as I do, there they still give it prominence in their classes.  Am I missing something ??

 

Many Thanks !

[kicken]

You can build up some more complex join conditions by using the join syntax.  Aside from that though the main reason I prefer them is it keeps your join conditions separated from your where conditions and makes the query easier to read, especially when you get into some bigger more complex queries.

 

For example

SELECT
sesuni.ADM_UN_ID as universityId,
sesuni.ADM_UN_name as universityName,
sescam.ADM_CAM_ID as campusId,
sescam.ADM_CAM_name as campusName,
ses.ADM_SES_ID as sessionId,
ses.ADM_SES_name as sessionName,
cat.ADM_CAT_ID as catalogId,
cat.ADM_CAT_name as catalogName,
sesoff.ADM_SES_OFF_ID as offeringId
FROM session ses 
INNER JOIN academic_years sesay ON ses.ADM_SES_ADM_AY_ID=sesay.ADM_AY_ID
INNER JOIN campus sescam ON sesay.ADM_AY_ADM_CAM_ID=sescam.ADM_CAM_ID
INNER JOIN universities sesuni ON sescam.ADM_CAM_ADM_UN_ID=sesuni.ADM_UN_ID
INNER JOIN session_offerings sesoff ON sesoff.ADM_SES_OFF_ADM_SES_ID=ses.ADM_SES_ID AND sesoff.DBODeleted=0
INNER JOIN catalog cat ON sesoff.ADM_SES_OFF_ADM_CAT_ID=cat.ADM_CAT_ID AND cat.DBODeleted=0
WHERE
ses.DBODeleted=0 
AND SYSUTCDATETIME() BETWEEN ses.ADM_SES_reg_start_date AND ses.ADM_SES_reg_end_date

 

vs something like

SELECT
sesuni.ADM_UN_ID as universityId,
sesuni.ADM_UN_name as universityName,
sescam.ADM_CAM_ID as campusId,
sescam.ADM_CAM_name as campusName,
ses.ADM_SES_ID as sessionId,
ses.ADM_SES_name as sessionName,
cat.ADM_CAT_ID as catalogId,
cat.ADM_CAT_name as catalogName,
sesoff.ADM_SES_OFF_ID as offeringId
FROM session ses, academic_years sesay, campus sescam, universities sesuni, session_offerings sesoff, catalog cat 
WHERE
ses.ADM_SES_ADM_AY_ID=sesay.ADM_AY_ID
AND sesay.ADM_AY_ADM_CAM_ID=sescam.ADM_CAM_ID
AND sescam.ADM_CAM_ADM_UN_ID=sesuni.ADM_UN_ID
AND sesoff.ADM_SES_OFF_ADM_SES_ID=ses.ADM_SES_ID AND sesoff.DBODeleted=0
AND sesoff.ADM_SES_OFF_ADM_CAT_ID=cat.ADM_CAT_ID AND cat.DBODeleted=0
AND ses.DBODeleted=0 
AND SYSUTCDATETIME() BETWEEN ses.ADM_SES_reg_start_date AND ses.ADM_SES_reg_end_date

 

In the first all the conditions related to how the tables join together are nicely separated and associated with their particular join.  In the later it's all just one big mess in the where condition.

 

That's actually a pretty simple and basic query compared to a lot of what I do each day.

 

[fenway]

That's the clearest explanation of JOINS I've ever read. Deserves a sticky!

Agreed -- done.