SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Wizard
    Join Date
    May 2002
    Posts
    1,370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Embarassing Question : checking for NULL

    There a table column I need to math results when NULL exists:

    SELECT *
    FROM Mem LEFT JOIN Run on Run.fk_MID=Mem.MID
    WHERE Run.PAID='NULL'

    But how does this really go? And yes, this is embarassing to ask.
    Records having NULL for this column will not appear, for example, when they exist and when checking on

    Run.PAID<> 2
    Run.PAID!=2

    As another example:

    SELECT *
    FROM Mem, Run
    WHERE fk_MID=MID AND PAID=2 AND MID!=366

    Does return to right results, whereas this does not (returns no results):

    SELECT *
    FROM Mem, Run
    WHERE fk_MID =MID AND PAID!=2 AND MID!=366

    What could be happening?
    Last edited by datadriven; Jan 19, 2006 at 01:25.

  2. #2
    SitePoint Addict
    Join Date
    May 2003
    Location
    Auckland
    Posts
    309
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You're using some operators incorrectly.

    When checking for null values you need to use the 'IS' operator. E.g:
    Code:
    WHERE SomeField IS NULL
    WHERE SomeField IS NOT NULL
    The not equals operator is actually <>. E.g.
    Code:
    WHERE SomeField <> SomeOtherField
    Your queries should be:
    Code:
    SELECT
    	*
    FROM
    	Mem
    LEFT JOIN
    	Run ON Run.fk_MID = Mem.MID
    WHERE
    	Run.PAID IS NULL
    Code:
    SELECT
    	*
    FROM
    	Mem
    LEFT JOIN
    	Run ON Run.fk_MID = Mem.MID
    WHERE
    	Run.PAID <> 2 AND Mem.MID <> 366

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    imminent, nice reply, absolutely correct about the NULLs

    just one minor detail about your sample queries -- in a LEFT OUTER JOIN, if you have a WHERE condition on a column of the right table, this effectively changes the LEFT OUTER JOIN into an INNER join because it can no longer return unmatched rows from the left table

    your last example should say:
    Code:
    select *
      from Mem
    left outer
      join Run 
        on Run.fk_MID = Mem.MID
       and Run.PAID <> 2 
     where Mem.MID <> 366
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Wizard
    Join Date
    May 2002
    Posts
    1,370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank You Imminent, Rudy,

    Works Great.

    Rudy- Then, that Left Outer Join I see too could be a way of verifying that values are being correctly inserted into both desired tables (visual check)

    Only other comment I have is that operator != seems to work as <>does for values other than NULL. (But my oberservation sounds inherently risky for some reason)

  5. #5
    SitePoint Addict
    Join Date
    May 2003
    Location
    Auckland
    Posts
    309
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    imminent, nice reply, absolutely correct about the NULLs

    just one minor detail about your sample queries
    Ah yes, good point. Probably should've spent a bit more time trying to understand the intention of the query


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •