1
I’m a beginner in the SQL world, I’m learning some functions and I came across a problem where I can’t understand.
I have a table medico with the attribute cod_medico. In that attribute is the information: 1, 2 and 3.
I have another table paciente with the attribute cod_paciente where there is information: 3, 4 and 5.
I want to get all the cod_medicos that exist in cod_paciente.
I know the correct answer is the code below where he brings the answer 3:
SELECT cod_medico
FROM medico
WHERE EXISTS (SELECT cod_paciente FROM paciente WHERE cod_paciente = cod_medico)
However, I cannot understand why the logic below is incorrect:
SELECT cod_medico
FROM medico
WHERE EXISTS (SELECT cod_paciente FROM paciente)
This answer returns all attribute data cod_medico: 1, 2 and 3.
In my opinion, SQL would seek the first information of the attribute cod_medico (that would be 1) and make the comparison with all attribute data cod_paciente where he would show me nothing, the second time he would make the comparison with the value 2 where I would also show me nothing and ultimately do with the value 3 where you would make the comparison and show me the value 3.
But where is the error in that logic?
Maybe the way I think is not the same as the SQL behaves.
The Exists operator returns true if your subquery returns any value, and this occurs when you remove Where because there is data in the table
– imex
I think you might be messing with the operator In
– imex
It is incorrect because if there is any record in the table
pacientewill be the necessary condition to select all records from the tablemedico. Your example is kind of strange because you want to relate the table datamedicowith the table datapacientebased on a code that in reality does not have the same meaning, although they may have common values. Evaluate the use of INNER JOIN to relate records of the two tables to a common field.– anonimo