Bug #4581 Allow use of *= and =* for outer joins in addition to ANSI standard syntax
Submitted: 17 Jul 2004 12:59 Modified: 25 Dec 2005 12:46
Reporter: Dave Boulden Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S4 (Feature request)
Version:All OS:Any (All)
Assigned to: CPU Architecture:Any

[17 Jul 2004 12:59] Dave Boulden
Description:
I would like to request this feature be added to the MySQL SQL SELECT syntax. It is to allow the OUTER JOIN syntax as used by Sybase, MS-SQL and, I believe, Oracle also. I would like to be able to specify OUTER joins from the WHERE clause by use of the following syntax: "SELECT a.ID, b.field FROM tablea a, tableb b WHERE tablea.ID =* tableb.ID" as an alternative to specifying "SELECT a.ID, b.field FROM tablea a RIGHT OUTER JOIN tableb b ON (a.ID=b.ID)".
Although it is not part of the ANSI standard, it is commonly used within 3 widely implemented database systems (Sybase, Oracle, MS-SQL) and I believe it would be a useful addition to MySQL, especially when migrating applications from one of these databases to MySQL.

How to repeat:
N/A

Suggested fix:
N/A
[6 Jul 2005 19:29] Shawn Green
I disagree. This was not a good decision by Oracle to start doing it and the fact that the others followed suit does not make it something we need to do.

Let me quote from section 7.7 of the SQL:1999 specification (ISO/IEC 9075-2:1999 (E)) covering the syntax of creating table joins:

<joined table> ::=
<cross join>
| <qualified join>
| <natural join>
| <union join>
<cross join> ::=
<table reference> CROSS JOIN <table primary>
<qualified join> ::=
<table reference> [ <join type> ] JOIN <table reference>
<join specification>
<natural join> ::=
<table reference> NATURAL [ <join type> ] JOIN <table primary>
<union join> ::=
<table reference> UNION JOIN <table primary>
<join specification> ::=
<join condition>
| <named columns join>
<join condition> ::= ON <search condition>
<named columns join> ::=
USING <left paren> <join column list> <right paren>
<join type> ::=
INNER
| <outer join type> [ OUTER ]
<outer join type> ::=
LEFT
| RIGHT
| FULL
<join column list> ::= <column name list>

This does not say that a comma separated list is acceptable nor does it allow for the =* format. MySQL already allows the comma-separated list please don't make it become LESS compliant by adding the =* comparitor as well.  Thank you...
[6 Jul 2005 20:54] Dave Boulden
My point is, though, that since MSSQL, Sybase and Oracle allow this short-hand form, it becomes onerous to have to use the overly verbose ANSI standard just for MySQL. This is particularly awkward when trying to keep your code-base common no matter which database is being used. I can currently use my code on MS-SQL, Sybase and probably Oracle too with no changes (apart from an abstraction layer in some languages) but when it comes to outer joins I am forced to use "special case" SQL statements for MySQL. I also find that the ANSI standard outer join syntax is difficult to read when revisiting code... the shorthand form is much easier to understand and makes the nature of the joins more instantly obvious.
[25 Dec 2005 12:28] Valeriy Kravchuk
Thank you for a feature request. Now most RDBMS vendors, including Oracle, do support ANSI SQL Standard syntax for (outer) joins. So, the right way to create portable SQL (if it looks like a good idea for you) is to conform to standard, not to vendor-specific features. 

I am not sure that MySQL will implement (debatable) SQL extensions from all other vendors - it has a plenty of its own. 

In Oracle (+), but not =*, is used as propriatary way to express outer join, by the way.
[25 Dec 2005 12:46] Dave Boulden
OK... it's a shame since the *= and (+) formats are far more readable than the verbose "OUTER JOIN" ANSI standard.

Never the less, thankyou for at least considering the request.
[25 Dec 2005 13:21] Valeriy Kravchuk
My first serious RDBMS was Oracle, so I agree with you that (+) is, in many cases, is more readable and gives smaller SQL statements. 

But in other cases, like NATURAL JOIN or FULL OUTER JOIN (you'll need to use UNION with Oracle's syntax for it), ANSI way is simpler. So, please, try to use ANSI syntax and, after some time, it will become equally natural.

I am sorry that your feature request was not considered properly for so long time.