CODEWITHSUNDEEP
sqlpostgresqlselectjoinnull
Jesper Tuborg Madsen
Jesper Tuborg Madsen

Reputation: 91

Ignore null values in select statement

I'm trying to retrieve a list of components via my computer_system, BUT if a computer system's graphics card is set to null (I.e. It has an onboard), the row isn't returned by my select statement.

I've been trying to use COALESCE without results. I've also tried with and OR in my WHERE clause, which then just returns my computer system with all different kinds of graphic cards.

Relevant code:

SELECT
computer_system.cs_id, 
computer_system.cs_name, 
motherboard.name, 
motherboard.price, 
cpu.name, 
cpu.price, 
gfx.name,
gfx.price
FROM 
public.computer_case , 
public.computer_system, 
public.cpu, 
public.gfx, 
public.motherboard, 
public.ram
WHERE 
computer_system.cs_ram = ram.ram_id AND
computer_system.cs_cpu = cpu.cpu_id AND
computer_system.cs_mb = motherboard.mb_id AND
computer_system.cs_case = computer_case.case_id AND 
computer_system.cs_gfx = gfx.gfx_id; <-- ( OR computer_system.cs_gfx IS NULL)

Returns:

1;"Computer1";"Fractal Design"; 721.00; "MSI Z87"; 982.00; "Core i7 I7-4770K "; 2147.00; "Crucial Gamer"; 1253.00; "ASUS GTX780";3328.00

Should I use Joins? Is there no easy way to say return the requested row, even if there's a bloody NULL value. Been struggling with this for at least 2 hours.

Tables will be posted if needed.

EDIT: It should return a second row:

2;"Computer2";"Fractal Design"; 721.00; "MSI Z87"; 982.00; "Core i7 I7-4770K "; 2147.00; "Crucial Gamer"; 1253.00; "null/nothing";null/nothing

Upvotes: 0

Views: 5733

Answers (2)

Craig Ringer
Craig Ringer

Reputation: 325031

You want a LEFT OUTER JOIN.

First, clean up your code so you use ANSI joins so it's readable:

SELECT
    computer_system.cs_id, 
    computer_system.cs_name, 
    motherboard.name, 
    motherboard.price, 
    cpu.name, 
    cpu.price, 
    gfx.name,
    gfx.price
FROM 
    public.computer_system
    INNER JOIN public.computer_case ON computer_system.cs_case = computer_case.case_id
    INNER JOIN public.cpu ON computer_system.cs_cpu = cpu.cpu_id
    INNER JOIN public.gfx ON computer_system.cs_gfx = gfx.gfx_id
    INNER JOIN public.motherboard ON computer_system.cs_mb = motherboard.mb_id
    INNER JOIN public.ram ON computer_system.cs_ram = ram.ram_id;

Then change the INNER JOIN on public.gfx to a LEFT OUTER JOIN:

    LEFT OUTER JOIN public.gfx ON computer_system.cs_gfx = gfx.gfx_id

See PostgreSQL tutorial - joins.

I very strongly recommend reading an introductory tutorial to SQL - at least the PostgreSQL tutorial, preferably some more material as well.

Upvotes: 2

djmorton
djmorton

Reputation: 616

It looks like it's just a bracket placement issue. Pull the null check and the graphics card id comparison into a clause by itself.

...
computer_system.cs_case = computer_case.case_id AND 
(computer_system.cs_gfx IS NULL OR computer_system.cs_gfx = gfx.gfx_id)

Additionally, you ask if you should use joins. You are in fact using joins, by virtue of having multiple tables in your FROM clause and specifying the join criteria in the WHERE clause. Changing this to use the JOIN ON syntax might be a little easier to read:

FROM sometable A
    JOIN someothertable B
        ON A.somefield = B.somefield
    JOIN somethirdtable C
        ON A.somefield = C.somefield
    etc

Edit:

You also likely want to make the join where you expect the null value to be a left outer join:

SELECT * FROM
    first_table a
    LEFT OUTER JOIN second_table b
        ON a.someValue = b.someValue

If there is no match in the join, the row from the left side will still be returned.

Upvotes: 1

Related Questions