Oracle SQL - Find where two unique columns in separate tables connect in a third table

Question

I have two tables: t1 with column “b” and t2 with column “c”

A third table exists, t3, that has two columns, “b” and “c,” that are of similar types to those of t1 and t2. Some of the data matches between t1, t2, and t3, but not all of it.

In Oracle SQL, what is the best way to find the data in t1.b that correlates with t2.c using t3.b and t3.c, such that where t1.b = t3.b and t2.c = t3.c, a pair is created: (t1.b, t2.c)?

I am new to both stackoverflow and SQL, apologies if this question is poorly worded! Thanks in advance.

Answer 1

select t1.b, t2.c
from   t1 cross join t2
where  (t1.b, t2.c) in (select b, c from t3)
;

"cross join" is the Cartesian product - all possible pairs of t1.b and t2.c. Other than that, the query should be self-explanatory.

The only remaining interesting question is how to handle NULL. Can t1.b be NULL (for example)? If so, do you consider the pair of t1.b as NULL and t2.c as 103 to be the same as the pair (NULL, 103) found in table t3? The standard SQL answer is that they shouldn't be considered equal; NULL never equals anything else, not even (another) NULL. My answer does exactly that - it is consistent with this standard. But the proper handling depends on your business problem.