Q

Problems with the WHERE clause

I have a Staff table that has a one-to-many relationship with an Orders table. I wish to show all Staff records

irrespective of whether there is an order associated with it. I have, therefore, created a left outer join on the Orders tables which works fine. However, when I create a "where" statement to filter the Order table on the current month e.g. WHERE Orders."Date Created" >= {d '2002-02-01'} AND Orders."Date Created" <= {ts '2002-02-12 23:59:59.000'}, the Staff table once again only shows only those staff members who have associated orders. Is there anyway around this problem?

The reason you are not getting the results you want is because in SQL-92 joins, the WHERE condition is evaluated after the join. You only want to take into consideration those orders created within a particular date range, but in the case where there are no orders, the value for DateCreated would be NULL because of the left outer join. Because a condition involving a NULL value does not resolve to true, the entire row is thrown out. The solution is either to use a sub-query filtered by the where clause in place of the table, or to include the filter as a join condition.

The SQL standard allows us to substitute a sub-query in place of a table and use an alias. This allows us to write SQL like this:

select *
  from Staff
       left join
       ( select * from Orders 
           where DateCreated >= {d '2002-02-11'} ) Orders2
    on Staff.StaffID = Orders2.StaffID
Here is an example of including the filter in the join condition:
select *
  from Staff
       left join
       Orders
    on Staff.StaffID = Orders.StaffID and
       Orders.DateCreated >= {d '2003-02-11'}
Either choice will work; I would use whichever gives you the best performance in your particular environment.

For More Information

  • What do you think about this answer? E-mail the edtiors at editor@searchDatabase.com with your feedback.
  • The Best SQL Web Links: tips, tutorials, scripts, and more.
  • Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
  • Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
  • Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.

This was first published in February 2002

Dig deeper on Oracle and SQL

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.

0 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:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close