2

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?

  • 1
    The rule is: the closest match wins. If there was an artistid and an id in the artists table those two would be compared. If the artists table had neither of those columns, the columns of the outer table would be compared – a_horse_with_no_name Feb 17 '17 at 7:39
6

It resolves the column names in scope order. Inside the sub query columns will be resolved against those from the artists table if there is such ambiguity.

This can sometimes be quite useful though it can also be quite dangerous.

I usually use table aliases and two part names to avoid any unexpected issues and make it clearer to the reader which table each column is from.

SELECT p.id,
       p.title,
       (SELECT a.name
        FROM   artists a
        WHERE  p.artistid = a.id) AS artist
FROM   paintings p; 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.