Select all columns except one of a MySQL table

This article will look into how to select * from a table except for one column. We will be discussing two ways to get all the columns of a table except one.

Table of Contents:-

• Select * except one column from a MySQL table using temporary tables
• Select * except one column from a MySQL table using views

Let us get started by making the sample data. We will be creating a table sale_details followed by inserting a few rows into it.

# create table sale_details
CREATE TABLE sale_details (
    id INT ,
    sale_person_id VARCHAR(255) ,
    sale_person_name VARCHAR(255),
    no_products_sold INT,
    sales_department VARCHAR(255)
);
#inserting into sale_details
INSERT INTO sale_details (id, sale_person_id, sale_person_name, no_products_sold, sales_department) 
 VALUES(1,"sd1","Henry",2000,"Kitchen Essentials"),
 (2,"sd1","Henry",5000,"Apparels"),
 (3,"sd1","Henry",40,"Medicines"),
 (4,"sd2","Richa",3000,"Furniture"),
 (5,"sd2","Richa",500,"Bathroom Essentials"),
 (6,"sd2","Richa",50,"Shoes"),
 (7,"sd3","Ved",100,"Kitchen Essentials"),
 (8,"sd3","Ved",150,"Apparels"),
 (9,"sd3","Ved",1000,"Medicines"),
 (10,"sd4","George",600,"Bathing Essentials"),
 (11,"sd4","George",1200,"Shoes"),
 (12,"sd4","George",200,"Medicines"),
 (13,"sd5","Alexendra",487, NULL),
 (14,"sd5","Alexendra",570,"Furniture"),
 (15,"sd5","Alexendra",510,"Medicines"),
 (16,"sd6","Gustav",3000,"Kitchen Essentials"),
 (17,"sd6","Gustav",100,"Shoes"),
 (18,"sd6","Gustav",150,"Furniture");

To view the snapshot of the sale_details table, execute:

SELECT * FROM sale_details;

Output:-

Select * except one column from a MySQL table using temporary tables

Temporary tables are the tables in MySQL which are created within a current session. We will be using the temporary table in this section. The steps would be:

• Create a temporary table from the original table.
• Drop the not required column from the table.
• Do a select * on the temporary table.

READ MORE : Create Temporary Table in MySQL

Example: Select * from all the columns of the sale_details table except sale_person_name.

Observe the below code for the solution.

CREATE TEMPORARY TABLE temp_sale_details AS SELECT * FROM sale_details;
ALTER TABLE temp_sale_details DROP COLUMN sale_person_name;
SELECT * FROM temp_sale_details; 

Output:-

The output in image_2 shows that the column sale_person_name is not present.

Limitations:-

• A temporary table will be available only in the current session.
• One cannot refer to a temporary table in the same query more than once.

Select * except one column from a MySQL table using views

A view is a virtual table created from a table or a query associated with many tables. We will be using the view in this section. The steps would be:

• Create a view from the original table with all columns except the non-required column.
• Do a select * on the view.

READ MORE : What are views in MySQL

Example: Select * from all the columns of the sale_details table except sale_person_name.

Observe the below code for the solution.

CREATE OR REPLACE VIEW sale_details_v AS
    SELECT 
        id, sale_person_id, no_products_sold, sales_department
    FROM
        sale_details;
SELECT * FROM sale_details_v;

Output:-

The output in image_3 shows that the column sale_person_name is not present.

READ MORE:

• What are triggers in MySQL
• What are views in MySQL
• What are indexes in MySQL
• MySQL Select last N rows
• MySQL select first row in each group

We hope this article helped you with MySQL queries to select all columns of a table. Good Luck!!!