Implications of using inner join and left join on the same select

Question

Implications of using inner join and left join on the same select

Navigation

#1 by (5 votes)

8

I have some stored procedures in a SQL Server 2008 R2 database, these stored procedures have several joins , in some cases I used the < , inner join and left join , for example:

Tabela Pessoa
| IDPessoa | Nome  |
|    1     | João  |
|    2     | Maria |
|    3     | José  |

Tabela Nascimento
| IDData | IDPessoa | DataNascimento |
|   1    |    1     |   01/01/2000   |     
|   2    |    2     |   10/05/2001   |
|   3    |    3     |   25/09/2009   |

Tabela Telefone
| IDTelefone | IDPessoa | IDTipo |  Numero   |
|      1     |     1    |    1   | 1523-4565 |
|      2     |     3    |    5¹  | 8481-9847 |
¹ esse valor não existe na tabela TipoTelefone.

Tabela TipoTelefone
| IDTipo |     Tipo    |
|    1   | Comercial   |
|    2   | Residencial |

SELECT 
    Nome, 
    DataNascimento, 
    Numero, 
    Tipo 
FROM 
    Pessoa
    INNER JOIN Nascimento ON (Pessoa.IDPessoa = Nascimento.IDPessoa)
    LEFT JOIN Telefone ON (Pessoa.IDPessoa = Telefone.IDPessoa)
    LEFT JOIN TipoTelefone ON (Telefone.IDTipo = TipoTelefone.IDTipo)

Note: The tables and select above were just to illustrate the three cases:

records that will always exist in both tables (Person x Birth)

records that may not exist in one of the tables (Person x Phone); and

records that depend on another table that is in the join (Phone x PhoneType)

I took a look at the question What is the difference between inner join and outer join? that explains in detail the joins , but it does not talk about using more than one join type in a select .

I would like to know if there is any problem / implication of using the joins exemplified above and if there is any point of attention when using this approach.

join sql-server

asked by anonymous 17.12.2014 / 00:55

1 answer

Why is the path not recognized by Silex when there is a slash "/" at the end of the requested url? PHP: Upload file

score 5 · Accepted Answer

There is a logical order to follow.

For your model, you can change the order of your joins without any problem. This is because all of them are based on a main table that will always be present to perform their comparisons (Person table).

But that may not always be the case. Imagine a system with the following tables:

  tb_pessoa           tb_pessoa_juridica  tb_pessoa_usuario
  ------------------  ------------------  ------------------------
  id_pessoa           id_pessoa           id_pessoa
  dc_apelido          dc_razao_social     dc_usuario_login
  dc_email            dc_cnpj             dc_senha

Not all people have access to the system, that is, not all have a record in tb_pessoa_usuario . Also not all are legal entities, only those that are registered in tb_pessoa_juridica .

Imagine a scenario in a listing where your customer wants to know who all the people with access to the system are (ie, those with a user registration). Also, all legal entities should appear, and the user will be listed if there are any. If the legal entity does not have a user, it wants them to appear in the listing so that it can register a user for them.

See the case of a SELECT where the order imports:

     SELECT 
           p.id_pessoa
          ,p.dc_apelido
          ,p.dc_email
          ,u.dc_usuario_login
          ,j.dc_razao_social
     FROM
          tb_pessoa p                   -- (1)traz todas as pessoas
     LEFT JOIN
          tb_pessoa_juridica j ON       -- (2)junta informações da jurídica se houver
             p.id_pessoa = j.id_pessoa
     INNER JOIN                         -- (3)erro: agora pega somente as que tem usuário
          tb_pessoa_usuario u ON
             p.id_pessoa = u.id_pessoa

The INNER JOIN in point (3) is destroying its LEFT JOIN in point (2). This is because you do not want all the information in the start table, you want a INNER association between tb_pessoa and tb_pessoa_usuario . So you can not make a LEFT JOIN , you will get people who are not users.

The correction would be to change the order from (2) to (3):

     SELECT 
           p.id_pessoa
          ,p.dc_apelido
          ,p.dc_email
          ,u.dc_usuario_login
          ,j.dc_razao_social
     FROM
          tb_pessoa p                   -- (1)traz todas as pessoas
     INNER JOIN                         -- (2)pega somente as que tem usuário
          tb_pessoa_usuario u ON
             p.id_pessoa = u.id_pessoa
     LEFT JOIN
          tb_pessoa_juridica j ON       -- (3)junta informações da jurídica se houver
             p.id_pessoa = j.id_pessoa

In this case, we are first taking information that is mandatory and then joining with information that may appear.

There is no rule to follow, only logic. But I like to take some precautionary measures in my constructions:

Always first use% s of% s when there is. If there are INNER JOIN s, place them later

OUTER JOIN are calculated faster than INNER JOINS s, so your query gets faster when they start first.

Try to associate your% s of% s (all of them, OUTER JOIN and JOIN ) with primary keys (PK) of one table with the foreign keys (FK) of the others. Do not put a filter on INNER , use filters on OUTER . Information is linked faster with keys. Do not mix association (keys) with filters !!!!!!!!!!!!!!!!!

Study ON if you are using Microsoft SQL Server databases! It can be much better than% s of% s in many situations! This has a lot to do with the order in which the database selects the data and then filters the data.