Doubt about using LEFT JOIN in mysql

Question

I have the following problem:

I have two tables. students and proof

students have 10 students tests has 9 results (because one student missed)

The union of the tables is given by the matricula field.

I want to unite the two tables and display all the students (including what was missing). In research I saw that if LEFT JOIN could.

I’m managing to show off. Only the student who missed doesn’t come out at the screening.

My appointment is like this:

SELECT alunos.*, provas.* FROM alunos 
LEFT JOIN provas ON alunos.matricula = provas.aluno_matricula 
WHERE provas.cod_prova = '00112233'
ORDER BY alunos.nome ASC

This type of query would agree to display the name of the student who has in the table students and has no grades in the table?

Thank you!

Answer 1

In this case, you will return all student table records, even if they are not in the test table, but since you are filtering data from the test table, you should also consider the null value, because when there is no corresponding record, the value will be null, for example:

SELECT alunos.*, provas.* FROM alunos 
LEFT JOIN provas ON alunos.matricula = provas.aluno_matricula 
WHERE provas.cod_prova = '00112233' or provas.cod_prova is null
ORDER BY alunos.nome ASC

Doing so, even if there are no corresponding records in the proof table, will bring.