In this on views you state that condition "classified_ads.posted > '1999-01-01' " will not give the desired results because the column 'posted' is nullable hence this condition will compute to NULL whenever 'posted' column is NULL. Hence the query will never return rows where 'posted' value is NULL.And in order to solve this issue you go onto to create a view with the following query: (select * from classified_ads where posted > '1999-01-01')
Wont this view suffer from the same issue? Why will this view contain columns where 'posted' column is NULL.
Please explain.
-- sanjay raj, August 25, 2005
Re Sanjay Raj's comment: Well it has been almost 9 years since you asked the question, so probably by now you've either found the answer elsewhere or lost interest in databases altogether. But since others might be confused and no one else has responded I figured I would. This view-on-the-fly (ad_view) is a list of all ads with dates after 1999-01-01. This is then outer joined to the users table ("where users.user_id = ad_view.user_id(+)") so that users whose ids are included in ad_view will have their ads next to them, but all other users (including both those with no ads and those whose most recent ads were before 1999-01-01 and thus didn't make the cut for ad_view) will be listed with null/white space.The difference is that rather than outer joining and then filtering for date, which breaks it because you're filtering out the nulls, you're instead filtering for date first by creating the view, and then outer joining, so you don't touch the nulls after they're created.
-- Dan Cusher, June 10, 2014