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

## Recommended Posts

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 !

##### Share on other sites

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.

##### Share on other sites

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!

##### Share on other sites

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 !

##### Share on other sites

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

##### Share on other sites

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.

##### Share on other sites

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.

##### Share on other sites

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 !

##### Share on other sites

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
FROM session ses
WHERE
ses.DBODeleted=0
```

vs something like

```SELECT
FROM session ses, academic_years sesay, campus sescam, universities sesuni, session_offerings sesoff, catalog cat
WHERE
AND ses.DBODeleted=0
```

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.

##### Share on other sites

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

Agreed -- done.

## Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

×   Pasted as rich text.   Restore formatting

Only 75 emoji are allowed.