Particular value or NULL

------=_NextPart_000_0234_01CAC03F.4D94F200
Content-Type: text/plain;
charset="UTF-8"
Content-Transfer-Encoding: 7bit

Is there a better construct for the WHERE clause in a LEFT JOIN than



WHERE (x = 17 OR x IS NULL)



?



Regards,



Jerry Schwartz

The Infoshop by Global Information Incorporated

195 Farmington Ave.

Farmington, CT 06032



860.674.8796 / FAX: 860.674.8341



www.the-infoshop.com




------=_NextPart_000_0234_01CAC03F.4D94F200--

Re: Particular value or NULL

Maybe:

WHERE coalesce(x, 17) = 17


""Jerry Schwartz"" escreveu na mensagem
news:023301cac069$366afa00$a340ee00$@com...
> Is there a better construct for the WHERE clause in a LEFT JOIN than
>
>
>
> WHERE (x = 17 OR x IS NULL)
>
>
>
> ?
>
>
>
> Regards,
>
>
>
> Jerry Schwartz
>
> The Infoshop by Global Information Incorporated
>
> 195 Farmington Ave.
>
> Farmington, CT 06032
>
>
>
> 860.674.8796 / FAX: 860.674.8341
>
>
>
> www.the-infoshop.com
>
>
>
>



--
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: Particular value or NULL

>-----Original Message-----
>From: João Cândido de Souza Neto [mailto:joao@consultorweb.cnt.br]
>Sent: Wednesday, March 10, 2010 10:55 AM
>To: mysql@lists.mysql.com
>Subject: Re: Particular value or NULL
>
>Maybe:
>
>WHERE coalesce(x, 17) = 17
>
>
[JS] Interesting suggestion, but

us-gii >select benchmark(10000000,(7=7 or null is null));
+-------------------------------------------+
| benchmark(10000000,(7=7 or null is null)) |
+-------------------------------------------+
| 0 |
+-------------------------------------------+
1 row in set (0.34 sec)

us-gii >select benchmark(100000000,coalesce(null,7));
+---------------------------------------+
| benchmark(100000000,coalesce(null,7)) |
+---------------------------------------+
| 0 |
+---------------------------------------+
1 row in set (2.61 sec)

It looks like COALESCE() is slower. Of course this isn't anything like a real
test. Among other things, I have no idea how well or poorly the optimizer,
query cache, etc. handle it. I don't know how much magic there is in the
BENCHMARK() function, either. I would hope that the server would know that the
expression needs to be evaluated over and over again from scratch.

Regards,

Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341

www.the-infoshop.com




>""Jerry Schwartz"" escreveu na mensagem
>news:023301cac069$366afa00$a340ee00$@com...
>> Is there a better construct for the WHERE clause in a LEFT JOIN than
>>
>>
>>
>> WHERE (x = 17 OR x IS NULL)
>>
>>
>>
>> ?
>>
>>
>>
>> Regards,
>>
>>
>>
>> Jerry Schwartz
>>
>> The Infoshop by Global Information Incorporated
>>
>> 195 Farmington Ave.
>>
>> Farmington, CT 06032
>>
>>
>>
>> 860.674.8796 / FAX: 860.674.8341
>>
>>
>>
>> www.the-infoshop.com
>>
>>
>>
>>
>
>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe: http://lists.mysql.com/mysql?unsub=jschwartz@the-
>infoshop.com





--
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: Particular value or NULL

Did you gave a try on using coalesce in your query?

