I think we can all agree that there is no more common statement in SQL than the SELECT statement. It is like the “Hello World” of SQL being the one thing that we learn and that introduces us to SQL.
At the basics, we use the SELECT statement to retrieve the data from a database table. This includes specifying which columns we wish to include in the result.
Despite this functionality, we may come across instances where we want to select all the columns and rows from a given table except a few. Although it may not be common as you might think, you are bound to come across such an instance in your database operations.
In this tutorial, we will learn how we can select all rows and columns except the specific ones by utilizing various SQL techniques.
NOTE: For demonstration purposes, we use MySQL version 8 with the Sakila sample database. You can download the sample database on the official website and follow the provided steps to import it.
You can also use any other dataset that you deem applicable.
Example 1: Select All Columns Except One
One of the common tasks of “select except” is where you need to select all the columns of a table except one.
Let us take the Sakila sample database. Suppose we wish to retrieve all the columns from the “film” table except the “description” column.
We can do this using the query as follows:
film_id,
title,
release_year,
rental_rate,
length,
special_features
FROM
film;
Yes, that’s it. This method involves specifying all the columns in the table except the “description” column.
Example 2: Except Multiple Ones
The same case applies when you need to exclude multiple columns. You can exclude them from the SELECT query.
For example, let us exclude both description and “special_features” from the “film” table. We can run the following query:
film_id,
title,
release_year,
rental_rate,
length
FROM
film;
This should return all the columns from the “film” table except the “description” and “special_features” columns.
Example 3: Exclude a Row
In other cases, you may need to exclude a row. In such a case, we can take advantage of SQL conditional filtering using the WHERE clause.
For example, suppose we wish to exclude the records where the “rental_rate” is greater than 4.0.
We can use a query as follows:
film_id,
title,
release_year,
rental_rate,
length
FROM
film
WHERE
rental_rate <= 4.0;
In the given example, we use the WHERE clause to specify the condition that filters out the rows with a “rental_rate” greater than 4.0.
Example 4: Select * Except
There is also a technique that we can use in conjunction with the SELECT * clause to exclude one specific column.
This involves using the NULL AS clause followed by the name of the column that we wish to exclude from the result set.
Take the following example:
*,
NULL AS description
FROM
film;
In this case, we include a NULL value as an alias for the description column. This should include it from the result set.
NOTE: This technique is not widely adopted by a large variety of SQL databases.
Conclusion
In this tutorial, we learned all the methods that we can use to include and exclude various rows and columns from a given database table.
