Suppose I have 2 tables which share some column names. In this case the primary key is both is called id
.
CREATE TABLE artists(
id int primary key,
name text,
-- …,
);
CREATE TABLE paintings(
id int primary key,
artistid references artists(id),
title text,
-- …,
);
Note: I know there are arguments against calling your primary key a generic name like id
, but let’s suppose, for argument’s sake that it’s out of my control. In any case, it could have been any other column for the purpose of this question.
Suppose I now have a carelessly written SELECT
statement which seeks to extract data from the referenced table using a correlated subquery:
SELECT
id, title,
(SELECT name FROM artists WHERE artistid=id) as artist
FROM paintings;
Clearly the inner WHERE
clause could have been better written as WHERE paintings.artistid=artists.id
. However I have got away with it and it works. It even works if I write the WHERE
clause as id=artistid
.
I know it’s not the best way to go about it, but I’m more surprised that it has understood my intention, which is not what we have come to expect from SQL.
The question is: How does SQL interpret ambiguous columns in a Correlated Subquery?
artistid
and anid
in theartists
table those two would be compared. If the artists table had neither of those columns, the columns of the outer table would be compared