LEFT JOIN and WHERE: identical or not and what is better?

Hello everyone,
Sorry for my beginner question. Actually I have been using MySql for a
long time but I just start using some advanced things (earlier I
accomplished those tasks with PHP), so I will be asking stupid
questions, please bear with me.
Here is the first one.
We have two queries:
SELECT `blogs`.* FROM `Blogs` LEFT JOIN `Users` ON
`Blogs`.`UserId`=3D`Users`.`Id`;
and the following one:
SELECT `Blogs`.* FROM `Blogs`, `Users` WHERE
`Blogs`.`UserId`=3D`Users`.`Id`;

1. Are they identical?
2. Which is better (faster, more optimal, more kosher, I mean, better
style...)?
Thanks!
=20

--=20
With best regards from Ukraine,
Andre
Skype: Francophile
Twitter: http://twitter.com/m_elensule
Facebook: http://facebook.com/menelion


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: LEFT JOIN and WHERE: identical or not and what is better?

'where' is a filter. You're limiting records based on a criterion.

'on' is used for joining.



On Mon, Apr 25, 2011 at 10:42 AM, Andre Polykanine wrote:
> Hello everyone,
> Sorry for my beginner question. Actually I have been using MySql for a
> long =A0time =A0but =A0I =A0just =A0start =A0using some advanced things (=
earlier I
> accomplished =A0those =A0tasks =A0with =A0PHP), =A0so =A0I =A0will =A0be =
asking stupid
> questions, please bear with me.
> Here is the first one.
> We have two queries:
> SELECT `blogs`.* FROM =A0 =A0 `Blogs` =A0 =A0 LEFT =A0 =A0 JOIN =A0 =A0 `=
Users` =A0 =A0 ON
> `Blogs`.`UserId`=3D`Users`.`Id`;
> and the following one:
> SELECT =A0 =A0 =A0 `Blogs`.* =A0 =A0 =A0FROM =A0 =A0 =A0`Blogs`, =A0 =A0 =
=A0`Users` =A0 =A0 =A0WHERE
> `Blogs`.`UserId`=3D`Users`.`Id`;
>
> 1. Are they identical?
> 2. =A0Which is better (faster, more optimal, more kosher, I mean, better
> style...)?
> Thanks!
>
>
> --
> With best regards from Ukraine,
> Andre
> Skype: Francophile
> Twitter: http://twitter.com/m_elensule
> Facebook: http://facebook.com/menelion
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dmmalten@gmail=
..com
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: LEFT JOIN and WHERE: identical or not and what is better?

I can be wrong about that, but I think the difference between them should be
irrelevant so it makes me think about a paranoiac thought.

For me, the only difference is: Chose the one you feel better to understand
your code.

Am I wrong or not?

--
João Cândido de Souza Neto

"Andre Polykanine" escreveu na mensagem
news:199779304.20110425174239@oire.org...
Hello everyone,
Sorry for my beginner question. Actually I have been using MySql for a
long time but I just start using some advanced things (earlier I
accomplished those tasks with PHP), so I will be asking stupid
questions, please bear with me.
Here is the first one.
We have two queries:
SELECT `blogs`.* FROM `Blogs` LEFT JOIN `Users` ON
`Blogs`.`UserId`=`Users`.`Id`;
and the following one:
SELECT `Blogs`.* FROM `Blogs`, `Users` WHERE
`Blogs`.`UserId`=`Users`.`Id`;

1. Are they identical?
2. Which is better (faster, more optimal, more kosher, I mean, better
style...)?
Thanks!


--
With best regards from Ukraine,
Andre
Skype: Francophile
Twitter: http://twitter.com/m_elensule
Facebook: http://facebook.com/menelion



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: LEFT JOIN and WHERE: identical or not and what is better?

--000e0cd3ff6842bbe904a1bfb0a1
Content-Type: text/plain; charset=ISO-8859-1

The only difference once MySQL parses these two queries is the first one is
a LEFT JOIN, which will produce all records from the blogs table even if
there is no matching record in the users table. The second query produces an
INNER JOIN which means only rows with matching records in both tables will
be returned.

