WHERE col = NULL ?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
jurriemcflurrie
Forum Commoner
Posts: 61
Joined: Wed Jul 06, 2005 7:14 am
Location: Den Haag, the Netherlands

WHERE col = NULL ?

Post by jurriemcflurrie »

Weirdan | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


I have a small query problem. First, these are my tables:

[syntax="sql"]CREATE TABLE `profile_fields` (
`field` VARCHAR( 255 ) NOT NULL ,
`type` VARCHAR( 255 ) NOT NULL ,
`size` INT( 3 ) NOT NULL ,
`max` INT( 5 ) NOT NULL ,
UNIQUE (
`field`
)
) ENGINE = MYISAM ;

CREATE TABLE `profile_data` (
`id` BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`user` VARCHAR( 32 ) NOT NULL ,
`field` VARCHAR( 255 ) NOT NULL ,
`value` TEXT NOT NULL ,
`timestamp` INT( 10 ) NOT NULL ,
INDEX ( `user` , `field` )
) ENGINE = MYISAM ;

What I want to do is select all fields, and join the users data to it. But, when I do a WHERE on the user column, I only get results of fields that are already filled in, and that's not what I want. I want all fields, and if there's userdata, it should be there.

This is what I planned to do:

Code: Select all

SELECT *
FROM profile_fields
LEFT JOIN profile_data ON profile_fields.name = profile_data.field
WHERE profile_data.user = '$user'
With this query, I only get results where a user matches (so I'm missing some field data). I thought I'd do this:

Code: Select all

SELECT *
FROM profile_fields
LEFT JOIN profile_data ON profile_fields.name = profile_data.field
WHERE
    profile_data.user = '$user' OR
    profile_data.user = NULL
but that doesn't work neither.

Can anyone help me out?

Thanks in advance!


Weirdan | Please use[/syntax]

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
User avatar
x
Forum Newbie
Posts: 12
Joined: Wed Jun 08, 2005 11:31 pm

Post by x »

Weirdan | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


im not an expert on this but i know left join should connect even if theres null, unlike inner join. could you try reversing the order of the two tables in the join or use right join?

[syntax="sql"]
SELECT *
FROM profile_data
LEFT JOIN profile_fields ON profile_fields.name = profile_data.field
WHERE profile_data.user = '$user'
or

Code: Select all

SELECT *
FROM profile_fields
RIGHT JOIN profile_data ON profile_fields.name = profile_data.field
WHERE profile_data.user = '$user'

Weirdan | Please use[/syntax]

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
User avatar
jurriemcflurrie
Forum Commoner
Posts: 61
Joined: Wed Jul 06, 2005 7:14 am
Location: Den Haag, the Netherlands

Post by jurriemcflurrie »

No luck.

It seems that WHERE user = '$user' limits the result to exactly match the where clause. That's why I tried to do a AND user = NULL, but that won't work also..
paladaxar
Forum Commoner
Posts: 85
Joined: Fri Jun 18, 2004 11:50 pm

Post by paladaxar »

OK...nothing ever "equals" NULL someting either IS NULL or IS NOT NULL. Try "WHER col IS NULL"
User avatar
jurriemcflurrie
Forum Commoner
Posts: 61
Joined: Wed Jul 06, 2005 7:14 am
Location: Den Haag, the Netherlands

Post by jurriemcflurrie »

Ok, solved!!

paladaxar, your suggestion didn't work. I still got no NULL results, and I did get results from other users :)



The query should be as follows:

Code: Select all

SELECT *
FROM profile_fields
LEFT JOIN profile_data ON 
	profile_fields.name = profile_data.field AND
	profile_data.user = '$user'

Thanks anyway, maybe we saved someone else a lot of time ;)
paladaxar
Forum Commoner
Posts: 85
Joined: Fri Jun 18, 2004 11:50 pm

Post by paladaxar »

Hey, I'm glad you got it.

Do remember though, never to say "equals NULL".

That trips up a lot of people.
User avatar
jurriemcflurrie
Forum Commoner
Posts: 61
Joined: Wed Jul 06, 2005 7:14 am
Location: Den Haag, the Netherlands

Post by jurriemcflurrie »

And why is that exactly?

I can equal false, '' and 0

so why not NULL
paladaxar
Forum Commoner
Posts: 85
Joined: Fri Jun 18, 2004 11:50 pm

Post by paladaxar »

NULL is an interesting little guy. It really means "non existent". Nothing can equal non existent. Something can exist, or it IS non existent.

You should only use NULL for something that doesnt yet exist. Never in place of a known zero value. Like the number of legs on an apple is 0. It is not NULL because we know that an apple has no legs. (I know...it was a stupid example...but it works)

If we have a package that is scheduled to ship sometime next week, the 'shipped_out_date' columb will be NULL. The package may have an anticipated ship date, but since it has not yet actually shipped, it's actual ship date is NULL. Also, we wouldnt want to use 0 here because that could confuse our server. If we are looking for all packages shipped before June 1, 2006, we would get this package in our results.

Doing a quick google search, I came up with this page:

http://www.sqlservercentral.com/columni ... ersion.asp

about half way down the page it directs you here:

http://www.sqlservercentral.com/columni ... isnull.asp

I didnt read the article since it wanted me to create an account. But, if you already have an account, it looks like an article that will explain exactly what we are talking about.

Try a few google searches on it. It is something that has tripped up SQL coders for years (experienced and unexperienced). Once you understand it, it shouldnt give you any more problems. It's getting the initial understanding of the subject that can be tough. It is, indeed, unintuitive at first.
paladaxar
Forum Commoner
Posts: 85
Joined: Fri Jun 18, 2004 11:50 pm

Post by paladaxar »

Just found another good site on this topic:

http://www.xaprb.com/blog/2006/05/18/wh ... ng-in-sql/

Hope that helps.
User avatar
jurriemcflurrie
Forum Commoner
Posts: 61
Joined: Wed Jul 06, 2005 7:14 am
Location: Den Haag, the Netherlands

Post by jurriemcflurrie »

Ok thanks, I'll have a read.

But I think I get it already!
Post Reply