Excluding some columns in a SELECT statement

Question

In the result for

SELECT * from myTable WHERE some-condition;

I'm interested in 9 of all the 10 columns that exist. The only way out is to specify the 9 columns explicitly ?

I cannot somehow specify just the column I don't want to see?

Answer 1

No, you can't (at least not in any SQL dialect that I'm aware of).

It's good practice to explicitly specify your column names anyway, rather than using SELECT *.

Answer 2

The only way is to list all 9 columns.

Such as:

SELECT col1, col2, col3, col4, col5, col6, col7, col8, col9 FROM myTable

Answer 3

No, you can not. An example definition of select list for Sybase can be found here, you can easily find others for other DBs

The reason for that is that the standard methods of selection - "*" (aka all columns) and a list of columns - are defined operations in relational Algebra whereas the exclusion of columns is not

Also, as mentioned in Joe's comment, it is usually considered good practice to explicitly specify column list as opposed to "*" even when selecting all columns.

The reason for that is that having * in a joined query may cause the query to break if a table schema change introduces identically-named fields in both of the joined tables.

However, when selecting without a join from a very wide and often-mutating table, the above rule may not apply, as having "*" makes for a good change management (your query is one less place to fix and release when adding new columns), especially if you have flexible DB retrieval code that can dynamically deal with a column set from table definition instead of something specified in the code. (e.g., 100% of our extractors and loaders are fully working whenever a new column is added to the DB).

Answer 4

If you had to (can't think of why), but you could dynamically create this select statement by querying the columns in this table and exclude the one column name in the where clause.

Not worth the performance hit, confusion, and maintenance issues that will come up.

Answer 5

You actually need to specify the columns explicitly (as said by Luke it is good practice), and here is the reason:

Let's say that you write some code / scripts around you sql queries. You now have a whooping 50 different selects in various places of your code.

Suddenly you realize that for this new feature you are working on, you need another column (symmetry, you are doing cleanup and realize a column is useless and wasting space, though it is harder).

Now you are in either of this 2 situations:

• You explicitly stated the columns in each and every query: Adding a column is a backward compatible change, just code your new feature and be done with it.
• You used the '*' operator for a few queries: you have to track them down and modify them all. Forget a single one and it will be your grave.

Oh, and did I specify that a query with a '' selector takes more time to be executed since the DB actually has to query the model and develop the '' selector ?

Moral: only use the '*' selector when you are checking manually that your columns are fine (at which point you actually need to check everything), in code, just bane them or they'll be your doom.

Answer 6

In the end, you need to specify all 9 out of 10 columns separately - but there's tooling help out there which helps you make this easier!

Check out Red-Gate's SQL Prompt which is an intellisense-add-on for SQL Server Management Studio and Visual Studio.

Amongst a lot of other things, it allows you to type

SELECT * FROM MyTable

and then go back, put the cursor after the " * ", and press TAB - it will then list out all the columns in that table and you can tweak that list (e.g. remove a few you don't need).

Absolutely invaluable - saves hours and hours of mindless typing! Well worth the price of a license, I'd say.

Highly recommended!

Marc