Suppose we have 20 columns in a table and I want to return 19 of them. How can I do that ?
select *
will give me all of them but I want only 19.
Is there a good solution for that situation ? something like
select * - [columnName]
?!?
views:
69answers:
3
+8
A:
Nope, sorry. You can take *, or you can take them one at a time, but you can't take "all of them except for X, Y, or Z."
mquander
2009-12-15 06:07:04
+1: Correct - no such syntax exists
OMG Ponies
2009-12-15 06:09:41
+2
A:
You can explicitly name each column you wish to select. That is the only way to exclude columns.
Myles
2009-12-15 06:14:34
+5
A:
As has been said, you use SELECT * for all columns or list the columns individually if you don't want them all.
Listing columns does seem like a chore but there is an important reason why it's actually good.
While it's OK for ad hoc queries, it's highly recommended that use don't use SELECT * in code because when the database schema changes you will get different columns in the results returned to your application which is almost certainly not what you want. If you could do select * but address from customer this would have the same problem: changing the DB would change the structure of the results of your query which is bad.
So not only can you not do it, I would recommend not doing it even if you could.
Dave Webb
2009-12-15 06:16:56
By your logic it would also be bad to explicitly list each column name, because the schema could change to make those column names no longer valid. You should write code that does what you need, if those needs change plan on refactoring. That's not to say planning ahead isn't prudent, but always plan on code changing when needs change.
Myles
2009-12-15 06:35:58
@Myles: It's not the same. If you list the column names and the schema changes, your query fails right away and you know the reason why. If you `SELECT *` and the schema changes, your query will succeed and maybe your code will execute but get weird-but-non-obvious bugs because the data isn't what it is supposed to be.It's not simply about the schema changing, but what happens when it does.
benzado
2009-12-15 07:06:17