Right join without intersection and another condition

Question

I want to make the correct connection as follows:

I want all B to be without A OR All B to just A.type <> 0 (if they have one A.type = 0, they don't want them)

At the moment 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

RIGHT JOIN. , LEFT JOIN . -, FROM " ". A LEFT JOIN . RIGHT JOIN . -, FROM , .

:

B, A.Type = 0

:

B, A.Type = 0

. , , :

:

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;

Answer 2

B, , - A, B, < > 0, :

SELECT * FROM B
WHERE B.id NOT IN (
   SELECT ticket  FORM A
   WHERE A.type = 0 )

---

, - :

SELECT  B.* FROM A 
RIGHT JOIN B
ON A.ticket = B.id 
WHERE
     A.type<>0
 -- (A.id is null OR A.type<>0)
 -- A.id IS NULL
 -- AND A.type <> 0 -- added for exclude a.type = 0
 AND B.state NOT IN (3,4,10)
 AND B.disable = 0

JOIN ing, A.type 0 ,

Answer 3

:

select  B.id
from    A right join B on A.ticket = B.id
where   B.state not in (3, 4, 10) and B.disable = 0
group by B.id
having count(case when A.ticket is not null and A.type = 0 then 1 end) = 0;

, B, A, , A.type = 0.

---

:

select  B.*
from    A right join B on A.ticket = B.id
where   B.state not in (3, 4, 10) and B.disable = 0 and coalesce(A.type, 1) <> 0

, , , , A.type = 0.

, coalesce - .

Answer 4

Use subqueries to achieve the same query and save the CORRECT JOIN. This method can also be faster as it tries to connect the minimum number of records from tables on two sides of the JOIN.

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