SQL multiple joins while dealing with null values-CodePudding

I have a question regarding SQL syntax and how my query should be done. I have atm conquered my problem using two querys and some PHP. However, let me present my DB and problem.

Table structure

With the unique ID of the print table i want to be able to get:

dex number from prints
image (URL) from prints
rarity from rarities
type from types x3 when not null else i still want to execute
name from pokemon x5 when not null else i still want to execute

The query is quite simple when it comes to collecting all the values

SELECT pr.dex, 
       pr.image, 
       p.name, 
       r.rarity, 
       t.type, 
       t2.type, 
       t3.type,
       p1.name,
       p2.name,
       p3.name,
       p4.name
FROM prints pr
       JOIN pokemons p ON p.dex=pr.dex
       JOIN rarities r ON r.id=pr.rarity
       JOIN types t ON t.id=p.type
       JOIN types t2 ON t2.id=pr.weakness
       JOIN types t3 ON t3.id=pr.resistance
       JOIN pokemons p1 ON p1.dex=p.evolveto
       JOIN pokemons p2 ON p2.dex=p.evolveto2
       JOIN pokemons p3 ON p3.dex=p.evolveto3
       JOIN pokemons p4 ON p4.dex=p.evolvefrom
WHERE pr.id=2;

But for some pr.id(s) the values for pr.weakness, pr.resistance, p.evolveto, p.evolveto2, p.evolveto3 or p.evolvefrom can and should be null. When any of them are null the query comes back empty. So basically what i want is to exclude values which are null. I've tried to wrap in some selects with COALESCE() and played around a bit with CASE but without success. It would be awesome if it can be done with only one qeury regardless of what value pr.id has.

CodePudding user response：

Your INNER JOINs feature ON clauses that do equijoin on a possibly NULL field. Yet you want to get a row back anyway, showing that the given field was empty.

The tool you're looking for is LEFT OUTER JOIN, which will do exactly that. https://en.wikipedia.org/wiki/Join_(SQL)#Left_outer_join

For example

SELECT cat.name, cat.price, inv.qty, inv.date
FROM catalog cat
LEFT OUTER JOIN inventory inv  ON cat.id = inv.cat_id

will reveal catalog items even if they've not been inventoried.

An expression like COALESCE(inv.qty, 0) can be handy for suppressing NULLs in the result rows, if desired.