CODEWITHSUNDEEP
postgresqlselectforeign-keys
Kknd
Kknd

Reputation: 3217

SELECT on a nullable reference

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?

Upvotes: 0

Views: 201

Answers (4)

Eduardo Crimi
Eduardo Crimi

Reputation: 1601

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

Upvotes: 1

DanSingerman
DanSingerman

Reputation: 36502

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

Upvotes: 4

seanhodges
seanhodges

Reputation: 17524

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

Upvotes: 0

George Stocker
George Stocker

Reputation: 57877

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?

Upvotes: 0

Related Questions