In SQL, how do I exclude result from SELECT * FROM …?
I know my title is not very descriptive... let me explain in details here.
Let say, if a table has 26 fields. e.g. field_a ... field_z. and I only want a select query to return me 15 fields only.
So, normally, I will do SELECT field_a, field_b ... field_o FROM myTable.
Which is tedious. Is there a way in MYSQL that I can do a SELECT * and tell it not to return certain fields?
e.g. soemthing like SELECT * exclude (field_p, field_q .. field_z) FROM myTable?
Thanks all for the answers. :)
delimiter // DROP PROCEDURE IF EXISTS `getColumnNames`// CREATE PROCEDURE `getColumnNames` (db_name CHAR(255), t_name CHAR(255), ex_name CHAR(255)) BEGIN SELECT group_concat(column_name) FROM `information_schema`.`COLUMNS` C WHERE table_schema = db_name AND table_name = t_name AND column_name not in (ex_name) GROUP BY table_schema,table_name; END // delimiter ; call getColumnNames("Db_name", "tbl_name", "col_to_exclude");
You should never use it in production code.
You should always specify the columns you want returned, like so:
SELECT `column1`, `column2`, `someothercolumn` FROM `myTable`
As always, the documentation can help with the nitty gritty!
SQL itself does not support the functionality you are asking for. (If it did, it would have the same problems as select *. See other's comments)
This is what I do when I'm using MySQL:
- Fire up mysql command line client
- Perform a describe my_table
- Copy the data in Field column (mouse select)
- Paste data in my editor (TextPad)
- Manually remove the columns I don't need
- Run a macro that substitutes new line for a comma (and insert a table alias)
All in all, it takes around 20-30 seconds to create a select list regardless of the number of columns. This way ensures that I don't misspell or forget any columns.
No use the fields you want (select * shouold not appear in production code even if you want allthe fields). I don't know about mySQL but in SQL Server I can drag and drop the columns which makes it easy to specify, is there a way to make this less tedious by dragging and dropping?
There's no way to do this because 'SELECT *' is a bad idea in a production environment anyhow. Just think of all the extra error-catching that would need to be done if something like this existed -- what if the excluded field didn't exist in the table? Does this create an error? A warning?
Enumerating all your fields is indeed tedious, but it's the correct way to do it. It makes your code (a little bit more) self-documenting, and helps stop errors early in the cycle. For one example, if 'user_name' is eventually renamed to 'username' for whatever reason, the SQL statement will fail and you won't have a strange data lolling around in your code waiting to be traced down.
I hope that there's none way to do this. Even if there is one, I'd suggest not to use it.
Kindof solution is to create a view that excludes the one row you want to have excluded, and you can select * from the view.