>-----Original Message----- >From: João Cândido de Souza Neto [mailto:j...@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"" <jschwa...@the-infoshop.com> escreveu na mensagem >news:023301cac069$366afa00$a340ee...@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 >> >> >> >> <http://www.the-infoshop.com> www.the-infoshop.com >> >> >> >> > > > >-- >MySQL General Mailing List >For list archives: http://lists.mysql.com/mysql >To unsubscribe: http://lists.mysql.com/mysql?unsub=jschwa...@the- >infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
