How to search for information in two different tables and return the ID of the table where the information is?

Question

I have a code that could be of a natural person OR legal, and I have a table for each one (one for PF and another for PJ).

I would like to find this user in one of the two tables and return your information.

Bounce that he (user) will only be present in one of the two...

I’m doing it this way at the moment:

select *
user.ID     as UserID
,conta.Email    as Email
,pf.Nome    as NomeUser
,pf.ID      as IDUser
from Users user
inner join PFContas pfconta on pfconta.PFID = user.PFID
inner join PF pf on pf.ID = pfconta.PFID
inner join Contas conta on conta.Id = pfcc.ContaID
where
conta.ID = '579'

But this only applies to PF, as it would cause the same query to search in the table of PJ, if it did not find in this?

Answer 1

Utilize LEFT JOIN instead of INNER JOIN in the desired tables, so it will always contain one or the other table.

Take a better look here

Answer 2

You are a little confused. What comes to be pfcc? It can exist as PF and PJ or just as one of the two? Evaluated if UNION does not meet you?

This comment gives the solution to the situation, due to the way the tables are created, I needed to use a union all between a query aimed at the information of a Natural Person and another aimed at a Legal Person.