subqueries vs joins 2

[svrobin]

I am trying to understand the difference between subqueries and joins and what the advantages and disadvantages are of using one over the other. Does anyone when you should use subqueries as oppose to using joins and vice versa?

 

[r937]

it really comes down to understandability

presumably they will perform the same (and even if they don't, this isn't really within the aegis of ANSI SQL)

for example, compare this --

SELECT f.id
     , f.file_title_id
     , f.url
     , f.version_number
  FROM tblFiles f
INNER 
  JOIN (
       SELECT file_title_id
            , max(version_number) as max_version
         FROM tblFiles 
       GROUP
           BY file_title_id
       ) as m
    ON m.file_title_id = f.file_title_id
   AND m.max_version   = f.version_number

with this --

SELECT f.id
     , f.file_title_id
     , f.url
     , f.version_number
  FROM tblFiles f
 WHERE f.version_number =
       ( SELECT max(version_number)
           FROM tblFiles 
          WHERE file_title_id = f.file_title_id )

can you tell right away what each of these is doing? which one do you prefer? are you sure they're doing the same thing?

r937.com | rudy.ca

 

[rac2]

A correlated sub-query might be a different kettle of fish.

SELECT CustomerID
FROM Customers a
WHERE EXISTS (
    SELECT * 
    FROM Orders 
    WHERE Fulfilled = 0
      AND CustomerID = a.CustomerID
   )

If customers can have multiple unfulfilled orders and you only need one row per customer then this JOIN wont work.

SELECT a.CustomerID
FROM Customers a
JOIN Orders o ON o.CustomerID = a.CustomerID
             AND Fulfilled = 0

Although you could force it with DISTINCT

SELECT DISTINCT a.CustomerID
FROM Customers a
JOIN Orders o ON o.CustomerID = a.CustomerID
             AND Fulfilled = 0

And what about "finding the one before?"

SELECT a.CustomerID, 
       a.orderID AS LatestOrderID, 
       a.orderdate AS LatestOrderDate,
       b.orderID AS PreviousOrderID, 
       b.orderdate AS PreviousOrderDate
FROM Orders a
JOIN Orders b ON b.CustomerID = a.CustomerID
   AND b.orderdate = (
       SELECT MAX(orderdate)
       FROM Orders
       WHERE CustomerID = a.CustomerID
         AND orderdate < a.orderdate
      )

You might think that would be equivalent to the JOIN

SELECT a.CustomerID, 
       a.orderID AS LatestOrderID, 
       a.orderdate AS LatestOrderDate,
       b.orderID AS PreviousOrderID, 
       b.orderdate AS PreviousOrderDate
FROM (
      SELECT CustomerID, 
             MAX(orderID) AS orderID,
             MAX(orderdate) AS orderdate
      FROM Orders
      GROUP BY CustomerID
     ) a
JOIN Orders b ON b.CustomerID = a.CustomerID
             AND b.orderdate  < a.orderdate

It would be equivalent unless the orderID is not monotonic with the orderdate. That is the row with the maximum orderid is always the same row with the maximum orderdate. Typically and usually I think you will find this to be true, but it depends on how the application creates the orderid.

And what if you need the LatestSalesPersonID instead of the latest orderID. That wont work at all. The correlated subquery must be used.

SELECT a.CustomerID, 
       a.orderID AS LatestOrderID, 
       a.orderdate AS LatestOrderDate,

       a.salespersonID AS LastestSalesPersonID,

       b.orderID AS PreviousOrderID, 
       b.orderdate AS PreviousOrderDate
FROM Orders a
JOIN Orders b ON b.CustomerID = a.CustomerID
   AND b.orderdate = (
       SELECT MAX(orderdate)
       FROM Orders
       WHERE CustomerID = a.CustomerID
         AND orderdate < a.orderdate
      )

So correlated sub-queries might not always be equivalent to some JOIN. However in general, I agree with r937 that it is most often a matter of which form makes sense when you read it in the context of the application and the result you are going for. Sometimes you think of the problem as a subquery, sometimes you think of it as a join.

 

[r937]

those are great examples, rac2



r937.com | rudy.ca

 

[svrobin]

Thank you r937 and rac2. These explanations and examples are wonderful. I now understand subqueries and joins much better.