Why Select * is a trap

5 reasons to avoid it.

Select * is the Hello World of SQL. Every course starts with:

SELECT * FROM Table1

It is easy to understand, convenient, and useful in some (rare) cases.

BUT when you are not a beginner anymore, you should avoid Select *.

Here are a few reasons why:

Reliability

Change a column and the whole product will break. Under the hood of working systems, there are fragile data backends.

Many applications will rely on specific column orders. Adding or removing columns in the table may kill your system. Explicitly listing columns ensures that the query remains consistent even if the schema changes.

Performance

Performance is everything in production. Select * will return all the columns from a database and that’s overkilling. The difference is negligible when we talk about small data, but it’s easy to reach a territory where seconds/minutes matter.

By selecting specific columns, we can avoid computations and aggregations that are not needed.

Before running a query always ask: Do we need this column? Less is more in this case.

Indexing

Indexes are like a map of your data—they make it faster to find what you need.

When you use SELECT *, you lose your map. SQL has to retrieve every column in the table, even if an index covers only some of them.

Think of it like looking for a chapter in a book: an index makes it fast to jump right to what you need.

SELECT * is like flipping through the whole book when you just need one chapter.

Readability

Select * is a black box. Until you don’t see the results or info about the db, you will not understand what’s happening. That is like -10 Readability & Understandability points.

Naming the columns explicitly makes communication way easier. Look at these:

SELECT * FROM Employees 

WHERE Department = 'Sales';

You need to run the above code to get an idea about the result. But for the next one, reading the code itself will tell a lot.

SELECT EmployeeID, FirstName, LastName, JobTitle 

FROM Employees 

WHERE Department = 'Sales';

As the author of the code, you may know the ins and outs of the db, but a newbie will understand more from column names.

Using code in other code

In SQL, we may use existing queries as part of another query.

For example when we want to union two queries.

For a UNION operation, the queries must have the same number of columns in the same order and with compatible data types. So many requirements where it could go wrong with a Select *.

SELECT * FROM Customers WHERE Country = 'USA'

UNION

SELECT * FROM Clients WHERE Country = 'USA';

But this will surely work:

SELECT CustomerID, Name, Country FROM Customers WHERE Country = 'USA'

UNION

SELECT ClientID AS CustomerID, ClientName AS Name, Country FROM Clients WHERE Country = 'USA';

When to use it then?

1, The star in SQL is not useless. For example, you can also use it to refer to rows:

SELECT COUNT(*) FROM Table1

In this case, we are not listing all the columns but count all the rows.

2, It’s good to use Select * to explore data. When you have the understanding, you can change to columns.

In this case, don’t forget to use the Limit N.

SELECT * FROM Table1
LIMIT 100;

This way, you can get a quick look at the data without overwhelming the system.

For a more advanced analysis take a look at this article: Reasons why SELECT * is bad for SQL performance | Tanel Poder Consulting