Unveiling the Secrets of SQL’s Most Misused Statement — “Select * from”

Photo by Fotis Fotopoulos on Unsplash

SQL, the versatile language that fuels the world of relational databases, is a double-edged sword. On one side, it offers us remarkable efficiency in handling data, while on the other, it lays a minefield of misconceptions and performance pitfalls. At the heart of this SQL conundrum lies the infamous SELECT * statement. Many database enthusiasts have at some point taken shelter in its apparent convenience, but few have dared to fathom its true implications.

We'll explore what happens in the backend of any SQL dialect when you execute SELECT * and provide a clear path to better and more efficient SQL querying

Understanding SELECT

• The SELECT * statement is a wildcard that instructs the database to return all columns from the specified table.
• It’s a simple way to retrieve all data, especially when you’re exploring a table’s structure.

Implications in the Backend

Performance Overhead

• The database must retrieve all columns, which can lead to a significant performance overhead.
• If the table contains many columns or millions of rows, this can result in slower query execution.

Network Traffic

• More data is transmitted from the database to your application, increasing network traffic.
• This can be particularly problematic in distributed or cloud-based database setups.

Maintainability Challenges

• When using SELECT *, your code might rely on the specific order of columns in the result set.
• If the table schema changes (e.g., column order, new columns), it can break your code.

Alternatives to SELECT

• It’s usually better to specify the exact columns you need in your query using SELECT column1, column2, ....
• This approach offers better performance, reduces network traffic, and enhances query maintainability.

Examples

Let’s consider a couple of examples to illustrate the implications of using SELECT *.

Example 1: Performance Overhead

Lets assume “orders” table has 100+ columns

-- Query 1: Using SELECT *
SELECT * FROM orders WHERE order_date > '2023-01-01';

-- Query 2: Specifying Columns
SELECT order_id, customer_id, order_date FROM orders 
WHERE order_date > '2023-01-01';

In Query 1, using SELECT * retrieves all columns, which can be inefficient if the "orders" table has many columns.

Query 2, which specifies only the necessary columns, can be more efficient.

Example 2: Maintainability Challenges

-- Query 1: SELECT *
SELECT * FROM employees;

-- Query 2: SELECT column1, column2, ...
SELECT first_name, last_name, email FROM employees;

In Query 1, if the table structure changes, it can break the code that relies on the specific order of columns.

Query 2 is less prone to such issues because it explicitly selects the required columns.

Now , you know why you have to keep select * as a last option.

To conclude, while SELECT * is a convenient way to retrieve all columns, it comes with performance and maintainability implications. To write efficient and maintainable SQL queries, it's best to explicitly list the columns you need. This practice ensures your queries run smoothly and are less affected by changes in the database schema.

Happy Selecting !