Wrong SQL result due to ALIAS used in SQL Query

We have already seen about ALIAS use in any SQL. However if ALIAS is not used properly in any SQL Query, then it may result in wrong result set. We may have written right query syntactically however due to ALIAS improper usage, result set may come wrong. There can be one big disadvantage of ALIAS used in SQL Query if not taken care of. Most of the time, it will be difficult to trace the problem. Good thing is it is easy to avoid such problem. Let us see below query and see this problem with an example.

SELECT CUSTOMERS.CUST_ID, CUSTOMERS.CUST_NAME, CUSTOMERS.CUST_CITY, CUSTOMERS.CUST_OPERATOR FROM CUSTOMERS;

Wrong result due to alias usage
SELECT * FROM CUSTOMERS; — 10 ROWS

In this example we have not created any ALIAS, hence while fetching the columns from table we have mentioned complete tablename along with columnnames. The result will contain 10 rows. Now look at the below query:

SELECT CUSTOMERS.CUST_ID, CUST.CUST_NAME, CUST.CUST_CITY, CUST.CUST_OPERATOR
FROM CUSTOMERS CUST;

In this example, we have created ALIAS for table CUSTOMERS as CUST. However while selecting columns we missed to add proper table ALIAS for the first column. In this case, in select clause for the first column we still have complete tablename instead of table ALIAS. Can you guess the output rows in this case ? The result will contain 100 rows. It is because while executing the query, the table CUSTOMERS and the ALIAS CUST will be treated as different entity and sql engine will put cross join for these two entities. Hence, the output will contain 100 rows [10 * 10] instead of 10.

Leave a Reply

Your email address will not be published. Required fields are marked *