Ambiguous Column Error in Subquery – Here’s Resolution

Here’s a resolution for ambiguous column errors that occurs in a subquery.

SQL query with where clause

SELECT NAME
FROM UPFALL
WHERE ID=2;

Here, it retrieves data from the UPFALL table( or view) for matching id=2.

There is a concept called Alias. By using it you can refer to the same table column name many times.

So alias avoids ambiguous, and column name treats as unique.

SQL query how to use Alias

SELECT U.NAME
    (SELECT COUNT (*) FROM UPFAL U2
       WHERE U2.CONTRY_ID = U.CONTRY.ID)
FROM UPFALL U;

Here, the aliases are ‘u, and u2’. If you do not use you will get an ambiguous column error. Since in the above query we are referring same columns of the same table. Also, this is one of the SQL interview questions of ambiguous column error.

The result of this query fetches names of whose country id is the same. This SQL query is tricky and will ask in top SQL interviews.

Related Posts