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

Synopsis: Implicit Columns

Explore how to resolve SQL query problems caused by implicit columns sharing names in joined tables. Understand the importance of using explicit column aliases to prevent NULL values and maintain clarity in query results, especially when combining data from multiple tables or using wildcards.

We'll cover the following...

A PHP programmer once asked me for help troubleshooting the confusing result of a seemingly straightforward SQL query against their library database:

MySQL
SELECT * FROM Books b JOIN Authors a ON (b.author_id = a.author_id);

This query returned all book titles as NULL. Even stranger, when the PHP programmer ran a different query without joining the Authors, the result included the real book titles.

MySQL
SELECT * FROM Books;

I helped the programmer find the cause of their trouble: the PHP database extension they were using returned each row resulting from the SQL query as an associative array. For example, the programmer could access the Books.isbn column as $row["isbn"] using this extension. In their tables, both Books and Authors had a column called title (the latter was for titles like “Dr.” or “Rev.”). Now, we know that a single-result array element ...