CODEWITHSUNDEEP
sqljoinsybase
DVK
DVK

Reputation: 129549

Avoiding selecting same-name columns in a join

TABLE_A:  columns id, colA1, colA2, ...
TABLE_B:  columns id, colB1, colB2, ...

QUERY1:   SELECT *
          FROM TABLE_A, TABLE_B
          WHERE TABLE_A.id = TABLE_B.id
-- This works perfectly

QUERY2:   SELECT *
          INTO #tempT
          FROM TABLE_A, TABLE_B
          WHERE TABLE_A.id = TABLE_B.id
-- This fails because A.id and B.id have same column name in SELECT
-- ERROR: Column names in each table must be unique.

One obvious way of fixing the problem with query #2 is to select all non-ID columns from TABLE_B explicitly:

QUERY3: SELECT TABLE_A.*, TABLE_B.colB1, TABLE_B.colB2, ...

The problem with that approach is 2-fold, and is really a generic problem with hard-coding exact list of columns into a SELECT query:

  1. Hard-coding the columns makes the code brittle against tables that have more columns added later. Any time you add a column to TABLE_B, you will need to change, re-test, and re-release the code with the given query.

  2. If there are a lot of columns in a table, the query looks awful and is hard to read due to overly-long SELECT statement

Is there a Sybase SQL specific solution to my problem with query #2, which avoids the issue with query #3?

I am looking for a solution using Sybase SQL. NOT applicable: solutions involving using the application code to read the table structure and construct the query #3 above. Nor solutions using eval in-Sybase.

Upvotes: 1

Views: 2070

Answers (1)

Kateract
Kateract

Reputation: 852

Short answer: NO.

Long answer:

You're not going to like it, but the answer is to not use * when you are authoring a query.

Specifying which columns to select is ideal when you are writing code to use the results, so that you know which columns are part of the returned dataset. Contrary to what you said, not specifying columns that are selected makes down-stream processes brittle, because if someone adds a column to the table, an unexpected column shows up in the resulting dataset. If you specify each column in the select, then whenever you add a column to the table, existing reports or views work exactly as they did before.

Also, key columns are not necessarily the only ones that have ambiguity; if you have two tables that are keyed on ID, but also have a name column (Say, a students table and a faculty table) then you will also have column ambiguity even if you exclude the ID column from the select. However, both of those columns could be important in the result set you are looking for.

Not Sybase SQL Specific, but MSDN offers this caution about Choosing All Columns:

If a SELECT is used in an application or script that has logic dependent on the number of columns in the result set, it is better to specify all the columns in the select list instead of specifying an asterisk. If columns are later added to the table or views referenced by the SELECT statement, the application is shielded from the change if the columns were listed individually. If an asterisk (*) was specified, the new columns become a part of the result set and may affect the logic of the application or script. You should avoid using the asterisk (*), especially against catalog views, dynamic management views, and system table-valued functions. Future upgrades and releases of Microsoft SQL Server may add columns and change the order of columns to these views and functions. These changes may break applications that expect a particular order and number of columns.

Upvotes: 1

Related Questions