Problem
You have a database table, how to select all columns except specific columns mentioned by the user?
Example Syntax:
SELECT * [except columnA]
FROM tabA;
Input
| id | first_name | last_name | salary | |
|---|---|---|---|---|
| 1 | John | Doe | [email protected] | 50000 |
| 2 | Jane | Smith | [email protected] | 60000 |
| 3 | Bob | Brown | [email protected] | 55000 |
This is not possbile in native SQL databases.
MySQL doesn’t natively support the SELECT * EXCEPT(column_name) syntax. To achieve the desired result, you need to manually specify the columns you want to include in your SELECT statement.
However, it is possible to do certain inefficient workarounds like this:e:5969d8a384b6b52f4ccf6acae27d8b7e)
Workaround Solution:
This solution is inefficient because it creates copies of data in new table and involves multiple steps. However addresses the problem of not having to specify all the column names in the table.
Using TempTables
/* Get the data into a temp table */
SELECT * INTO #TempTable
FROM YourTab
le
/* Drop the columns that are not needed */
ALTER TABLE #TempTable
DROP COLUMN ColumnTo
Drop
/* Get results and drop temp table */
SELECT * FROM #TempTable
DROP TABLE #TempTable1;
Modern Databases solution:
Modern databases such as Databricks and BigQuery supports the following syntax:
SELECT * EXCEPT(ColumnNameX, [ColumnNameY, ...])
FROM TableA;
DuckDB supports the following: Use EXCLUDE instead of EXCEPT
SELECT * EXCLUDE(ColumnNameX, [ColumnNameY, ...])
FROM TableA
