Clever Geek Handbook

Excluding Certain Columns in a SELECT Statement

As a result, for 

SELECT * from myTable WHERE some-condition;

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

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

+2


source to share


6 answers


The only way is to list all 9 columns.

For example:



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


source


No, you cannot. A rough definition of a picklist for Sybase can be found here , you can easily find others for other DBs

The reason for this is that the standard selection methods - "*" (aka all columns) and list of columns - are defined operations in relational algebra , whereas excluding columns is not

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



The reason for this is that the presence of * in a joined query can break the query if a table schema change introduces the same name in both joined tables.

However, when selecting without joining from a very wide and frequently changing table, the above rule may not apply as the presence of the "*" makes good change management possible (your query takes up less patch and release space when adding new columns), especially if you have there is flexible DB fetch code that can dynamically handle a set of columns from a table definition instead of what is specified in the code. (for example, 100% of our extractors and loaders are fully functional when a new column is added to the DB).

+3


source


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

Not worth the performance, confusion, and maintenance issues that will arise.

+2


source


You really need to specify the columns explicitly (as Luke said, this is good practice), and here's why:

Let's say you are writing code / scripts around your sql queries. You now have 50 different options in different places in your code.

Suddenly you realize that this new feature you are working on needs a different column (symmetry, you do the cleanup and realize that the column is useless and wastes space, even though it's harder).

You are now in either of two situations:

  • You have explicitly specified the columns in every query: Adding a column is a backward compatible change, just enter the code for your new function and be done with it.
  • You have used the '*' operator for several queries: you must track them and change them all. Forget one thing and this will be your grave.

Oh, and I pointed out that the selector query takes longer to execute since the DB actually requires a model query and selector design?

Moral: only use the "*" selector when you manually check that your columns are ok (at what point you really need to check everything), in the code just skip them or they will become your doom.

+1


source


No, you cannot (at least not in every SQL dialect I know of).

It is good practice to explicitly list the column names anyway, rather than use them SELECT *

.

0


source


After all, you need to specify all 9 of the 10 columns separately - but there are tools out there to help you make it easier!

This will open the Red-Gate SQL Prompt which is an intellisense add-on for SQL Server Management Studio and Visual Studio.

Among many other things, it allows you to enter

SELECT * FROM MyTable

and then go back, put your cursor after "*" and press TAB

- it will display all columns in this table and you can customize this list (for example, delete a few that you don't need).

Absolutely priceless - saves hours and hours of meaningless typing! I would say it is worth the price of the license.

Highly Recommended!

Mark

0


source






More articles:


All Articles