SELECT * EXCEPT

gms8994_16645 0
Viewed 78144

Is there any RDBMS that implements something like SELECT * EXCEPT? What I'm after is getting all of the fields except a specific TEXT/BLOB field, and I'd like to just select everything else.

Almost daily I complain to my coworkers that someone should implement this... It's terribly annoying that it doesn't exist.

Edit: I understand everyone's concern for SELECT *. I know the risks associated with SELECT *. However, this, at least in my situation, would not be used for any Production level code, or even Development level code; strictly for debugging, when I need to see all of the values easily.

As I've stated in some of the comments, where I work is strictly a commandline shop, doing everything over ssh. This makes it difficult to use any gui tools (external connections to the database aren't allowed), etc etc.

Thanks for the suggestions though.

sql-except sql
15 Answers

As others have said, it is not a good idea to do this in a query because it is prone to issues when someone changes the table structure in the future. However, there is a way to do this... and I can't believe I'm actually suggesting this, but in the spirit of answering the ACTUAL question...

Do it with dynamic SQL... this does all the columns except the "description" column. You could easily turn this into a function or stored proc.

declare @sql varchar(8000),
    @table_id int,
    @col_id int

set @sql = 'select '

select @table_id = id from sysobjects where name = 'MY_Table'

select @col_id = min(colid) from syscolumns where id = @table_id and name <> 'description'
while (@col_id is not null) begin
    select @sql = @sql + name from syscolumns where id = @table_id and colid = @col_id

    select @col_id = min(colid) from syscolumns where id = @table_id and colid > @col_id and name <> 'description'
    if (@col_id is not null) set @sql = @sql + ','
    print @sql
end

set @sql = @sql + ' from MY_table'

exec @sql
user_52550

Create a view on the table which doesn't include the blob columns

Paul_Dixon_65210

Stay away from SELECT *, you are setting yourself for trouble. Always specify exactly which columns you want. It is in fact quite refreshing that the "feature" you are asking for doesn't exist.

user_486840

I believe the rationale for it not existing is that the author of a query should (for performance sake) only request what they're going to look at/need (and therefore know what columns to specify) -- if someone adds a couple more blobs in the future, you'd be pulling back potentially large fields you're not going to need.

Rowland_Shaw_504470

Your question was about what RDBMS supports the * EXCEPT (...) syntax, so perhaps, looking at the jOOQ manual page for * EXCEPT can be useful in the future, as that page will keep track of new dialects supporting the syntax.

Currently (mid 2022), among the jOOQ supported RDBMS, only H2 and BigQuery support the syntax natively. The others need to emulate it by listing the columns explicitly:

-- ACCESS, ASE, AURORA_MYSQL, AURORA_POSTGRES, COCKROACHDB, DB2, DERBY, EXASOL, 
-- FIREBIRD, HANA, HSQLDB, INFORMIX, MARIADB, MEMSQL, MYSQL, ORACLE, POSTGRES, 
-- REDSHIFT, SNOWFLAKE, SQLDATAWAREHOUSE, SQLITE, SQLSERVER, SYBASE, TERADATA, 
-- VERTICA, YUGABYTEDB
SELECT LANGUAGE.CD, LANGUAGE.DESCRIPTION
FROM LANGUAGE

-- BIGQUERY, H2
SELECT * EXCEPT (LANGUAGE.ID)
FROM LANGUAGE
user_5217990

As others are saying: SELECT * is a bad idea.

Some reasons:

  1. Get only what you need (anything more is a waste)
  2. Indexing (index what you need and you can get it more quickly. If you ask for a bunch of non-indexed columns, too, your query plans will suffer.
Michael_Haren_290
Related