CODEWITHSUNDEEP
mysqlsql
Simon
Simon

Reputation: 59

SQL no table name for columns

I have a join (not mine) with 3 different tables and certain columns selected. Some have the prefix of the corresponding table and some not. Example: 

Select a.column1 as column1, b.column2 as column2, column3 as column3, b.column4 as column4, c.column5 as column5, b.column6 as column6 from a, b, c where a.column1 = b.column1 and *column10* = b.column5

When is it necessary to mark some column as a specific column from a specific table and when not? Is there a way to find out from which table a certain column is, just by looking at the SQL statement? (for example column3 or column10)

Thank you!

Upvotes: 1

Views: 1126

Answers (3)

onedaywhen
onedaywhen

Reputation: 57023

The answer to this requires a brief history lesson.

In the relational model, an operation between two relations results in a relation and a relation has no duplicate columns. In the earliest version of SQL (in the 1970s!), it was decided a table expression could have duplicate columns. But how to tell them apart? The chosen solution was to use range variables, a concept that already existed in database theory.

Note terminology is slight confused here: the SQL standards (started in the 1980s!) used the term 'correlation name' (despite having no concept of 'correlation'); most vendor literature -- and therefore most SO users -- employ the term 'table alias' (which isn't appropriate because it takes the place of a row); the literature uses the term 'range variable' (as in "ranges over the rows of the table").

Later, when derived tables were introduced into SQL, the error of allowing duplicate columns was realised. Although the requirement of unique column names is enforced for derived tables from their beginning, duplicate column names are still allowed in the result of the top-level SELECT in a query because nothing is ever deprecated or removed from the SQL language.

In 1992 (in the 1990s!), the non-relational SQL operators were given corrected versions e.g. UNION became UNION CORRESPONDING and JOIN became NATURAL JOIN.

So using the most up-to-date join syntax

SELECT * FROM a NATURAL JOIN b

will join the tables using equality operator on columns with the same names, not require the use of range variables, and the resulting table expression will not have duplicate column names.

That said, it won't work with your example! Did you just make it up on the spot? It seems odd to me that two tables would have a common column named column5 but you would not use them to join; instead you choose to join one column5 to a column10. If these are real tables then you have deeper problems than you may realize :)

Upvotes: 0

Terry
Terry

Reputation: 66093

Some tables have duplicated column names—having aliases for each column is a helpful way of distinguishing between identically-named columns across multiple tables when performing a join. But a rule of thumb is that it's generally a good idea to use aliases, to avoid possible confusing situations from arising.

Also useful is when you are using operators on your column names, such as MAX(table1.ProductID). Instead of having the column being referenced directly as such, you can use MAX(table1.ProductID) AS MaxProductID to make column references more readable and friendlier.

Upvotes: 0

Rahul
Rahul

Reputation: 77846

Well it's always recommended to prefix the column name with table name or table alias for better readability and understanding but in cases when a column name can be duplicated in multiple table you are joining with then prefixing the table name with column name becomes mandatory for you to specify that which table column you are trying to fetch actually else you will end up getting Ambiguous column name error

Upvotes: 1

Related Questions