""Jerry Schwartz"" escreveu na mensagem
news:02a201cac083$e07a2330$a16e6990$@com...
> >-----Original Message-----
>>From: João Cândido de Souza Neto [mailto:joao@consultorweb.cnt.br]
>>Sent: Wednesday, March 10, 2010 10:55 AM
>>To: mysql@lists.mysql.com
>>Subject: Re: Particular value or NULL
>>
>>Maybe:
>>
>>WHERE coalesce(x, 17) = 17
>>
>>
> [JS] Interesting suggestion, but
>
> us-gii >select benchmark(10000000,(7=7 or null is null));
> +-------------------------------------------+
> | benchmark(10000000,(7=7 or null is null)) |
> +-------------------------------------------+
> | 0 |
> +-------------------------------------------+
> 1 row in set (0.34 sec)
>
> us-gii >select benchmark(100000000,coalesce(null,7));
> +---------------------------------------+
> | benchmark(100000000,coalesce(null,7)) |
> +---------------------------------------+
> | 0 |
> +---------------------------------------+
> 1 row in set (2.61 sec)
>
> It looks like COALESCE() is slower. Of course this isn't anything like a
> real
> test. Among other things, I have no idea how well or poorly the optimizer,
> query cache, etc. handle it. I don't know how much magic there is in the
> BENCHMARK() function, either. I would hope that the server would know that
> the
> expression needs to be evaluated over and over again from scratch.
>
> Regards,
>
> Jerry Schwartz
> The Infoshop by Global Information Incorporated
> 195 Farmington Ave.
> Farmington, CT 06032
>
> 860.674.8796 / FAX: 860.674.8341
>
> www.the-infoshop.com
>
>
>
>
>>""Jerry Schwartz"" escreveu na mensagem
>>news:023301cac069$366afa00$a340ee00$@com...
>>> Is there a better construct for the WHERE clause in a LEFT JOIN than
>>>
>>>
>>>
>>> WHERE (x = 17 OR x IS NULL)
>>>
>>>
>>>
>>> ?
>>>
>>>
>>>
>>> Regards,
>>>
>>>
>>>
>>> Jerry Schwartz
>>>
>>> The Infoshop by Global Information Incorporated
>>>
>>> 195 Farmington Ave.
>>>
>>> Farmington, CT 06032
>>>
>>>
>>>
>>> 860.674.8796 / FAX: 860.674.8341
>>>
>>>
>>>
>>> www.the-infoshop.com
>>>
>>>
>>>
>>>
>>
>>
>>
>>--
>>MySQL General Mailing List
>>For list archives: http://lists.mysql.com/mysql
>>To unsubscribe: http://lists.mysql.com/mysql?unsub=jschwartz@the-
>>infoshop.com
>
>
>
>



--
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: Particular value or NULL

>>Maybe:
>>
>>WHERE coalesce(x, 17) = 17
>>
>>
> [JS] Interesting suggestion, but
>
> us-gii >select benchmark(10000000,(7=7 or null is null));
> +-------------------------------------------+
> | benchmark(10000000,(7=7 or null is null)) |
> +-------------------------------------------+
> | 0 |
> +-------------------------------------------+
> 1 row in set (0.34 sec)
>
> us-gii >select benchmark(100000000,coalesce(null,7));
> +---------------------------------------+
> | benchmark(100000000,coalesce(null,7)) |
> +---------------------------------------+
> | 0 |
> +---------------------------------------+
> 1 row in set (2.61 sec)
>
> It looks like COALESCE() is slower. Of course this isn't anything like a
> real
> test. Among other things, I have no idea how well or poorly the optimizer,
> query cache, etc. handle it. I don't know how much magic there is in the
> BENCHMARK() function, either. I would hope that the server would know that
> the
> expression needs to be evaluated over and over again from scratch.

WHERE COALESCE(, ... ) = ...

cannot use an index, I guess, which is why the comparison is slow.

If NULLs are in indices, IS NULL could be index optimized as well.

With regards,

Martijn Tonies
Upscene Productions
http://www.upscene.com

Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
Anywhere, MySQL, InterBase, NexusDB and Firebird!

Database questions? Check the forum:
http://www.databasedevelopmentforum.com


--
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: Particular value or NULL

My query isn't slow enough to tell the difference, I'm only working with a few
hundred records.

This was intellectual curiosity.

Regards,

Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341

www.the-infoshop.com


