aswin mythili aswin mythili - 4 months ago 9
SQL Question

SQL ambigutiy when trying to display

Consider the following query i tried, there two tables, Orders and Customers, each have column name CustomerID, when i try to display both CustomerID's only one column is displaying, i can't understand why is it so, or am i understanding the basics wrong.

SELECT Customers.CustomerID,Orders.CustomerID
FROM customers
inner JOIN orders
on customers.customerid=orders.customerid;


When i try to display only one column it is displaying well and good

SELECT Customers.CustomerID
FROM customers
inner JOIN orders
on customers.customerid=orders.customerid;


and

SELECT Customers.CustomerID
FROM customers
inner JOIN orders
on customers.customerid=orders.customerid;


Atlast my problem is why i cant display the both.

Answer

This is a peculiarity of some query interfaces. Your first query (which I would write like this) is:

SELECT c.CustomerID, o.CustomerID
FROM customers c INNER JOIN
     orders o
     ON c.customerid = o.customerid;

This returns two columns, both named CustomerId. Some query interfaces insist that the resulting columns be unique in the result set. Hence, the results ignore "subsequent" columns with the same name.

You can get a flavor of this by using the query as a subquery:

SELECT x.*
FROM (SELECT c.CustomerID, o.CustomerID
      FROM customers c INNER JOIN
           orders o
           ON c.customerid = o.customerid
     ) x;

This should return an error, because CustomerId is ill-defined.

Three points to remember:

  • Most databases and query interfaces allow result sets with multiple columns with the same name.
  • No database allows multiple columns with the same name in a subquery.
  • You know how to fix this by assigning a column alias, which is a best-practice anyway.