SELECT on a nullable reference

Question

Hello,

I have a relationship between two tables, authors and styles. Every author is associated with a style, with the special case where an author doesn't have a style (IS NULL).

There's no problem in setting the reference to NULL, but there's a problem doing a query to select the authors and styles.

For example, the query:

SELECT "authors"."id", "authors"."name", "styles"."name", "authors"."comments" FROM 
"authors" , "styles" WHERE "authors"."style" = "styles"."id"

just ignores the authors that have a NULL style (as expected).

I need to do a select that also lists authors with NULL style, like a left join would do (I can't use LEFT JOIN fo some reasons).

There's a solution that doesn't include explicit joins?

Answer 1

The most obvious solution is a LEFT OUTER JOIN.

See: http://www.postgresql.org/docs/8.1/static/tutorial-join.html

If you don't want to use explicit joins you should be able to use a UNION

SELECT "authors"."id", "authors"."name", "styles"."name", "authors"."comments" FROM 
"authors" , "styles" WHERE "authors"."style" = "styles"."id"
UNION
SELECT "authors"."id", "authors"."name", "", "authors"."comments" FROM 
"authors" WHERE "authors"."style" IS NULL

Answer 2

SELECT "authors"."id", "authors"."name", "styles"."name", "authors"."comments" FROM    "authors" , "styles" WHERE "authors"."style" = "styles"."id" OR "authors"."style" = null

Have you tried that?

Answer 3

From my understanding, you just need to expand your query to include NULL:

SELECT "authors"."id", "authors"."name", "styles"."name", "authors"."comments" 
FROM "authors" , "styles" 
WHERE "authors"."style" = "styles"."id" OR "authors"."style" IS NULL

Answer 4

I think that if you can't use a LEFT JOIN you should use a UNION.
Review the link from Coding Horror, it's pretty interesting. A Visual Explanation of SQL Joins