SP: handling nulls

Question

Hi!

I have this Table structure:

Id int not null --PK
Title varchar(50)
ParentId int null --FK to same Table.Id

I'm writing a SP that returns a row's "brothers", here's the code

select * from Table
where Table.ParentId = (select Table.ParentId from Table where Table.id = @Id)
and Table.Id <> @Id

It works perfectly for rows having a parent, but for those who's parent are null (root records), it returns no row. This is working as expected since null = null is always false.

I'm looking for help on how to better design my SP to handle this specific case. I'm not a DBA and my TSQL knowledge is basic.

EDIT: I've updated my SQL query like this:

DECLARE @Id INT
SET @Id = 1

DECLARE @ParentId INT
SET @ParentId = (SELECT Table.ParentId FROM Table WHERE Table.Id = @Id)

SELECT * FROM Table
WHERE (
    (@ParentId IS NULL AND (Table.ParentId IS NULL))
    OR (Table.ParentId = @ParentId)
)

AND Table.Id <> @Id

It does do the job but if the Id is not in the table, it still returns the row who have no parents. Going to lunch, continue looking at this later.

Thanks in advance, Fabian

Answer 1

I'm not sure this is the best solution, but you could try to use the COALESCE operator using a "not valid" id for NULL

select * from Table
where COALESCE(Table.ParentId,-1) = (select COALESCE(Table.ParentId,-1) from Table where Table.id = @Id)
and Table.Id <> @Id

Assuming -1 is never used as an ID

Answer 2

It's possible I have not understood your problem description however, in order to return Brothers only when they exist for a given Parent, the following query should suffice:

  select Brother.* 
    from Table Parent
        inner join Table Brother on
          Parent.id = Brother.ParentID
    where Parent.Id= @Id and Brother.Id <> @Id

Answer 3

To make the best use of any indexes on ParentId and avoid any problems with plan caching I can't at the moment think of a way better than simply splitting the cases up.

DECLARE @ParentId INT

SELECT @ParentId = ParentId FROM TABLE WHERE id = @Id

IF (@ParentId IS NULL)
    SELECT * 
    FROM TABLE
    WHERE ParentId IS NULL
    AND Table.Id <> @Id
ELSE
    SELECT * 
    FROM TABLE
    WHERE ParentId = @ParentId
    AND Table.Id <> @Id