Q

LEFT OUTER JOIN to a MIN/MAX row

An Oracle user asks SQL expert Rudy Limeback how to use a LEFT OUTER JOIN with a MIN/MAX condition.

I need to create an SQL query where tables will be connected by LEFT OUTER JOIN, but some of them should be presented only by one (MIN or MAX of additional key) row. If I add the MIN/MAX conditions to the main where clause, it cuts my selection according to the number of MIN/MAX tables rows which found match on main tables. What is a good way to make such a selection?

The answer is to move the MIN/MAX condition out of the WHERE clause and into the ON clause.

The problem of specifying a condition in a LEFT OUTER JOIN has been discussed before. LEFT OUTER JOIN with ON condition or WHERE condition? (September 16, 2005) explains the difference.

Your query probably looked something like this:

SELECT t1.foo
     , t2.bar
  FROM table1 AS t1
LEFT OUTER
  JOIN table2 AS t2
    ON t2.table1_id = t1.id  
 WHERE t2.datefld = 
       ( SELECT MIN(datefld)
           FROM table2
          WHERE table1_id = t1.id )

The problem here is that the WHERE condition will surely filter out all unmatched rows from table1. If there is no matching row in table2, then the MIN will be NULL, and so the WHERE condition fails (nothing is equal to NULL).

SELECT t1.foo
     , t2.bar
  FROM table1 AS t1
LEFT OUTER
  JOIN table2 AS t2
    ON t2.table1_id = t1.id  
   AND t2.datefld = 
       ( SELECT MIN(datefld)
           FROM table2
          WHERE table1_id = t1.id )

Now, the MIN condition has been moved into the ON clause. In effect, the LEFT OUTER JOIN now says "get matching rows based on the keys and on the matching row being the MIN matching row." In other words, if there is no matching row in table2, the row from table1 is still returned.

This was first published in May 2008
This Content Component encountered an error

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

4 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close