Btukfyl

There are three tables - mapping_hospital_procedure, master_hospital and master_procedure.

master_hospital contains an id column to identify each hospital.

master_procedure also contains an id column for the same purpose, i.e, to identify each procedure.

mapping_hospital_procedure is used to map hospitals to medical procedures and contains columns hid (hospital id) and pid(procedure id) besides other columns. Now, if I run this query :

SELECT hid FROM master_procedure

it doesn't work as you would because there is no hid column in master_procedure. But if I put this query in a sub-query, it works fine. Like this way, for example -

SELECT hid FROM mapping_hospital_procedure WHERE pid = (SELECT hid FROM master_procedure)

But it shouldn't work as there is no column named hid in master_procedure. It should give the same Unknown column error as is the case with the previous query. Can anyone explain why or how this works?

Thank you.

Scope issue. You sub-query has access to the main-query's columns.

Since hid isn't found in the sub-query table, it will step out and look for it in the main query - there it's found in mapping_hospital_procedure table.

SELECT hid FROM mapping_hospital_procedure

WHERE pid = (SELECT hid FROM master_procedure)

Is the same as

SELECT mhp.hid FROM mapping_hospital_procedure mhp

WHERE mhp.pid = (SELECT mhp.hid FROM master_procedure mp)

Lesson now learned: Always qualify your columns!

EDIT: " This should at least be a logical error giving incorrect output, right?"

Yes, that query doesn't make much sense.

If the master_procedure table contains no rows at all, the mhp.pid = (SELECT mhp.hid FROM master_procedure mp) will be false. No rows at all are returned.

If the master_procedure table contains 2 rows or more, the "Subquery returns more than 1 row" exception should be raised. (Since it's a = comparison. Use IN to avoid this problem.)

If the master_procedure table contains exactly 1 row, the mhp.pid = (SELECT mhp.hid FROM master_procedure mp) condition evaluates to true if mhp.pid is not null, and the row is returned from mapping_hospital_procedure. If mhp.pid is null, the row isn't returned.

What you really want to do is probably:

SELECT mhp.hid FROM mapping_hospital_procedure mhp

WHERE mhp.pid IN (SELECT mp.id FROM master_procedure mp)

With that query you are actually doing a natural join. https://www.w3resource.com/sql/joins/natural-join.php

The database parser is smart enough to translate your query into a join.
Your query:

SELECT hid FROM mapping_hospital_procedure 

WHERE pid = (SELECT hid FROM master_procedure)

Is basically the same as:

SELECT hid FROM mapping_hospital_procedure 

NATURAL JOIN master_procedure 

WHERE pid = hid