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

Solution: Name Columns Explicitly

Explore the benefits of explicitly naming columns in SQL queries and INSERT statements. Understand how this practice prevents errors, simplifies troubleshooting, and enhances query efficiency by avoiding wildcards. Learn how naming columns supports scalability and clarity in database programming.

We'll cover the following...

The best approach is to spell out the names of all the columns we need instead of relying on wildcards or implicit column lists.

MySQL
SELECT bug_id, date_reported, summary, description, resolution, 
       reported_by, assigned_to, verified_by, status, priority, hours 
  FROM Bugs;

Then, we follow this step by inserting a new record in the Accounts table.

MySQL
INSERT INTO Accounts (account_id, account_name, first_name, last_name, email, 
                     password_hash, portrait_image, hourly_rate) 
     VALUES (4, 'bkarwin', 'Bill', 'Karwin', 'bill@example.com',
            SHA2('xyzzy',256), NULL, 49.95);

All this typing seems burdensome, but it’s ...