Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2005
    Location
    Cape Town, South Africa
    Posts
    57

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

    Hi,

    From the manual:

    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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    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!
    Last edited by aschk; 07-24-07 at 09:33.

  4. #4
    Join Date
    Feb 2005
    Location
    Cape Town, South Africa
    Posts
    57
    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.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Mar 2004
    Posts
    480
    Isn't it corNbustackle?

  7. #7
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    In MySQL, there is no difference between LEFT OUTER JOIN and LEFT JOIN, however you may find there IS a different in other database systems.

  8. #8
    Join Date
    Mar 2004
    Posts
    480
    Why would there be?

  9. #9
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Apologies i'm being a numpty, LEFT OUTER JOIN is a synonym for LEFT JOIN. For some reason or another I was telling myself these were different.

  10. #10
    Join Date
    Mar 2004
    Posts
    480
    I mostly use Mysql, some postgres, I've only dabbled with Oracle and Microsoft Server a wee bit. that's why I was asking.

  11. #11
    Join Date
    Feb 2005
    Location
    Cape Town, South Africa
    Posts
    57
    Thanks Rudy.

    ... where on earth do those words come from?

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    they are taken from my virtual database of meaningless names

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •