SQL Join – If it doesn’t find any, try another parameters

Let’s suppose we have the following query:

SELECT *
FROM tableA a
LEFT JOIN tableB b ON b.number = a.number 
                  AND b.name = a.name 
                  AND b.place = a.place

If that LEFT JOIN doesn’t find nothing, I need to change the ON parameters to something like:

SELECT *
FROM tableA a
LEFT JOIN tableB b ON b.number = a.number 
                  AND b.person = a.person 

That second LEFT JOIN only needs to run if the first one doesn’t return nothing. How can I achieve that behaviour?

---

I have already tried with an OR statement, but doesn’t work because the first LEFT JOIN always need to be checked first, and not at the same time that the second.

Answer

Perhaps the simplest method is union all:

SELECT a.*, b.*
FROM tableA a JOIN
     tableB b
     ON b.number = a.number AND
        b.name = a.name AND
        b.place = a.place
UNION ALL
SELECT a.*, b.*
FROM tableA a JOIN
     tableB b
     ON b.number = a.number AND
        b.person = a.person 
WHERE NOT EXISTS (SELECT 1
                  FROM tableB b
                  WHERE b.number = a.number AND
                        b.name = a.name AND
                        b.place = a.place
                 );