How do SELECT all fields except a few?

Question

It is known (and has been asked) which should be avoided SELECT * in some cases in bank queries. But let’s imagine that I have a table with 50 columns, and I want to select 40 of them.

Just the fact of selecting the fields would already leave the gigantic query, example:

SELECT campo_1, campo_2, campo_3, campo_4, campo_5, campo_6, campo_7, ....

My question is, is there any simpler way to inform that I want all fields MINUS some?

Answer 1

That nay is possible with SQL Server or Oracle. I don’t know if it’s possible with other banks, like Mysql. If anyone knows, feel free to edit my answer.

The Oracle select specification is found here: http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_10002.htm

And SQL Server, in English: http://msdn.microsoft.com/pt-br/library/ms189499.aspx

Some people might suggest gambiarras coded alternatives, that make a consultation on schema from the table to get the names of all columns except the ones you specified. This only makes the code more complex and can become a maintenance nightmare later.

Also, for many people it is considered bad practice to get all columns except for a specific group. The reason is that by specifying the name of each column you want, you ensure that your query will bring always the same columns.

Imagine that you make a query that returns all the columns that exist today, except foo. If tomorrow I create the column bar, your query will bring up all previous columns except foo, but including the new column bar. Without changing your query, I changed the format of the results.

If you go through with this, the people who are going to keep your code will curse you until the end of time.

Answer 2

If you are going to use this data frequently, I advise you to create a view for it. And then you can still create "select’s" from this view.

http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_8004.htm#SQLRF54694

Answer 3

But the question is also interesting, such as the wildcard(*) issue in select.

You want to make a flexible/scalable system. That works even with new columns. Then you make a system that searches for which columns exist (metadata) and deletes the ones you’re sure you won’t need. Very well, you are creating a system concerned with data traffic (considering that it is cached metadata, of course) but not traffic (amount of queries).

Only that you pass this overhead to the application server, because as our friend Renan said, they did not create it in SQL (it would generate a lot of overhead for the BD, because although the logic is simple, it would need to be done in all types of query). Won data, lost performance. Of course, the lost performance depends on the availability of your application. Systems that have no problems with multiple/concurrent access have no problem with this.

Now, another note. What if the column that was added semantically has to go to the list of exceptions? Um, and even worse, if its meaning asks other listed columns (brought yes by select) to enter the exceptions? Worse: and if ....

All right, this problem has a solution, called parameterization. the user or a configuration file - or yet another system - define both incoming and non-incoming query fields.

Problems? Yes, parameterization is one of the most debated paradigms of programming: how to parameterize, what to parameterize, when, and so on. Yes, because that’s why generates code harder to create and maintain despite diminishing it.

Parameterization increases overhead as well (mainly from the programmer).

But all this could be solved with wildcard (*); because the system you are doing is simple. Or just a few columns.

Answer 4

Just to add some information. As far as I know, SQL does not allow this (yet?), but some languages/tools have database options that can play this role, such as the SAS, which has the option DROP=, where it indicates which fields will be ignored.

select * 
  from tabela(drop=campo200 campo5000); 

Answer 5

I found a non-performation solution, but it works.

SELECT * INTO #TEMP
FROM NOME_SUATABELA


ALTER TABLE #TEMP
DROP COLUMN COLUMN_1, COLUMN_2...


SELECT * FROM #TEMP
DROP TABLE #TEMP

• Enter any result you need in a table Temporary.
• Remove columns you will not need in Output.
• All ready, now just run a command select the Temporary table.

Answer 6

I solved this issue with this Select here using IN in Mysql SQL

In case I have several system usage profiles but when registering a new uusário (varying user profile I display for selection only those I want to appear on the screen

SELECT SQL_CACHE IdPerfil, Nome_Perfil 
FROM usuarios_perfil 
WHERE Nome_Perfil IN ('Administrador','Cliente') 
ORDER BY Nome_Perfil 
DESC

I don’t know if that helps you

Answer 7

I solved this problem using a logical way. In the field I don’t want to group, convert it to random. See an example:

SELECT codigo,locador,mes,descricao, iIf(recibo = 999999999999999, rand()*516241235432165, recibo)  As recibo, Sum(valor)  
FROM CONTA
GROUP BY codigo,locador,mes,descricao, recibo
ORDER BY CODIGO