how does NOT work?

On 24 Apr 2002, tony wrote:

Stange one

select count(*) from individu

count
18417

select count(*) from individu where type2 like 'a%'

count
12619

select count(*) from individu where type2 not like 'a%'

count
81

Clues?

NULLs?

:)

--
Nigel J. Andrews
Director

---
Logictree Systems Limited
Computer Consultants

Tony,
you forgot the NULL entries.

Add the result of:
select count(*) from individu where type2 is null

JLL

tony wrote:

On Wed, 2002-04-24 at 17:22, Nigel J. Andrews wrote:

select count(*) from individu

count
18417

select count(*) from individu where type2 like 'a%'

count
12619

select count(*) from individu where type2 not like 'a%'

count
81

Clues?

NULLs?

the cells are either empty or contain a word that doesn't start with "a"
or a -

If I do it with two letters it works just fine so I guess I'll just put
another "a" in front

Cheers

Tony Grant

--
RedHat Linux on Sony Vaio C1XD/S
http://www.animaproductions.com/linux2.html
Macromedia UltraDev with PostgreSQL
http://www.animaproductions.com/ultra.html

I guess select count(*) from individu where type2 is null will return 5717 (18417-12619-81)
Arnaud

----- Original Message -----
From: "tony" <tony@animaproductions.com>
To: "postgres list" <pgsql-general@postgresql.org>
Sent: Wednesday, April 24, 2002 5:19 PM
Subject: [GENERAL] how does NOT work?

Stange one

select count(*) from individu

count
18417

select count(*) from individu where type2 like 'a%'

count
12619

select count(*) from individu where type2 not like 'a%'

count
81

Clues?

Cheers

Tony Grant

--
RedHat Linux on Sony Vaio C1XD/S
http://www.animaproductions.com/linux2.html
Macromedia UltraDev with PostgreSQL
http://www.animaproductions.com/ultra.html

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

On Wed, 2002-04-24 at 17:27, Jean-Luc Lachance wrote:

Tony,
you forgot the NULL entries.

Add the result of:
select count(*) from individu where type2 is null

The null and ones that don't start with 'a' are the ones I want. I just
wanted to say "list all the individuals who don't have a type2 that
starts with a".

It works if I use more than one character (all the "au%" or all the
"ar%"). Nowhere do I read that there is a 2 character limitation on
wildcard selects.

Cheers

Tony Grant

--
RedHat Linux on Sony Vaio C1XD/S
http://www.animaproductions.com/linux2.html
Macromedia UltraDev with PostgreSQL
http://www.animaproductions.com/ultra.html

On Wed, 2002-04-24 at 17:31, ARP wrote:

I guess select count(*) from individu where type2 is null will return 5717 (18417-12619-81)

OK so I trashed the null values

Still don't understand the logic - I just want cells that don't start
with "a" I don't care if they contain null values or not.

But I will be rewriting everything so that there is a default value in
each and every cell from now on.

Thanks

Cheers

Tony

--
RedHat Linux on Sony Vaio C1XD/S
http://www.animaproductions.com/linux2.html
Macromedia UltraDev with PostgreSQL
http://www.animaproductions.com/ultra.html

I guess select count(*) from individu where type2 is null will return 5717 (18417-12619-81)

Still don't understand the logic - I just want cells that don't start
with "a" I don't care if they contain null values or not.

Your answer is :

select count(*) from individu where type2 is null or type2 not like 'a%'

otherwise you trash the null values as you said :-)

Arnaud

I would discourage you from plugging in a default value if the true value is "unknown." I recently had to put the nulls back into a database where they had used 0 (zero) to represent "no evaluation" in a "score" column. Well, they tried averaging the values and got a low value. The zeroes figured into the average, where nulls would not.

Nulls are worth the trouble sometimes.

- Ian

tony <tony@animaproductions.com> 04/24/02 08:47AM >>>

On Wed, 2002-04-24 at 17:31, ARP wrote:

I guess select count(*) from individu where type2 is null will return 5717 (18417-12619-81)

OK so I trashed the null values

Still don't understand the logic - I just want cells that don't start
with "a" I don't care if they contain null values or not.

But I will be rewriting everything so that there is a default value in
each and every cell from now on.

Thanks

Cheers

Tony

--
RedHat Linux on Sony Vaio C1XD/S
http://www.animaproductions.com/linux2.html
Macromedia UltraDev with PostgreSQL
http://www.animaproductions.com/ultra.html

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

On Wed, 2002-04-24 at 17:55, ARP wrote:

I guess select count(*) from individu where type2 is null will return 5717 (18417-12619-81)

Still don't understand the logic - I just want cells that don't start
with "a" I don't care if they contain null values or not.

select count(*) from individu where type2 is null or type2 not like 'a%'

"or" works here but not in some of the more complex joins I am doing
elsewhere. I will be using "-" and "0" a lot more from now on!

Thanks again

Tony

--
RedHat Linux on Sony Vaio C1XD/S
http://www.animaproductions.com/linux2.html
Macromedia UltraDev with PostgreSQL
http://www.animaproductions.com/ultra.html

Still don't understand the logic - I just want cells that don't start
with "a" I don't care if they contain null values or not.

But I will be rewriting everything so that there is a default value in
each and every cell from now on.

The logic is this: in SQL, NULL is __NOT__ the same thing as 'empty'. It
means 'unknown'. And when you ask 'how many names start with A', you won't
get the names that are NULL (read: unknown). When you ask 'How many names DO
NOT start with A', you __still__ won't get the names that are NULL (read:
unknown), since, as they're unknown, it's impossible to say if they start
with A or not. It might seem pedantic, but very straightforward and
logically correct.

You can say either

WHERE column NOT LIKE 'a%' or column IS NULL

or

WHERE ( column LIKE 'a%' ) IS NOT TRUE;

The first is more clear to most people as it makes the NULL exception
explicit and obvious.

... or see my answer a second ago about (c LIKE 'a%') IS NOT TRUE which
will also work.

Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant

On 24 Apr 2002, tony wrote:

On Wed, 2002-04-24 at 17:55, ARP wrote:

I guess select count(*) from individu where type2 is null will return 5717 (18417-12619-81)

Still don't understand the logic - I just want cells that don't start
with "a" I don't care if they contain null values or not.

select count(*) from individu where type2 is null or type2 not like 'a%'

"or" works here but not in some of the more complex joins I am doing
elsewhere. I will be using "-" and "0" a lot more from now on!

I don't understand what you're saying now can we just restate the situation.

You have:

Query Giving
-------- ----------
SELECT count(*) from individu T
SELECT count(*) from individu WHERE type2 like 'a%' x
SELECT count(*) from individu WHERE type2 not like 'a%' y

where x + y != T

and, here is where I get uncertain:

SELECT count(*) from individu WHERE type2 like 'ar%' v
SELECT count(*) from individu WHERE type2 not like 'ar%' w

where v + w == T

But in another message you imply that you did have nulls present, so was your
'two letter' test incorrectly stated or was it that you weren't aware that
NULLs existed? (Only asking because although I can't see it happening if there
weren't any NULLs there must be a bug)

--
Nigel J. Andrews
Director

---
Logictree Systems Limited
Computer Consultants