Select with multiple Left Joins Sql Server

Question

I took an example of people registering with the following structure:

I’m trying to make a SELECT in tblPessoa with JOIN with the intention that select returns data from the table in which Id exists, select:

select tblPessoaJuridica.Nome as [NomePJ], tblPessoaFisica.Nome as [NomePF], tblPessoaProdutor.Nome as [NomeProdutor] 
from tblPessoa 
left join tblPessoaFisica on tblPessoaFisica.IdPessoaFisica = tblPessoa.idPessoa
left join tblPessoaJuridica on tblPessoaJuridica.IdPessoaJuridica = tblPessoa.idPessoa 
left join tblPessoaProdutor on tblPessoaProdutor.IdPessoaProdutor = tblPessoa.idPessoa
where idPessoa = 2547

However I have read that the left Join is slow, example:

Slow query in SQL Server with left Join

Left Joins are what I want but they are very slow?

among others.

What would be the most efficient way to replace this left Join ?

EDIT

I put 'NAME' just for example, the PJ has Social Reason, the PF has the Name and the Producer has the name of the Farm/Farm/Etc. This diagram is an example I took.

Answer 1

You can use the clause UNION which will "concatenate" the results of querys that have the same definitions of resulting columns. So you can assemble a return of people with their proper types.

UNION

It combines the results of two or more queries into a single set of results, which includes all lines belonging to all union consultations. The UNION operation is different from using joins that combine columns of two tables.

The following are the basic rules for combining the result sets of two queries using UNION:

• The number and order of columns should be equal in all queries.

• Data types shall be compatible.

Down with the scheme you’ve determined.

SELECT tpj.nome,
       'J' as tipo
  FROM tblPessoaJuridica tpj
  INNER JOIN tblPessoa tp ON tp.idPessoaJuridica = tpj.idPessoaFisica
UNION
SELECT tpf.nome,
       'F' as tipo
  FROM tblPessoaFisica tpf
  INNER JOIN tblPessoa tp ON tp.idPessoa = tpf.idPessoaFisica
UNION
SELECT tpp.nome,
       'P' as tipo
  FROM tblPessoaProdutor tpp
  INNER JOIN tblPessoa tp ON tp.idPessoa = tpp.idPessoaProdutor

You can use the select above to create a view which will facilitate reuse.

VIEW

Creates a virtual table whose contents (columns and rows) are defined by a query. Use this statement to create a view of the data in one or more tables in the database.

Answer 2

Create an ID to identify the type of person and in the programming use this id to select the information.

Example:

My Idpessoa is 555 and my Idtipopessoa is 2 (Physical person):

if(IDTipoPessoa == 1){

}else if(IDTipoPessoa == 2){

}else if(IDTipoPessoa == 3){

}else{
   //retorna erro tipo inválido
}

Whenever you have a new type just add an Else. Or put the following, put everything in the same table, be it Product/Legal person and select is like this:

     SELECT COALESCE(CNPJ,CPF,nomeProduto,'ERRO') as NOME FROM tblPessoa 
JOIN tblinformacoes on idPessoa = FKidPessoa

I guess it could be that way, it depends more on your business rule. Hug,

Answer 3

Not considering the possibility of restructuring its tables, it is quite likely that the LEFT JOIN is the best alternative after all.

Also, you should only be concerned about performance in this case if you really have evidence that this is cluttering your system. In this case, a possible solution would be to copy the data into another cache table with a denormalized structure to expedite the query, or to keep this table or parts of it in memory. However, these things are something you will only think about doing if you have a real need for such.

Another possibility is to make sure that all three tables have records for each ID of the tblPessoa, even if filled with a bunch of nulls and with it exchange the LEFT JOINs for INNER JOINs. However, if you are going to do this trick, it would be better to change the structure of the tables.

Answer 4

• First your tables are wrong
• If name is common to all, so it should be on the parent table
• Common fields are in the parent table
• In the parent table you should define a type, which represents, what type is each person
• Yes can be slow, because it maps wrong and does not create the correct indexes.
• Another way to do it is with subconsultation instead of left Jon