It is well known ( and has already been asked ) you should avoid using SELECT * in some cases in queries to the bank. But imagine that I have a table with 50 columns, and I want to select 40 of them.
Just selecting the fields would already leave the query gigantic, for 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 report that I want all the LESS fields some?
This not is possible with SQL Server or Oracle. I do not know if it is possible with other banks like MySql. If anyone knows, feel free to edit my answer.
The Oracle select specification can be found here: link
And SQL Server, in English: link
Some people may suggest gambiarras alternatives with code , which make a query in the schema of the table to get the names of all the columns except the ones you specified. This just makes the code more complex and can turn into a maintenance nightmare later.
In addition, for many people it is considered bad practice to get all columns except a specific group. The reason is that by specifying the name of each column you want, you ensure that your query will always have 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 all previous columns, except foo , but including the new bar column. Without changing your query, I changed the results format .
If you go ahead with this, people who are keeping up with your code will curse you until the end of time.
If you are going to use this data frequently, I suggest you create a view for them. And then you can still create "select's" from that view.
But the question is also interesting, such as the wildcard question (*) in select .
You want to make a flexible / scalable system. It works even with new columns. Hence you make a system that searches which columns exist (metadata) and excludes those that you are sure you will not need. Very well, you are creating a system that is concerned with data traffic (considering that the metadata is cached, of course) but not traffic (number of queries).
Only you pass this overhead to the application server, because as our friend Renan said, they did not create this in SQL (it would generate a lot of overhead for the DB, because although the logic is simple, it would need to be done in all types of consultation). Won on data, lost on performance. Of course, lost performance depends on the availability of your application. Systems that do not have 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? Hum, and worse, if its meaning asks that other columns of the listed (brought by the select) enter the exceptions? Wow, what if ....
Well, this problem has a solution, called parameterization. the user or a configuration file - or another system - define the incoming and non-incoming fields of the query.
Problems? Yes, parametrization is one of the most debated paradigms of programming: how to parameterize, what to parameterize, when, and so on. Yes, because this generates harder code to create and maintain even though it decreases it.
Parametrization raises the overhead too ( mostly from the programmer ).
But all this could be solved with the wildcard (*); because the system you are doing is simple. Or just a few columns.
Just to add 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 SAS , which has the DROP= option, where indicates which fields are ignored.
select *
from tabela(drop=campo200 campo5000);
I found a non-performative 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
select the Temporary table. I solved this problem using a logical form. In the field I do not want to group, it converts it into a random one. Here's 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