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
address columns from the
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
-- 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
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
Now you can use
ALTER TABLE statement to drop the column you wish to exclude from the
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;
comment column is removed from the temporary table, the
SELECT * query won’t return the
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.
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 database is a database generated by MySQL to store information about databases and tables that you’ve created in the server.
information_schema.COLUMNS table holds all information about the columns in your MySQL tables.
To exclude columns, you use the
GROUP_CONCAT() functions to generate the column names you wish to include in your
SELECT statement later.
In the following query, replace
<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>';
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 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
Because querying the
information_schema database demands more resources, I’d recommend you try the first two methods before using the
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