How to Exclude a Column Using SELECT EXCEPT in SQL

1. Overview

In SQL, we commonly use the SELECT * statement to retrieve all columns from the table. However, in certain scenarios, we sometimes want to display all columns except one or even a few. Unfortunately, standard SQL doesn’t support the direct  SELECT * EXCEPT syntax in most database management systems. Instead, the implementations vary depending on the database we’re using.

In this tutorial, we’ll explore various techniques for excluding specific columns in SQL Server, PostgreSQL, and MySQL.

To demonstrate, we’ll use the Baeldung University database schema as the working example.

2. Listing Specific Columns in SQL

Generally, we can always explicitly list the required columns instead of using a SELECT * query. This approach works in every database management system and is ideal for small tables that rarely change their schema.

To start with, we can use the following syntax to display only the selected columns:

SELECT column_1, column_2, column 3, column_4...
FROM table_name;

Above, we specify the required columns and table_name.

While this method is straightforward, it requires that we know and enumerate all required columns. Especially with big tables, this can be very tedious.

3. SQL Server sys.columns

In SQL Server, we can use the sys.columns view system to dynamically build the query that excludes specific columns. In particular, this view contains metadata about each column, including its name, data type, and position.

To illustrate, let’s exclude the gpa column in the Student table. For this purpose, we can execute a specific query:

DECLARE @sql NVARCHAR(MAX);
SELECT @sql =
    'SELECT ' + STRING_AGG(QUOTENAME(name), ', ')
    + ' FROM dbo.student'
FROM sys.columns
WHERE object_id = OBJECT_ID('dbo.student')
  AND name <> 'gpa';
EXEC sp_executesql @sql;

The important part here, and the one we can build upon, is AND name <> ‘gpa’. Adding AND statements with respective names enables the exclusion of multiple columns.

Consequently, we see the respective output:

In this output, all columns are listed except the gpa.

4. PostgreSQL string_agg

In PostgreSQL, we can exclude a column using the INFORMATION_SCHEMA view and concatenating the column list with string_agg. This approach uses the system catalog view to build flexible queries without manually listing every column.

For instance, let’s exclude the code column from the department table:

university=# DO $$
university$# DECLARE
university$#     sql_query text;
university$# BEGIN
university$#     SELECT 'SELECT ' ||
university$#            string_agg(quote_ident(column_name), ', ' ORDER BY ordinal_position) ||
university$#            ' FROM Department'
university$#     INTO sql_query
university$#     FROM information_schema.columns
university$#     WHERE table_schema = 'public'
university$#       AND table_name   = 'department'
university$#       AND column_name <> 'code';
university$#
university$#     RAISE NOTICE 'Generated SQL: %', sql_query;
university$#     EXECUTE sql_query;
university$# END $$;
NOTICE:  Generated SQL: SELECT id, name FROM department
DO

In return, we see the generated query SELECT id, name FROM department.

Now, let’s run the above-generated query to see the results:

university=# SELECT id, name FROM department;
 id |              name
----+--------------------------------
  1 | Computer Science
  2 | Electronics and Communications
  3 | Mechanical Engineering
  4 | Civil Engineering
  5 | Mathematics
(5 rows)

Thus, the output only displays the id and name columns.

5. MySQL GROUP_CONCAT

In MySQL, we can achieve a functionality similar to that in PostgreSQL using the GROUP_CONCAT function, along with the INFORMATION_SCHEMA view. This approach enables the dynamic elimination of specific columns in the database.

For example, to eliminate the textbook column from the course table, we can use the COLUMN_NAME field:

SET @sql = NULL;
SELECT GROUP_CONCAT(CONCAT('`', COLUMN_NAME, '`') ORDER BY ORDINAL_POSITION SEPARATOR ', ')
INTO @sql
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'university'
  AND TABLE_NAME = 'course'
  AND COLUMN_NAME <> 'textbook';
SET @sql = CONCAT('SELECT ', @sql, ' FROM course');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Below, we can see all columns from the course table except textbook:

While the approach uses similar concepts, the syntax isn’t interchangeable with that of PostgreSQL.

6. Conclusion

In this article, we learned various techniques for excluding a particular column in SQL Server, PostgreSQL, and MySQL.

First, we discussed explicitly listing the required columns as a universal and reliable approach. Then, we saw how to dynamically generate column lists using sys.columns in SQL Server, INFORMATION_SCHEMA with string_agg in PostgreSQL, and GROUP_CONCAT in MySQL to exclude unwanted columns.