Thread: wired behaviour
Hello, I have an problem understanding a simple query: For example I have the following: select count(*) from foo; gives me 1000 select count(*) from foo where bar = 'Mr Spock'; gives me 5 select count(*) from foo where NOT bar = 'Mr Spock'; gives me 857 Why I'm not getting 955 for the last query ? Any idea ? Kindly regards Lutz
NULL values? Met vriendelijke groet, Paul Dam Informatieanalist Amyyon Bijsterhuizen 11.58 6546 AS Nijmegen 050 - 311 5686 www.amyyon.nl -----Oorspronkelijk bericht----- Van: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] Namens Lutz Steinborn Verzonden: vrijdag 28 november 2008 14:52 Aan: pgsql-sql@postgresql.org Onderwerp: [SQL] wired behaviour Hello, I have an problem understanding a simple query: For example I have the following: select count(*) from foo; gives me 1000 select count(*) from foo where bar = 'Mr Spock'; gives me 5 select count(*) from foo where NOT bar = 'Mr Spock'; gives me 857 Why I'm not getting 955 for the last query ? Any idea ? Kindly regards Lutz -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
On Nov 28, 2008, at 8:51 AM, Lutz Steinborn wrote: > Hello, > > I have an problem understanding a simple query: > > For example I have the following: > select count(*) from foo; > gives me 1000 > > select count(*) from foo where bar = 'Mr Spock'; > gives me 5 > > select count(*) from foo where NOT bar = 'Mr Spock'; > gives me 857 > > Why I'm not getting 955 for the last query ? > > > Any idea ? Do you happen to have any NULL values on the field bar?? Ries
Hello Paul, thanks for the quick answer. > NULL values? Jepp, thats it. I've supposed this but can't believe it. So NULL is something out of this dimension :-) Kindly regards Lutz > > Met vriendelijke groet, > > Paul Dam > Informatieanalist > > > > > Amyyon > Bijsterhuizen 11.58 > 6546 AS Nijmegen > 050 - 311 5686 > www.amyyon.nl > -----Oorspronkelijk bericht----- > Van: pgsql-sql-owner@postgresql.org > [mailto:pgsql-sql-owner@postgresql.org] Namens Lutz Steinborn > Verzonden: vrijdag 28 november 2008 14:52 > Aan: pgsql-sql@postgresql.org > Onderwerp: [SQL] wired behaviour > > Hello, > > I have an problem understanding a simple query: > > For example I have the following: > select count(*) from foo; > gives me 1000 > > select count(*) from foo where bar = 'Mr Spock'; > gives me 5 > > select count(*) from foo where NOT bar = 'Mr Spock'; > gives me 857 > > Why I'm not getting 955 for the last query ? > > > Any idea ? > > Kindly regards > > > Lutz > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql -- Lutz Steinborn 4c Business Service GmbH Putzbrunner Str. 71 81739 M_nchen Fon: +49 89 99341 30, Fax +49 89 99341 399 l.steinborn@4c-ag.de, http://www.4c-shopping.de --------------------------------------------------------- Sitz der Gesellschaft: Putzbrunner Str. 71, 81739 Muenchen Vertretungsberechtigter Geschaeftsfuehrer: Frank W. Lutze Registergericht: Amtsgericht Muenchen Registernummer: HR 130 207 Ustnr. gemaess _ 27 a Umsatzsteuergesetz: DE 206 864 106
On fös, 2008-11-28 at 15:22 +0100, Lutz Steinborn wrote: > Hello Paul, > > thanks for the quick answer. > > > NULL values? > Jepp, thats it. > I've supposed this but can't believe it. So NULL is something out of this > dimension :-) Yes, that is one way of putting it. A more useful way to look at it is to say that NULL represents an UNKNOWN value. rows with NULL bar value will not be counted by: > > select count(*) from foo where bar = 'Mr Spock'; because you cannot know whether the UNKNOWN value is 'Mr Spock'. these rows will neither be counted by: > > select count(*) from foo where NOT bar = 'Mr Spock'; because you also cannot know that the UNKNOWN value is NOT 'Mr Spock'. Does that make it any clearer? gnari
Lutz Steinborn wrote: > Hello Paul, > > thanks for the quick answer. > >> NULL values? > Jepp, thats it. > I've supposed this but can't believe it. So NULL is something out of this > dimension :-) > > Kindly regards > Lutz > NULL refers to an unknown value - it cannot be said to equal or not equal anything other than NULL It is a concept that catches those new to databases (and sometimes not so new). -- Shane Ambler pgSQL (at) Sheeky (dot) Biz Get Sheeky @ http://Sheeky.Biz
--- On Fri, 11/28/08, Lutz Steinborn <l.steinborn@4c-ag.de> wrote:
> From: Lutz Steinborn <l.steinborn@4c-ag.de>
> Subject: [SQL] wired behaviour
> To: pgsql-sql@postgresql.org
> Date: Friday, November 28, 2008, 1:51 PM
> Hello,
>
> I have an problem understanding a simple query:
>
> For example I have the following:
> select count(*) from foo;
> gives me 1000
>
> select count(*) from foo where bar = 'Mr Spock';
> gives me 5
>
> select count(*) from foo where NOT bar = 'Mr
> Spock';
> gives me 857
>
> Why I'm not getting 955 for the last query ?
>
>
> Any idea ?
>
> Kindly regards
>
>
> Lutz
>
> --
Probably you have null values in bar column
Shane Ambler wrote: > Lutz Steinborn wrote: >> Jepp, thats it. >> I've supposed this but can't believe it. So NULL is something out of >> this >> dimension :-) >> >> > > NULL refers to an unknown value - it cannot be said to equal or not > equal anything other than NULL ... > Not exactly. Null does not equal null. The "translation" being does some-unknown-value equal some-unknown-value? Answer: unknown. If you want to determine if something is null you must use "is null". select null = null; null select null is null; true If you want to treat nulls as a defined known value, use the coalesce function: select coalesce(my_column, 'a null value')....; will return the string 'a null value' whenever my_column is null. Cheers, Steve