Same table inner-join a table on 2 conditions and either conditions are sometimes false

Details:

1. V and C are tables

2. I want C to join V on 2 conditions(two C are named C0 and C1). Since I must filter V by columns of C and C‘s child tables separately in the same query, I think joining is needed.

3. V has many, at least one C, and either Cx sometimes doesn’t exist.

Because of 3., using inner join to C0 and C1 sometimes cause empty result, but I don’t want get V‘s white area.

Schema:

V (id)
C (id, v_id, name, type)

Query(1):

SELECT V.* 
FROM V
INNER JOIN C AS C0 ON V.id = C0.v_id WHERE C0.type = 0
INNER JOIN C AS C1 ON V.id = C1.v_id WHERE C1.type = 1
WHERE C0.name = 'word' or C1.name = 'word'
WHERE C0.name like '%wo%'

Query (2):

SELECT V.* 
FROM V
INNER JOIN C AS C0 ON V.id = C0.v_id WHERE C0.type = 0
INNER JOIN C AS C1 ON V.id = C1.v_id WHERE C1.type = 1
WHERE C0.name = 'word' or C1.name = 'word'
WHERE C1.name like '%wo%'

WHERE C0.name = 'word' or C1.name = 'word': This clause exists every time.

WHERE Cx.name like '%wo%': This clause depends on the time.

Answer

Well… You seem to be looking for an EXISTS condition with correlated subquery:

SELECT * FROM v 
WHERE EXISTS (SELECT 1 FROM c WHERE c.c0 = v.c0 OR c.c1 = v.c1)

This will return all records in table v for which at least one record exists in table c that satisfies either condition c.c0 = v.c0 or condition c.c1 = v.c1.

In this situation, the advantage of the correlated subquery against the JOIN approach is that it avoids multiplying records when more than one match exists in table c.

If you want to exclude records that meet either condition instead of including them, then just change WHERE EXISTS to WHERE NOT EXISTS.