Which one is faster? Probably the second since NULLs do not have to be
considered -- probably not much faster though.
Which one is better? That'll depend on your needs, if you only need records
from both tables that have a matching row in the other, the second is
better. If you need all blogs, even those without a matching user (can that
even occur?), the first one is better.

I prefer to write the INNER JOIN out though because it leaves my WHERE
clause to do filtering.

JW


On Mon, Apr 25, 2011 at 9:42 AM, Andre Polykanine wrote:

> Hello everyone,
> Sorry for my beginner question. Actually I have been using MySql for a
> long time but I just start using some advanced things (earlier I
> accomplished those tasks with PHP), so I will be asking stupid
> questions, please bear with me.
> Here is the first one.
> We have two queries:
> SELECT `blogs`.* FROM `Blogs` LEFT JOIN `Users` ON
> `Blogs`.`UserId`=`Users`.`Id`;
> and the following one:
> SELECT `Blogs`.* FROM `Blogs`, `Users` WHERE
> `Blogs`.`UserId`=`Users`.`Id`;
>
> 1. Are they identical?
> 2. Which is better (faster, more optimal, more kosher, I mean, better
> style...)?
> Thanks!
>
>
> --
> With best regards from Ukraine,
> Andre
> Skype: Francophile
> Twitter: http://twitter.com/m_elensule
> Facebook: http://facebook.com/menelion
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=johnny@pixelated.net
>
>


--
-----------------------------
Johnny Withers
601.209.4985
johnny@pixelated.net

--000e0cd3ff6842bbe904a1bfb0a1--

Re: LEFT JOIN and WHERE: identical or not and what is better?

Hi Andre, everybody!


Andre Polykanine wrote:
> Hello everyone,
> Sorry for my beginner question. Actually I have been using MySql for a
> long time but I just start using some advanced things (earlier I
> accomplished those tasks with PHP), so I will be asking stupid
> questions, please bear with me.
> Here is the first one.
> We have two queries:
> SELECT `blogs`.* FROM `Blogs` LEFT JOIN `Users` ON
> `Blogs`.`UserId`=`Users`.`Id`;
> and the following one:
> SELECT `Blogs`.* FROM `Blogs`, `Users` WHERE
> `Blogs`.`UserId`=`Users`.`Id`;
>
> 1. Are they identical?
> 2. Which is better (faster, more optimal, more kosher, I mean, better
> style...)?

In your subject line, you are mixing unrelated things:

- "LEFT JOIN" is an alternative to "INNER JOIN".
It tell the database to return not only matching row combinations but
also those where the first (left-hand) table has a row with a NULL
column.

- "ON" is an alternative to "WHERE".

For both aspects, the manual has more information than I will be able to
think of in this mail.

So the real difference between your statements is not "LEFT JOIN" vs
"WHERE", or "ON" vs "WHERE", it is "LEFT JOIN" vs "inner join".
It will become important if you have rows in table "Blogs" whose column
"UserId" holds NULL rather than any definite value.

To understand that, you will have to read about NULL and the
three-valued logic of SQL (whose comparisons can return true, false, and
unknown).

In general, an inner join should be faster - but who cares?
SQL statements must be coded for correctness first, not for speed - and
that will determine your choice of "LEFT JOIN" vs "inner join".
Tuning and optimization come later (in priority).

The same holds for style etc: Clean programming is nice (and helpful in
understanding and maintenance), but correctness comes first.


Regards,
Joerg

--
Joerg Bruehe, MySQL Build Team, joerg.bruehe@oracle.com
ORACLE Deutschland B.V. & Co. KG, Komturstrasse 18a, D-12099 Berlin
Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven
Amtsgericht Muenchen: HRA 95603

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: LEFT JOIN and WHERE: identical or not and what is better?, etc.

