Select with INNER JOIN, bringing fields that have NULL foreign key

Question

I have a table items, requested items, interested (she has self relationship, is used for secretariat, sector and employee, and a table requested), in the table requested I have 3 foreign keys (one for secretariat, another for sector and one for employee)where the foreign registry key is always filled, and the other ones are optional, with null when they are not filled.

I need to create a select that brings me a result even when these foreign keys for sector and employee in the requested table are NULL.

The select I made only shows me the records that have value in id_secretariat, id_sector and id_funcionaio in the requested table:

SELECT pedido.id as id_pedido, pedido.id_secretaria, pedido.id_setor, pedido.id_funcionario, pedido.dataretirada, 
   pedido_itens.id_item, pedido_itens.quantidade,  pedido_itens.datadevolucao,
   item.nome as item_nome,  
   secretaria.nome as secretaria,
   setor.nome as setor,
   funcionario.nome as funcionario
   FROM pedido 
   INNER JOIN pedido_itens ON (pedido.id = pedido_itens.id_pedido)
   INNER JOIN item ON (pedido_itens.id_item = item.id)
   INNER JOIN interessado as secretaria ON (pedido.id_secretaria = secretaria.id)
   INNER JOIN interessado as setor ON (pedido.id_setor = setor.id) 
   INNER JOIN interessado as funcionario ON (pedido.id_funcionario = funcionario.id);

Answer 1

Use the left join instead of inner join

select pedido.id as id_pedido, pedido.id_secretaria, pedido.id_setor, pedido.id_funcionario, pedido.dataretirada, 
   pedido_itens.id_item, pedido_itens.quantidade,  pedido_itens.datadevolucao,
   item.nome as item_nome,  
   secretaria.nome as secretaria,
   setor.nome as setor,
   funcionario.nome as funcionario
   FROM pedido 
   INNER JOIN pedido_itens ON (pedido.id = pedido_itens.id_pedido)
   INNER JOIN item ON (pedido_itens.id_item = item.id)
   LEFT JOIN interessado as secretaria ON (pedido.id_secretaria = secretaria.id)
   LEFT JOIN interessado as setor ON (pedido.id_setor = setor.id) 
   LEFT JOIN interessado as funcionario ON (pedido.id_funcionario = funcionario.id);

Answer 2

There is the option you can use LEFT JOIN, take a look at this post click here, still you would need to check your business rule, and still have the issue of performance.