How to show in a query records that do not have a referenced datum

Question

I have 3 tables:

Person

CREATE TABLE Pessoa (
    ID_Pessoa INTEGER PRIMARY KEY IDENTITY (1,1),
    ID_Telefone INTEGER FOREIGN KEY REFERENCES Telefone (ID_Telefone),
    Nome_PSOA VARCHAR(50) NOT NULL,
    CPF_PSOA CHAR(14) NOT NULL,
    RG_PSOA VARCHAR(15) NOT NULL,
    Classe_PSOA VARCHAR(30) NOT NULL,
    Cadastro_Pendente_PSOA BIT 
);

Functionary

CREATE TABLE Funcionario (
    ID_Funcionario INTEGER PRIMARY KEY IDENTITY (1,1),
    ID_Pessoa INTEGER FOREIGN KEY REFERENCES Pessoa (ID_Pessoa) NOT NULL,
    ID_Cargo INTEGER FOREIGN KEY REFERENCES Cargo (ID_Cargo),
    Email_FUNC VARCHAR(50),
    Ramal_FUNC VARCHAR(4),
    Horario_Trabalho_FUNC VARCHAR(50) NOT NULL,
    Escala_FUNC VARCHAR(25),
    Permitir_Visitas_FUNC BIT,
    Descricao_FUNC VARCHAR(200) 
);

Office

CREATE TABLE Cargo (
    ID_Cargo INTEGER PRIMARY KEY IDENTITY (1,1),
    ID_Departamento INTEGER FOREIGN KEY REFERENCES Departamento (ID_Departamento) NOT NULL,
    Nome_CRGO VARCHAR(50) NOT NULL,
    Descricao_CRGO VARCHAR(200)
);

In my application I have a query that returns the following data (respectively): Id_pessoa - Id_funcionario - Pso_name - Cr_name - RG_PSOA - CPF_PSOA

The point is this : When I remove a post from the system I Seto NULL in the ID_Cargo all records of Funcionario that has that role so that I can delete it without referential integrity problems. However, when the query is executed, the DataGridView returns me only the records that have a ID_Cargo not null. My goal is, in the field Nome_CRGO of all records with the ID_Cargo = NULL, show nothing, but make them appear in the query.

Consultation that did not work :

SELECT TOP 20 dbo.Pessoa.ID_Pessoa AS 'ID', 
    ID_Funcionario AS 'ID do Funcionário', 
    Nome_PSOA AS 'Nome', 
    Nome_CRGO AS 'Cargo', 
    RG_PSOA AS 'RG', 
    CPF_PSOA AS 'CPF'
FROM dbo.Pessoa, 
    dbo.Funcionario, 
    dbo.Cargo 
WHERE dbo.Funcionario.ID_Pessoa = dbo.Pessoa.ID_Pessoa 
    AND (
         dbo.Cargo.ID_Cargo = dbo.Funcionario.ID_Cargo 
         OR dbo.Funcionario.ID_Cargo = NULL
        ) 
ORDER BY dbo.Pessoa.ID_Pessoa DESC

Answer 1

(...) When the query is executed, the DataGridView returns me only the records that have a ID_Cargo other than nil

This is due to the fact that you have defined an expression that will never be true in what would be the faculty statement of the office’s existence. Implicitly, you ended up defining a INNER JOIN between the 3 tables.

Behold:

...
dbo.Funcionario.ID_Pessoa = dbo.Pessoa.ID_Pessoa // INNER JOIN entre funcionario e pessoa
     AND (
          dbo.Cargo.ID_Cargo = dbo.Funcionario.ID_Cargo // Implicitamente INNER JOIN entre cargo e funcionario
          OR dbo.Funcionario.ID_Cargo = NULL // Nunca será verdadeiro
         ) 

This happens because the null behaves more like a 'state' than a value.

There is a question that right here at Sopt that further details the NULL issue in the database.

In short, in SQL, considering that the column ID_Cargo be as null, the result of the expression ID_cargo = NULL is Falso as well as the expression ID_cargo <> NULL also is. The correct form of comparison in this case would be ID_cargo IS NULL. So return Verdadeiro, as expected.

In particular, I prefer to separate what is criteria for joining tables (the clauses JOIN stated in the FROM) of what is a selection condition (those declared in WHERE). Like proposed by Rovann Linhalis, you could also make a LEFT OUTER JOIN explicitly in the consultation.

Thus:

SELECT TOP 20 dbo.Pessoa.ID_Pessoa AS 'ID', 
    ID_Funcionario AS 'ID do Funcionário', 
    Nome_PSOA AS 'Nome', 
    Nome_CRGO AS 'Cargo', 
    RG_PSOA AS 'RG', 
    CPF_PSOA AS 'CPF'
FROM dbo.Pessoa 
    INNER JOIN dbo.Funcionario ON dbo.Pessoa.ID_Pessoa = dbo.Funcionario.ID_Pessoa 
    LEFT JOIN dbo.Cargo ON dbo.Funcionario.ID_Cargo = dbo.Cargo.ID_Cargo
ORDER BY dbo.Pessoa.ID_Pessoa DESC

For this specific example, the consultation dispenses with the use of the WHERE because all conditions refer only to the criteria for joining the tables.