SQL Antipatterns: Avoiding the Pitfalls of Database Programming/

...

/

Solution: Use Columns Unambiguously

Solution: Use Columns Unambiguously

Let’s discover how we can solve the antipattern using unambiguous queries.

We'll cover the following...

The sections that follow describe several ways we can resolve this antipattern and write unambiguous queries.

Query only functionally dependent columns

The most straightforward solution is to eliminate ambiguous columns from the query.

Press + to interact
SELECT product_id, MAX(date_reported) AS latest 
  FROM Bugs JOIN BugsProducts USING (bug_id) 
 GROUP BY product_id;

The query reveals the date of the latest bug per product, even though it doesn’t report the bug_id corresponding to that latest bug. Sometimes this is enough, and we shouldn’t overlook this simple solution.

Using a correlated subquery

A correlated subquery contains a reference to the outer query and produces different results for each outer query row. We can use this to find the latest bug per product by running a subquery to search for bugs with the same product and a greater date. ...