Mysql unknown column in subquery works

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 expect 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.

Answer

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)