I hate NULL

[ivarbug]

I have 2 tables: table1 for some stuff and table2 for some other stuff. Each row in table2 references 0-1 rows in table1. I'm using foreign keys.

table1: id, stuff
table2: id, table1_id, stuff

I select records from table2 by table1_id.

The problem:
How do I select records from table2 that doesn't reference any rows in table1(table1_id = NULL)?

I started using prepared statements and I'm not going to write 2 different queries for this anymore(WHERE table1_id = ? and WHERE table1_id IS NULL).

COALESCE(table1_id, 0) = ? looks stupid.

I can't set table2.table1_id to 0 because I'm using foreign keys.

I won't insert a special row with id 0 into table1.

Should I really make a 3'rd table for this?

table2_table1: table1_id(unique), table2_id

NULL is so useless.

[brazenskies]

sory, Just on my way out the door but a right outer join should do the trick here!

[Fumigator]

An OUTER JOIN will do the trick, but you'll also have to include in your JOIN ON clause OR table1_id IS NULL.


SELECT table1.stuff, table2.stuff
FROM table1
RIGHT JOIN table2
ON table1.id = table2.table1_id
OR table2.table1_id IS NULL


Otherwise you'll pick up all table2 rows that don't match a table1_id row (whether or not table1_id is null).

Alternatively you can UNION two queries together, which will put the results in the same columns thereby perhaps making it easier to work with the resultset.


SELECT stuff
FROM table1
JOIN table2
ON table1.id = table2.table1_id
UNION
SELECT stuff
FROM table2
WHERE table1_id IS NULL