Implications of using Inner Join and left Join in a single select

Question

I have some stored procedures in a SQL Server 2008 R2 database, these stored procedures have several joins, in some cases I used in it select, 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 the select above were only to exemplify the three cases:

1. records that will always exist in both tables (Person x Birth)
2. records that may not exist in one of the tables (Person x Phone); and
3. records that depend on another table that is in the Join (Telephone x Tipotelefone)

I took a look at the question What is the difference between Ner Join and Uter Join? which explains in detail the joins, but it doesn’t talk about using more than one type of Join in a select.

I would like to know if there are any problems/implications of using joins exemplified above and if there is any point of attention when using this approach.

Answer 1

There is a logical order to follow.

For your model, it is possible to change the order of your joins without any problem. This is because they are all 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 tables below:

  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, IE, not all have a record in tb_pessoa_usuario. Also not all are legal persons, only those who are registered in tb_pessoa_juridica.

Imagine a scenario of a listing where your client wants to know who all the people with access to the system are (i.e., those with user registration). Also, all legal persons should appear, including their user if any. If the legal entity does not have a user, he wants them to appear in the listing so that he can register a user for them.

Take the case of a SELECT where order matters:

     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 your LEFT JOIN in point (2). This is because you do not want all information from the initial table, you want an association INNER amid tb_pessoa and tb_pessoa_usuario. So you can’t make one LEFT JOIN, you will catch people who are not users.

The correction would 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 gathering information that may appear.

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

1. Always use first INNER JOINs when there is. If there is OUTER JOINs, put them on after

2. INNER JOINS are calculated faster than OUTER JOINs, this way your query gets faster when they start first.

3. Seek to associate your JOINs (all of them, INNER and OUTER) with primary keys (PK) from one table with foreign keys (FK) from the others. Do not put a filter in the ON, use filters on WHERE. Information is connected faster with keys. Do not mix association (keys) with filters!!!!!!!!!!!!!!!!!

4. Study the CROSS APPLY if you are using Microsoft SQL Server banks! It can be much better than the INNER JOINs in various situations! This has a lot to do with the order in which the database selects the data and then filters it.