CODEWITHSUNDEEP
sqlselect
vtbose
vtbose

Reputation: 325

Excluding some columns in a SELECT statement

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?

Upvotes: 2

Views: 5059

Answers (6)

marc_s
marc_s

Reputation: 754478

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

Upvotes: 0

DVK
DVK

Reputation: 129403

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).

Upvotes: 3

Matthieu M.
Matthieu M.

Reputation: 299810

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.

Upvotes: 1

JeffO
JeffO

Reputation: 8043

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.

Upvotes: 2

Ben S
Ben S

Reputation: 69342

The only way is to list all 9 columns.

Such as:

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

Upvotes: 11

LukeH
LukeH

Reputation: 269368

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 *.

Upvotes: 0

Related Questions