List:General Discussion« Previous MessageNext Message »
From:Shawn Green (MySQL) Date:October 19 2011 4:00pm
Subject:Re: What is wrong with this outer join?
View as plain text  
Hello Dotan,

On 10/19/2011 09:57, Dotan Cohen wrote:
> mysql>  select * from beers;
> +----+-----------+--------+
> | ID | name      | colour |
> +----+-----------+--------+
> |  1 | carlsburg |      2 |
> |  2 | tuburg    |      1 |
> |  3 | tuburg    |      9 |
> +----+-----------+--------+
> 3 rows in set (0.00 sec)
>
> mysql>  select * from colours;
> +----+--------+
> | id | colour |
> +----+--------+
> |  1 | red    |
> |  2 | green  |
> |  3 | blue   |
> +----+--------+
> 3 rows in set (0.00 sec)
>
> mysql>  select * from beers inner join colours on beers.colour = colours.ID;
> +----+-----------+--------+----+--------+
> | ID | name      | colour | id | colour |
> +----+-----------+--------+----+--------+
> |  1 | carlsburg |      2 |  2 | green  |
> |  2 | tuburg    |      1 |  1 | red    |
> +----+-----------+--------+----+--------+
> 2 rows in set (0.00 sec)
>
> mysql>  select * from beers outer join colours on beers.colour = colours.ID;
> ERROR 1064 (42000): You have an error in your SQL syntax; check the
> manual that corresponds to your MySQL server version for the right
> syntax to use near 'outer join colours on beers.colour = colours.ID'
> at line 1
>
>
> So I've gone looking the fine manual, here:
> http://dev.mysql.com/doc/refman/5.6/en/join.html
>
> The manual references natural outer joins and requires curly brackets
> and I'm frankly not making sense of it. Left, right, and inner joins
> work as I expect them too, and fishing for examples in google doesn't
> find anything unusual. How exactly am I erring?
>
> Thanks!
>

This is a simple misunderstanding. From the page you quote, the syntax 
patterns for an OUTER join are these:

   | table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference 
join_condition

   | table_reference NATURAL [{LEFT|RIGHT} [OUTER]] JOIN table_factor

Notice that in the second, the [OUTER] is nested inside of [{LEFT|RIGHT} 
[OUTER]] and in the first it follows the NON-OPTIONAL choice of 
{LEFT|RIGHT).  Neither one of these syntax patterns allows the keyword 
OUTER to appear without either the LEFT or RIGHT keyword before it.

To make this crystal clear those patterns allow LEFT JOIN, RIGHT JOIN, 
LEFT OUTER JOIN, or RIGHT OUTER JOIN but not just OUTER JOIN.

Regards,
-- 
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN
Thread
What is wrong with this outer join?Dotan Cohen19 Oct
  • Re: What is wrong with this outer join?MySQL)19 Oct
    • Re: What is wrong with this outer join?Dotan Cohen19 Oct
      • Re: What is wrong with this outer join?MySQL)19 Oct
        • Re: What is wrong with this outer join?Dotan Cohen20 Oct
          • Re: What is wrong with this outer join?MySQL)20 Oct
            • Re: What is wrong with this outer join?Peter Brawley20 Oct
            • Re: What is wrong with this outer join?Dotan Cohen20 Oct
Re: What is wrong with this outer join?Dotan Cohen19 Oct
  • Re: What is wrong with this outer join?hsv20 Oct