SQL makes selecting all fields in a table quite trivial via the SELECT * (SELECT ALL) clause. Unfortunately, as soon as you omit a column from the list, the SELECT ALL statement goes out the window. Writing out every every column name can quickly become tedious, especially if you happen to be dealing with tables that contain dozens of columns. What if we could select every column but one - selecting by exclusion rather than inclusion? It can be done. In fact there are a couple of ways to do it - one simple, the other, a bit less so. These will be the focus of today's blog.
Method 1: Using The INFORMATION_SCHEMA.COLUMNS table
The INFORMATION_SCHEMA provides access to database metadata, information about the MySQL server such as the name of a database or table, the data type of a column, or access privileges. More specifically, the COLUMNS table provides information about columns in tables, including column names.
The Sakila sample database's film table contains the highest number of columns at thirteen.
Here's how we would use the INFORMATION_SCHEMA.COLUMNS table to fetch all but the original_language_id column:
The GROUP_CONCAT function concatenates all of the column names into a single, comma-delimited string. We can then replace the field to omit with an empty string!
Executing the Query
One small hurdle to overcome is that a MySQL query cannot accept dynamic column names. The solution is to employ a Prepared Statement. Here's the code that sets the @sql variable, prepares the statement, and executes it:
SET @sql = CONCAT('SELECT ',
(SELECT REPLACE(GROUP_CONCAT(COLUMN_NAME), '<columns_to_omit>,', '')
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '<table>'
AND TABLE_SCHEMA = '<database>'),
' FROM <table>');
PREPARE stmt1 FROM @sql;
EXECUTE stmt1;
Inserting the column, table, and schema information into the query yields the results that we're after:
Method 2: Using Navicat
The main goal of database development and administration tools like Navicat is to increase productivity. As such, Navicat is designed to make your job as quick and easy as possible. To that end, the SQL Editor helps you to code faster thanks to Code Completion and customizable Code Snippets that offer suggestions for keywords and strip the repetition from coding. And if that wasn't enough, Navicat also provides a useful tool called Query Builder for building queries visually. It allows you to create and edit queries with only a cursory knowledge of SQL. While the Query Builder is marketed predominantly to more novice coders, those more proficient in SQL can still benefit from the Query Builder for certain tasks. One such tasks is that of choosing columns.
In the Query Builder, there is a checkbox next to the table name to select all of its columns. If we click on it, we can then simply uncheck the original_language_id field to remove it from the column list:
Clicking the OK button then closes the dialog and adds the SQL code to the editor:
Creating queries using the Query Builder offers a few advantages over writing code by hand:
- it minimizes typos
- it generates formatted SQL that's easy to read
Conclusion
In today's blog, we learned a couple of techniques to select every column in a table but one or two.
Interested in finidng out more about Navicat for MySQL? You can try it for 14 days completely free of charge for evaluation purposes!
