The real difference between LEFT and RIGHT JOINS

[Marek Kotowski]

Hi,

What is the real difference between LEFT JOIN and RIGHT JOIN?
Are there situations where a query with RIGHT JOIN cannot
be rewritten with LEFT JOIN and tables reversed? (and vice versa).

In MySQL documentation there is stated explicitly:
"RIGHT JOIN is implemented analogously to LEFT JOIN, with the roles of
the tables reversed."

So may be it would be enough to have one universal LEFTRIGHT join
and put tables in a query in proper places depending on what
results are needed? What are two different JOIN syntaxes for?

Thank you in advance.

Marek Kotowski
Warsaw

[jerry gitomer]

Re: The real difference between LEFT and RIGHT JOINS

Marek Kotowski wrote:[color=blue]
> Hi,
>
> What is the real difference between LEFT JOIN and RIGHT JOIN?
> Are there situations where a query with RIGHT JOIN cannot
> be rewritten with LEFT JOIN and tables reversed? (and vice versa).
>
> In MySQL documentation there is stated explicitly:
> "RIGHT JOIN is implemented analogously to LEFT JOIN, with the roles of
> the tables reversed."
>
> So may be it would be enough to have one universal LEFTRIGHT join
> and put tables in a query in proper places depending on what
> results are needed? What are two different JOIN syntaxes for?
>
> Thank you in advance.
>
> Marek Kotowski
> Warsaw[/color]

What would a LEFTRIGHT OUTER JOIN do?

Aside from that the order in which tables are specified in the
FROM should have no bearing on the performance of the query
since optimizers select the order of the tables based on
statistical analysis rather than specification order.

Jerry

[Bill Karwin]

Re: The real difference between LEFT and RIGHT JOINS

Marek Kotowski wrote:[color=blue]
> So may be it would be enough to have one universal LEFTRIGHT join
> and put tables in a query in proper places depending on what
> results are needed?[/color]

Yes, there is such a thing. It is spelled "LEFT JOIN". Just list the
tables in the specific order, based on which result you want.

Or if it's easier for your editor to overwrite a word, instead of cut
and paste table names to reverse their order, change "LEFT" to "RIGHT".
[color=blue]
> What are two different JOIN syntaxes for?[/color]

Keep in mind that the SQL-92 language was designed by a committee. ;-)

Regards,
Bill K.

[Marek Kotowski]

Re: The real difference between LEFT and RIGHT JOINS

Bill Karwin <bill@karwin.co m> wrote in message news:<d3h1au016 hr@enews2.newsg uy.com>...[color=blue]
>[color=green]
> > What are two different JOIN syntaxes for?[/color]
>
> Keep in mind that the SQL-92 language was designed by a committee. ;-)
>
> Regards,
> Bill K.[/color]

Thank you. So I see (am I right?) that - to say the truth -
enough would be always to use LEFT JOIN and just
to manipulate tables. This way one can always get results
he needs. No real functionality lost.

Regards

Marek Kotowski
Warsaw

[Bill Michaelson]

Re: The real difference between LEFT and RIGHT JOINS

Perhaps it's an accomodation for left-handed programmers.

Marek Kotowski wrote:[color=blue]
> Bill Karwin <bill@karwin.co m> wrote in message news:<d3h1au016 hr@enews2.newsg uy.com>...
>[color=green][color=darkred]
>>>What are two different JOIN syntaxes for?[/color]
>>
>>Keep in mind that the SQL-92 language was designed by a committee. ;-)
>>
>>Regards,
>>Bill K.[/color]
>
>
> Thank you. So I see (am I right?) that - to say the truth -
> enough would be always to use LEFT JOIN and just
> to manipulate tables. This way one can always get results
> he needs. No real functionality lost.
>
> Regards
>
> Marek Kotowski
> Warsaw[/color]