Right join without intersection and another condition

Question

I want to do a right join like this:

I want all B without any A OR All B with just A.type <> 0 (if they have a single A.type = 0 i don't want them)

For now I have this :

SELECT B.* 
FROM A 
RIGHT JOIN B ON A.ticket = B.id 
WHERE A.id IS NULL
  AND B.state NOT IN (3,4,10)
  AND B.disable = 0

Answer 1

There is no reason to use RIGHT JOIN. For most people, it is simply more confusing than LEFT JOIN for two reasons. First, the FROM clause is read "left to right". A LEFT JOIN keeps all rows in the first table in the clause. The RIGHT JOIN in the not-yet-seen last table. Second, the FROM clause is parsed left-to-right, which introduces some subtle issues when combining multiple outer joins.

Next, you explicitly say:

i want to exclude B who doesn't have a A.Type = 0

This is equivalent to:

i want to include B who have no A.Type = 0

This is not quite an outer join. I think this is closer to what you want:

In your case:

SELECT  B.*
FROM B
WHERE NOT EXISTS (SELECT 1
                  FROM A
                  WHERE A.ticket = B.id AND A.Type = 0
                 ) AND
      B.state NOT IN (3, 4, 10) AND B.disable = 0;