>>>> 2011/04/25 17:42 +0300, Andre Polykanine >>>>
Here is the first one.
We have two queries:
SELECT `blogs`.* FROM `Blogs` LEFT JOIN `Users` ON
`Blogs`.`UserId`=`Users`.`Id`;
and the following one:
SELECT `Blogs`.* FROM `Blogs`, `Users` WHERE
`Blogs`.`UserId`=`Users`.`Id`;

1. Are they identical?
2. Which is better (faster, more optimal, more kosher, I mean, better
style...)?
<<<<<<<<

>>>> 2011/04/25 10:16 -0500, Johnny Withers >>>>
The only difference once MySQL parses these two queries is the first one is
a LEFT JOIN, which will produce all records from the blogs table even if
there is no matching record in the users table. The second query produces an
INNER JOIN which means only rows with matching records in both tables will
be returned.

....

I prefer to write the INNER JOIN out though because it leaves my WHERE
clause to do filtering.
<<<<<<<<
and it is usual to write all about the joining in the FROM-clause --the tables and the criterion for joining them-- and reserve the WHERE-clause for filtering the result:

SELECT `blogs`.* FROM `Blogs` LEFT JOIN `Users` ON `Blogs`.`UserId` = `Users`.`Id`

SELECT `Blogs`.* FROM `Blogs` JOIN `Users` ON `Blogs`.`UserId` = `Users`.`Id`

That is, if you already had a table with the joined outcome, you would use the WHERE-clause to determine what of it enters into further processing.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: LEFT JOIN and WHERE: identical or not and what is better?, etc.

Hello Halбsz,

Aha. So, I should write
SELECT `Blogs`.* INNER JOIN `Users` ON `Users`.`Id`=3D`Blogs`.`UserId`
instead of my original WHERE clause?
Thanks!

--=20
With best regards from Ukraine,
Andre
Skype: Francophile
My blog: http://oire.org/menelion (mostly in Russian)
Twitter: http://twitter.com/m_elensule
Facebook: http://facebook.com/menelion

------------ Original message ------------
From: Halбsz Sбndor
To: Andre Polykanine
Date created: , 7:00:03 AM
Subject: LEFT JOIN and WHERE: identical or not and what is better?, etc.


>>>> 2011/04/25 17:42 +0300, Andre Polykanine >>>>
Here is the first one.
We have two queries:
SELECT `blogs`.* FROM `Blogs` LEFT JOIN `Users` ON
`Blogs`.`UserId`=3D`Users`.`Id`;
and the following one:
SELECT `Blogs`.* FROM `Blogs`, `Users` WHERE
`Blogs`.`UserId`=3D`Users`.`Id`;

1. Are they identical?
2. Which is better (faster, more optimal, more kosher, I mean, better
style...)?
<<<<<<<<=20

>>>> 2011/04/25 10:16 -0500, Johnny Withers >>>>
The only difference once MySQL parses these two queries is the first one is
a LEFT JOIN, which will produce all records from the blogs table even if
there is no matching record in the users table. The second query produces an
INNER JOIN which means only rows with matching records in both tables will
be returned.

....

I prefer to write the INNER JOIN out though because it leaves my WHERE
clause to do filtering.
<<<<<<<<
and it is usual to write all about the joining in the FROM-clause --the tab=
les and the criterion for joining them-- and reserve the WHERE-clause for f=
iltering the result:

SELECT `blogs`.* FROM `Blogs` LEFT JOIN `Users` ON `Blogs`.`UserId` =3D `Us=
ers`.`Id`

SELECT `Blogs`.* FROM `Blogs` JOIN `Users` ON `Blogs`.`UserId` =3D `Users`.=
`Id`

That is, if you already had a table with the joined outcome, you would use =
the WHERE-clause to determine what of it enters into further processing.



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: LEFT JOIN and WHERE: identical or not and what is better?, etc.

>>>> 2011/04/26 17:55 +0300, Andre Polykanine >>>>
Aha. So, I should write
SELECT `Blogs`.* INNER JOIN `Users` ON `Users`.`Id`=`Blogs`.`UserId`
instead of my original WHERE clause?
Thanks!
<<<<<<<<
I think so.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org