How to avoid Ambiguous column perfectly in SELECT query

In SQL select statement one of the keyword you use to get the result is ‘FROM’. OK, why FROM is needed. It is the area you need to mention source details. I mean, from which table or view you need to get the data.

So for good…

Let us take an example…

SELECT NAME

FROM UPFALL

WHERE ID=2;

This query does retrieve data from UPFALL table or view where the matching column, i.e. id=2.

The other point is how to refer same table column name using alias. This way of writing SQL query avoids confusion that the column belongs to which table.

Do not worry..

I will tell this concept with an awesome example.

SELECT U.NAME

    (SELECT COUNT (*) FROM UPFAL U2

       WHERE U2.CONTRY_ID = U.CONTRY.ID)

FROM UPFALL U;

Here, you should not write the above query without using SQL alias. Here, the alias are ‘u, and u2’.

Since in the above query we are referring same columns of the same table. Also, this is one of the complex SQL interview question.

The result you will get is names who are having same country id. This SQL query is most interesting and also very important for SQL developers.

Advertisements

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.