How to obtain via IN the records found and not found

Navigation
1

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 a result like this in SQL:

ID | STUDENT

1 | LUIZ

2 | MARIA

3 |

4 | JOHN

5 | CARLOS

6 |

That is, bring the ones he found, and those he did not think left blank ... does he have via SQL?

I'm using Oracle 11

    
asked by anonymous 07.11.2014 / 13:43

1 answer

2

As the data does not exist, one must resort to an "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 union brings back data that does not exist. The user_objects is just a table "fajuta" to generate a sequence

    
07.11.2014 / 15:20
Print arrays with line break Average calculation problem between int and float