CODEWITHSUNDEEP
mysqlsql
Kevin Lee
Kevin Lee

Reputation: 1089

Please help me understand this query in SQL

I'm reading SQL Antipatterns and found this query really hard to understand:

SELECT
    bp1.product_id, b1.date_reported AS latest, b1.bug_id 
FROM
    Bugs b1
    JOIN 
    BugsProducts bp1 ON (b1.bug_id = bp1.bug_id) 
    LEFT OUTER JOIN
    (
      Bugs AS b2
      JOIN 
      BugsProducts AS bp2 ON b2.bug_id = bp2.bug_id
    )
     ON bp1.product_id = bp2.product_id AND 
         (b1.date_reported < b2.date_reported OR b1.date_reported = b2.date_reported
          AND
          b1.bug_id < b2.bug_id
         )
WHERE
    b2.bug_id IS NULL;

Please explain this to me SQL experts.. Thank you!

Upvotes: 1

Views: 116

Answers (2)

Johan
Johan

Reputation: 76535

It appears the query in the question is the good pattern. I find it confusing and would rewrite it as:

SELECT
    bp1.product_id, b1.date_reported AS latest, b1.bug_id 
FROM Bugs b1
INNER JOIN BugsProducts bp1 ON (b1.bug_id = bp1.bug_id) 
WHERE NOT EXISTS 
(
  SELECT * FROM Bugs AS b2
  INNER JOIN BugsProducts AS bp2 ON (b2.bug_id = bp2.bug_id)
  WHERE (bp1.product_id = bp2.product_id) 
     AND 
     (
        (b1.date_reported < b2.date_reported) OR 
        (b1.date_reported = b2.date_reported AND b1.bug_id < b2.bug_id)
     )

I strongly suspect that this code has the same performance (on MySQL anyway) as the query in the question.

Just in case people are wondering:

http://dev.mysql.com/doc/refman/5.5/en/exists-and-not-exists-subqueries.html

Traditionally, an EXISTS subquery starts with SELECT *, but it could begin with SELECT 5 or SELECT column1 or anything at all. MySQL ignores the SELECT list in such a subquery, so it makes no difference

What was the antipattern associated with this 'good' pattern anyway, would really like to know.

Upvotes: 0

gbn
gbn

Reputation: 432200

Show me bug/products where there are no later bugs, based on date/id

You can simplify the code to pseudo code

SELECT
    pair1 data
FROM
    pair1
    LEFT OUTER JOIN
    pair2
     ON same product, 1st date <= 2nd date, 1st internal id <= 2nd internal id
WHERE
    no such pair2

Edit, FYI: the author is SO user Bill Karwin https://stackoverflow.com/users/20860

Upvotes: 2

Related Questions