How to SELECT all columns except one in MySQL

A MySQL SELECT statement is used to retrieve data from a MySQL table.

To exclude certain column(s) from the SELECT statement result, you can omit the column/ field name from the query.

For example, suppose you have a table named Students with the following columns:

+---------+
| Field   |
+---------+
| id      |
| name    |
| age     |
| address |
| phone   |
| email   |
| comment |
+---------+

To retrieve certain columns, you can name them in the SELECT statement as shown below:

SELECT name, age, address FROM Students;

The above query will retrieve the name, age, and address columns from the Students table.

But sometimes, you may want to retrieve all columns from a table except one or two.

One way of doing it is to name all the columns in the SELECT statement.

-- exclude comment column
SELECT id, name, age, address, phone, email FROM Students;

But if you have more than 10 columns in a single table, then specifying all column names but one is too tedious.

This tutorial will help you learn how to exclude column(s) easier in your SELECT statement. There are at least 3 ways to do so:

• Using temporary table
• Using a view
• Using the information_schema.COLUMNS table

Let’s start with using a temporary table first.

Exclude specific columns using a temporary table

A MySQL temporary table is a table that’s created only for you during the current MySQL session.

Once you create this table, then you can use it as many times as you want, as long as you’re in the same session (haven’t disconnected from MySQL server)

To create a temporary table from your existing table, use the CREATE TEMPORARY TABLE statement as shown below:

CREATE TEMPORARY TABLE TempStudents AS SELECT * FROM Students;

The data from the temporary table will be copied from the existing Students table.

Now you can use ALTER TABLE statement to drop the column you wish to exclude from the SELECT statement.

The following statements will drop the comment column from the TempStudents table and then run a SELECT * statement for the temporary table:

ALTER TABLE TempStudents DROP COLUMN comment;
SELECT * FROM TempStudents; 

With the comment column is removed from the temporary table, the SELECT * query won’t return the comment column.

To exclude multiple columns, you need to drop multiple columns from the temporary table:

ALTER TABLE TempStudents 
  DROP COLUMN phone,
  DROP COLUMN email;

By using a temporary table, you can still have all your columns unchanged. What’s more, the table is automatically removed once you end the session.

If you need a record that stays after your current session ends, then you can use a MySQL view.

Exclude specific columns using a view

A MySQL view is a stored query designed to help you retrieve custom data from existing tables in your database.

Once a view is created, it will be available until you remove the view.

To create a view, you can use the CREATE VIEW statement as follows:

CREATE VIEW StudentsView AS
  SELECT id, name, age, address, phone, email
  FROM Students;

When you create the view, you need to specify the columns you wish to include in the view one by one.

With the comment column exBut after the view is created, you can do a SELECT * statement as follows:

SELECT * FROM StudentsView;

-- +----+--------+------+---------+-------+-------+
-- | id | name   | age  | address | phone | email |
-- +----+--------+------+---------+-------+-------+
-- |  1 | Jack   |   19 | NULL    | NULL  | NULL  |
-- +----+--------+------+---------+-------+-------+

You can drop the view using a DROP VIEW statement as follows:

DROP VIEW IF EXISTS StudentsView;

And that’s how you exclude MySQL table columns using a view.

Exclude specific columns using the information_schema.COLUMNS table

The information_schema database is a database generated by MySQL to store information about databases and tables that you’ve created in the server.

The information_schema.COLUMNS table holds all information about the columns in your MySQL tables.

To exclude columns, you use the REPLACE() and GROUP_CONCAT() functions to generate the column names you wish to include in your SELECT statement later.

In the following query, replace <column_name>, <table_name> and <database_name> with your actual MySQL names:

SELECT REPLACE(GROUP_CONCAT(COLUMN_NAME), '<column_name>,', '') 
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '<table_name>' 
AND TABLE_SCHEMA = '<database_name>';

Because my Students table is created in the school_db database, the query for the table is as shown below:

SELECT REPLACE(GROUP_CONCAT(COLUMN_NAME), 'comment,', '') 
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Students' 
AND TABLE_SCHEMA = 'school_db';

The query result will be as follows:

+----------------------------------------------------+
| REPLACE(GROUP_CONCAT(COLUMN_NAME), 'comment,', '') |
+----------------------------------------------------+
| address,age,email,id,name,phone                    |
+----------------------------------------------------+

You can use the result to create your SELECT statement:

SELECT address,age,email,id,name,phone FROM Students;

And that’s how you use the information_schema.COLUMNS table to exclude a column from your SELECT statement.

Because querying the information_schema database demands more resources, I’d recommend you try the first two methods before using the information_schema.COLUMNS table.

This method also doesn’t allow you to exclude more than one column because of the REPLACE() function limitation.

To exclude multiple columns, you need to call the REPLACE() function for each column, which is cumbersome 😕

You need to try and use a temporary table or a view before using the information_schema.COLUMNS table.