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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
