How to exclude a column using select * except column?

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

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

• DuckDB Ref.
• BigQuery Ref
• DataBricks Ref

Recommended Courses

1. SQL for Data Science – Level 1
2. SQL for Data Science – Level 2
3. SQL for Data Science – Level 3

Recommended Tutorial

1. Introduction to SQL
2. SQL Window Functons – Made Simple and Easy
3. SQL Subquery

More SQL Questions

1. How to concatenate multiple rows into one field in MySQL?
2. What is the difference between UNION and UNION ALL in SQL?
3. How to find duplicate values in SQL?
4. How to select first row in each GROUP BY group?