SQL - Return all but one row value

Question

When querying a database, how can you return every field value of a row,, except one (or more), ^while using the * operator .

Example - A property has 50 fields describing it.
What if I wanted the query to return every field except the 39th ^column, or the 10th and the 23rd ^columns?
(^edited to remove ambiguity)

Surely, it is not necessary to type in every one you want just to exclude the ones you don't.

Answer 1

Maybe use the "NOT" statement? Just exclude the one you don't need by using id or some other field.

Example:
SELECT * FROM table WHERE NOT id=39;

It may slow, but at the same time why would you need to exclude a specific row, maybe we can find a better way to achieve your goal.

Answer 2

Aymane Boukrouh & Sandra Meyer, thankyou.

I don't have a specific need, it was just a curiosity, right now.

Thanks for the ideas.

Answer 3

Aymane Boukrouh and Sandra Meyer Your suggestions apply to filtering rows from the resultset based on values in a named column rather than excluding said columns altogether from all rows.

Mark McClan If I understand your question, you want to know if there is a way to exclude columns from the resultset without having to specify all the columns you want to keep.

If so... you may need to select * into a temp table, drop the columns to exclude, then select * from the new temp table. This won't be performant, but it might be an option depending on the use case.

Otherwise, I would use the SQL generate tools for the respective database to generate that list of columns, then remove the columns from the select statement.

Answer 4

David Carroll , yes that is exactly the type of result I was wondering was possible.

At least that is an easy workaround, despite not quite being what I would have hoped.

Never ceases to amaze me how often something that seems so simple, ends up being not.

Answer 5

Or NOT IN ( < list of excludes> )

Answer 6

The need will come up... Sooner or later 😉

Answer 7

David Carroll I didn't even notice, I thought he was asking for rows and just started typing my answer ^^".

Answer 8

Mark McClan Incidentally, I happened to stumble upon some interesting discussions and similar variations of my suggestions - which you may find interesting:

https://stackoverflow.com/questions/729197/sql-exclude-a-column-using-select-except-columna-from-tablea

https://stackoverflow.com/questions/29095281/how-to-select-all-the-columns-of-a-table-except-one-column/29114381

I do agree that it would be nice to specify a list of columns to exclude in conjunction with the * character to include all columns. But it's been so long ago that I swallowed that pill, I haven't thought about this for SQL in decades. 😉

That said, MongoDB's Query API does support field exclusion within projection blocks that accomplish this if you're ever working with this specific document database.

It looks something like this:

db.collection.find({}, {
fieldA: 0,
fieldB: 0
})

Which essentially returns all results and all fields, except those set with the value 0.

This was a super simple example to demonstrate the concept.

Answer 9

Btw. 50 fields in a relational DB is quite a lot, actually too much. Having less overhead of fields within a table is also less effort when noting all required fields in the select statement.

Answer 10

Hi

Answer 11

Can you please help me