How to get the records found and not found via IN

Question

I have a simple command

SELECT MATRICULA FROM ALUNOS WHERE ID_ALUNO IN (1,2,3,4,5,6)

I would like to know how to get via SQL a result like this:

ID| STUDENT

1 | LUIZ

2 | MARIA

3 |

4 | JOÃO

5 | CARLOS

6 |

I mean, bringing in the ones he found, and the ones he couldn’t find to leave blank... is via SQL?

I’m using Oracle 11

Answer 1

As the data do not exist one should resort to a "artifice"

SELECT MATRICULA , NOME FROM ALUNOS WHERE ID_ALUNO IN (1,2,3,4,5,6)
union
SELECT MATRICULA,NOME
FROM (SELECT ROWNUM MATRICULA, '' NOME FROM USER_OBJECTS WHERE ROWNUM < 7) VIRTUAL
WHERE NOT EXISTS (SELECT NULL FROM ALUNOS WHERE ID_ALUNO = MATRICULA)

A Nion brings back non-existent data. user_objects is just a "fake" table to generate a sequence