>-----Original Message-----
>From: João Cândido de Souza Neto [mailto:joao@consultorweb.cnt.br]
>Sent: Wednesday, March 10, 2010 2:10 PM
>To: mysql@lists.mysql.com
>Subject: Re: Particular value or NULL
>
>Did you gave a try on using coalesce in your query?
>
>""Jerry Schwartz"" escreveu na mensagem
>news:02a201cac083$e07a2330$a16e6990$@com...
>> >-----Original Message-----
>>>From: João Cândido de Souza Neto [mailto:joao@consultorweb.cnt.br]
>>>Sent: Wednesday, March 10, 2010 10:55 AM
>>>To: mysql@lists.mysql.com
>>>Subject: Re: Particular value or NULL
>>>
>>>Maybe:
>>>
>>>WHERE coalesce(x, 17) = 17
>>>
>>>
>> [JS] Interesting suggestion, but
>>
>> us-gii >select benchmark(10000000,(7=7 or null is null));
>> +-------------------------------------------+
>> | benchmark(10000000,(7=7 or null is null)) |
>> +-------------------------------------------+
>> | 0 |
>> +-------------------------------------------+
>> 1 row in set (0.34 sec)
>>
>> us-gii >select benchmark(100000000,coalesce(null,7));
>> +---------------------------------------+
>> | benchmark(100000000,coalesce(null,7)) |
>> +---------------------------------------+
>> | 0 |
>> +---------------------------------------+
>> 1 row in set (2.61 sec)
>>
>> It looks like COALESCE() is slower. Of course this isn't anything like a
>> real
>> test. Among other things, I have no idea how well or poorly the optimizer,
>> query cache, etc. handle it. I don't know how much magic there is in the
>> BENCHMARK() function, either. I would hope that the server would know that
>> the
>> expression needs to be evaluated over and over again from scratch.
>>
>> Regards,
>>
>> Jerry Schwartz
>> The Infoshop by Global Information Incorporated
>> 195 Farmington Ave.
>> Farmington, CT 06032
>>
>> 860.674.8796 / FAX: 860.674.8341
>>
>> www.the-infoshop.com
>>
>>
>>
>>
>>>""Jerry Schwartz"" escreveu na mensagem
>>>news:023301cac069$366afa00$a340ee00$@com...
>>>> Is there a better construct for the WHERE clause in a LEFT JOIN than
>>>>
>>>>
>>>>
>>>> WHERE (x = 17 OR x IS NULL)
>>>>
>>>>
>>>>
>>>> ?
>>>>
>>>>
>>>>
>>>> Regards,
>>>>
>>>>
>>>>
>>>> Jerry Schwartz
>>>>
>>>> The Infoshop by Global Information Incorporated
>>>>
>>>> 195 Farmington Ave.
>>>>
>>>> Farmington, CT 06032
>>>>
>>>>
>>>>
>>>> 860.674.8796 / FAX: 860.674.8341
>>>>
>>>>
>>>>
>>>> www.the-infoshop.com
>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>>
>>>--
>>>MySQL General Mailing List
>>>For list archives: http://lists.mysql.com/mysql
>>>To unsubscribe: http://lists.mysql.com/mysql?unsub=jschwartz@the-
>>>infoshop.com
>>
>>
>>
>>
>
>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe: http://lists.mysql.com/mysql?unsub=jschwartz@the-
>infoshop.com





--
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: Particular value or NULL

Hi Jerry, all!


Jerry Schwartz wrote:
>> [[...]]
>>
>> Maybe:
>>
>> WHERE coalesce(x, 17) =3D 17
>>
>>
> [JS] Interesting suggestion, but
>=20
> us-gii >select benchmark(10000000,(7=3D7 or null is null));
> [[...]]
> 1 row in set (0.34 sec)
>=20
> us-gii >select benchmark(100000000,coalesce(null,7));
> [[...]]
> 1 row in set (2.61 sec)
>=20
> It looks like COALESCE() is slower. Of course this isn't anything l=
ike a real=20
> test. [[...]]

Both your statements use constants only, so they can be evaluated dur=
ing
statement analysis and need not access any data.

I don't think the time relation you get here can be applied to a
statement really getting column values from a table and using them in=
a
predicate.


Regards,
Jörg

--=20
Joerg Bruehe, MySQL Build Team, Joerg.Bruehe@Sun.COM
Sun Microsystems GmbH, Komturstraße 18a, D-12099 Berlin
Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels
Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB1610=
28


--
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