Search⌘ K
AI Features
Home
Courses
SQL Antipatterns: Avoiding the Pitfalls of Database Programming

Antipattern: A Shortcut That Gets You Lost

Understand the risks of using implicit columns and wildcards in SQL queries. Learn to explicitly name columns to avoid errors caused by schema changes and improve query efficiency and maintainability.

We'll cover the following...

Although using wildcards and unnamed columns satisfies the goal of less typing, this habit creates several hazards.

Breaking refactoring

Let’s suppose we need to add a column to the Bugs table, such as date_due, for scheduling purposes.

MySQL
ALTER TABLE Bugs ADD COLUMN date_due DATE;

But our INSERT statement results in an error because we listed eleven values instead of the twelve the table now expects.

MySQL
INSERT INTO Bugs 
     VALUES (DEFAULT, CURDATE(), 'New bug', 'Test T987 fails...', 
            NULL, 123, NULL, NULL, DEFAULT, 'Medium', NULL);

In an INSERT statement that uses implicit columns, we must give values for all the columns in the same order that the columns are defined in the table. If the ...