Select with Null Field

Question

I’m doing a survey in a student database, and I started having the following problem: Every time a student fails to fill in the "address" field, even though he has all the other data in the table, the select does not return that student at all.

TABLE PERSON

id_pessoa	Cpf	identity	id_address
236589	04245897123	036690000	234567
236753	04245897123	036690000	NULL
224569	04245897123	036690000	NULL

ADDRESS TABLE

id_addressee	complement	neighborhood	city
234567	Rua X Casa y	Casa Nova	RIVER
256713	John Doe Street House 23	Address X	PAUL
223269	Street Ciclano nº 22	Hortolandia	CURITIBA

FOLLOW THE QUERY:

SELECT DISTINCT pessoa.nome as NOME, pessoa.cpf_cnpj as CPF, dis.matricula as MATRICULA, pessoa.numero_identidade as IDENTIDADE, 
pessoa.email as EMAIL, pessoa.data_nascimento as IDADE, pessoa.sexo as SEXO,
ende.logradouro as ENDERECO, ende.complemento as COMPLEMENTO, ende.bairro as BAIRRO
FROM comum.unidade un
INNER JOIN "public".curso cs ON cs.id_unidade = un.id_unidade
INNER JOIN "public".discente dis ON dis.id_curso = cs.id_curso
INNER JOIN comum.pessoa pessoa ON pessoa.id_pessoa = dis.id_pessoa
INNER JOIN ensino.matricula_componente mat ON mat.id_discente = dis.id_discente
INNER JOIN ensino.turma turma ON turma.id_turma = mat.id_turma
INNER JOIN comum.endereco ende ON pessoa.id_endereco = ende.id_endereco
WHERE cs.nivel IN ('G','L','E','S','D') -- níveis de curso: G [GRADUAÇÃO], L [LATO SENSU], S [STRICTO SENSU], E [MESTRADO], D [DOUTORADO], R [RESIDÊNCIA], F [FORMAÇÃO COMPLEMENTAR] 
AND turma.ano = 2020 -- mudar ano
AND cs.id_modalidade_educacao IN (1,2) -- modalidades de educação: 1 [PRESENCIAL], 2 [A DISTÂNCIA], 3 [SEMI-PRESENCIAL]

In short, I wanted to do this search and return the data of people who have all the other data even if they have no address, even though it is a key within the search. Because there are people who have all the research data, and those who don’t have the address are not returning.

IE, I wanted the return of all regardless of having or not registered address, since they have all other data. When I do the search without the address field, all candidates are returned normally.

Answer 1

Hello,

Replace "Inner Join" with "left Join" or another Join of your choice.

Ex:

left JOIN "public".curso cs ON cs.id_unidade = un.id_unidade
left JOIN "public".discente dis ON dis.id_curso = cs.id_curso
left JOIN comum.pessoa pessoa ON pessoa.id_pessoa = dis.id_pessoa
left JOIN ensino.matricula_componente mat ON mat.id_discente = dis.id_discente
left JOIN ensino.turma turma ON turma.id_turma = mat.id_turma
left JOIN comum.endereco ende ON pessoa.id_endereco = ende.id_endereco

So it will return the Row result even if the fields of the Join link are null.