You should generally not have any conditions in the ON part ...

darkangel · #1 (**permalink**) 07-23-07, 17:48

Hi,

From the manual:

Quote:

You should generally not have any conditions in the ON part that are used to restrict which rows you want in the result set, but rather specify these conditions in the WHERE clause. There are exceptions to this rule.

Code:

DROP TABLE IF EXISTS `x`.`members`;
CREATE TABLE  `x`.`members` (
  `acc_num` varchar(9) NOT NULL default '',
  `first_name` varchar(25) NOT NULL default '',
  `last_name` varchar(25) NOT NULL default '',
  `password` varchar(20) NOT NULL default '',
  PRIMARY KEY  (`acc_num`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

The data ...

'A12345', 'Thomas', 'Jackson', 'test'

Code:

DROP TABLE IF EXISTS `x`.`units`;
CREATE TABLE  `x`.`units` (
  `project_id` varchar(5) NOT NULL default '',
  `acc_num` varchar(9) NOT NULL default '',
  `units` mediumint(8) unsigned NOT NULL default '0',
  PRIMARY KEY  (`project_id`,`acc_num`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

The data ...

'SCG01', 'A12345', 42100
'SCG02', 'A12345', 20000

I want the member details, and the units.units if units.project_id exists (otherwise a null column). This is the only thing that I could get working:

Code:

SELECT m.first_name, m.last_name, u.units
FROM members AS m
LEFT JOIN units AS u
ON u.project_id = 'SCG03'

... but, as you can see, the ON clause restricts the result set.

- Is this an exception?
- Is there another method of joining these tables this way?
- Should I just use separate queries?

TIA,

_da.

r937 · #2 (**permalink**) 07-23-07, 18:27

the relevant portion of the excerpt you quoted from the mysql docs is There are exceptions to this rule

this is one situation where you must put the condition into the ON clause

notice that you have forgotten the necessary join condition (shown in blue below) --

Code:

SELECT m.first_name
     , m.last_name
     , u.units
  FROM members AS m
LEFT OUTER
  JOIN units AS u
    ON u.acc_num = m.acc_num
   AND u.project_id = 'SCG03'

compare the results you get from that with the following --

Code:

SELECT m.first_name
     , m.last_name
     , u.units
  FROM members AS m
LEFT OUTER
  JOIN units AS u
    ON u.acc_num = m.acc_num
 WHERE u.project_id = 'SCG03'

see the difference?

aschk · #3 (**permalink**) 07-24-07, 08:28

So as to provide some understanding about what the difference is.

In rudy's improved example:

Code:

LEFT OUTER
  JOIN units AS u
    ON u.acc_num = m.acc_num
   AND u.project_id = 'SCG03'

performs the JOIN on BOTH of the clauses specified. So anything that cohere's in the joining table to BOTH acc_num and project_id = 'SCG03' will be joined.

However, in the first attempt

Code:

LEFT OUTER
  JOIN units AS u
    ON u.acc_num = m.acc_num
 WHERE u.project_id = 'SCG03'

the join is performed ON acc_num ONLY
and then once that set is complete
the WHERE clause is applied to the whole set.

Thus, in the reviewed example the LEFT JOIN ONLY occurs when both the acc_num matches AND the project_id = 'SCG03'. So, if it doesn't match (which is doesn't) it will still return a member.
In the first attempt the LEFT JOIN occurs when both acc_num matches (which is great, it will give results), but then we're testing to see if the project_id = 'SCG03', which there are no joined results for. So we get no results... DOH!

darkangel · #4 (**permalink**) 07-24-07, 10:25

You guys are brilliant!

Rudy, your second example is what I originally used, but of course, as aschk mentioned, the WHERE clause is actually working on the joined table, and not acting as the join condition.

I then moved to my example above, which nearly works (except that I needed to AND the regular join condition), but I wasn't sure if it was acceptable.

2 more basic Qs. if I may:

1) Is the use of the OUTER keyword for compliance to the SQL standard? (i.e. is it best not to omit it.)

2) I realise it makes no difference, but do you think it's best to put the joined table's field first in the join condition, or vice versa?

Thanks SO much for your help.

Regards,

_da.

r937 · #5 (**permalink**) 07-24-07, 11:12

1) OUTER is optional, but i always write it anyway

2) i always put the joined table columns first, it scans better, i.e. it allows you to see easily which table the new table is joining to

compare this --

Code:

  from frabilgimjer
inner
  join quistipunctous
    on frabilgimjer.horbliston = quistipunctous.scrimflabat
inner
  join haplistplogget
    on quistipunctous.whipfintaggle = haplistplogget.opsilfrummer
inner
  join gromulphastic
    on quistipunctous.clapfrepple = gromulphastic.corbustackle

with this --

Code:

  from frabilgimjer
inner
  join quistipunctous
    on quistipunctous.scrimflabat = frabilgimjer.horbliston
inner
  join haplistplogget
    on haplistplogget.opsilfrummer = quistipunctous.whipfintaggle
inner
  join gromulphastic
    on gromulphastic.corbustackle = quistipunctous.clapfrepple

i just think the latter is easier to understand