Solved

SQL Server Full Outer Join problem

Posted on 2012-04-02
7
412 Views
Last Modified: 2012-04-06
I'm experiencing some weirdness in a SQL Server Full Outer Join

The join is between two simple tables:

Migration.AccessObjectsInEffect (
    AccessObjectID char(8) not null,
    InEffect datetime not null
    )

Vector.EmployerSettings (
    AccessObjectID PK char(8) not null,
    RandomTestingDayOfMonth tinyint not null,
    Logged datetime not null
   )

Open in new window


I want to find all AccessObjectsInEffect where InEffect < GetDate() and that are not already in the EmployerSettings. I also want to be sure there arn't any records in the EmployerSetting that are not in AccessObjectsInEffect table.

A Full Outer Join should do the job showing where one table has a record but the other doesn't. Here's the code:

Select
	ES.*,
	I.*
From
	Vector.EmployerSettings ES
	Full Outer Join Migration.AccessObjectInEffect I on
		I.AccessObjectID = ES.AccessObjectID
		and I.InEffect < GetDate()
Where ES.AccessObjectID is null or I.AccessObjectID is null

Open in new window


The problem I'm encountering is that I get all records from AccessObjectsInEffect, not just those where the InEffect date has pased. Here's one sample record returned:

AccessObjectID	RandomTestingDayOfMonth	Logged	AccessObjectID	InEffect
NULL	NULL	NULL	AO999999	2013-03-13 12:34:17.000

Open in new window


This record should not have been included, since the InEffect date is in the future.

I recoded the query as:

Select
	ES.*,
	I.*
From
	Vector.EmployerSettings ES
	Full Outer Join (
		Select *
		From Migration.AccessObjectInEffect
		Where InEffect < GetDate()
		) I on I.AccessObjectID = ES.AccessObjectID
Where ES.AccessObjectID is null or I.AccessObjectID is null

Open in new window


To replace the 2nd table in the join with a query and now it works as expected.

My question is what's going on here? Is there a reason not to put additional conditions on the join clause when doing full outer joins? Or is this a SQL Server bug?
0
Comment
Question by:psyche6
  • 3
  • 3
7 Comments
 
LVL 15

Expert Comment

by:deepakChauhan
Comment Utility
Hi take a look on the below link it will be very help full.

http://msdn.microsoft.com/en-us/library/ms187518.aspx
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
>> what's going on here? <<

SQL is just doing what it thinks you told it to do.


      Full Outer Join Migration.AccessObjectInEffect I on
            I.AccessObjectID = ES.AccessObjectID
            and I.InEffect < GetDate()


For a FULL OUTER JOIN (Full OJ) SQL will include all rows from both tables.  The conditions specified in the OJ are **exclusively join conditions**.  That is, they control ONLY whether two rows are joined, NOT whether they appear in the result set or not.

For OJs, you must put selectivity conditions in the WHERE clause, like so:


...
      Full Outer Join Migration.AccessObjectInEffect I on
            I.AccessObjectID = ES.AccessObjectID
Where ES.AccessObjectID is null or (I.AccessObjectID is null or I.InEffect < GetDate())
0
 
LVL 1

Author Comment

by:psyche6
Comment Utility
ScottPletcher:

Thanks for you input, I'll research 'exclusively join conditions'. However, that's not quite the answer; at least your proposed modification to my first query does not make it Functionally equivalent to my second query. After adding the indicated records from the AccessObjectInEffect table to the EmployerSettings table, my second query now returns no records as it should. This indicates that there are no records in EmployerSettings that are not in AccessObjectInEffect and there are no records in AccessObjectInEffect with a past InEffect date that are not in EmployerSettings.

The query with your modification returns all records from the AccessObjectInEffect table linked to all existing records from the EmployerSettings table.

My original query seems to describe what I want to do - a full outer join between two records sets. The first set is all records from the EmployerSettings table and the second set is all records from the AccessObjectInEffect table where InEffect < GetDate(). I'm thinking more and more that this is a bug.

deepakChauhan:

Please DO NOT reply with simple links to Microsoft standard documentation. It's insulting to me and shows that you have no idea what the question is about.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
Comment Utility
>> at least your proposed modification to my first query does not make it Functionally equivalent to my second query. <<

No, nor was it intended to be.

It was to adjust the original query to apply the conditions as specified **in a FOJ**.

The first query *will* return *different* results from coding the condition in the WHERE rather than the FOJ.


Your second query is a totally different query, functionally.


I don't think it's a bug, it's a question of what OJ means.  To limit the rows in a result set, you use WHERE, but when you specify a FOJ you've already implied certain conditions that I don't think you can "cancel out" with a WHERE clause.
0
 
LVL 1

Author Comment

by:psyche6
Comment Utility
Hmmmmm ... I think we're at the answer.

If I do a query like:

From
    TableA
    Full Outer Join TAbleB on TableB.Key = TableA.Key

It's understood that, before possible filtering by a Where clause, I'll see all the records from TableB. Some will be in shared records with TableA where the Key values matched, some will be in records by themselves where the Key values did not match.

Changing it to:

From
    TableA
    Full Outer Join TAbleB on TableB.Key = TableA.Key and TableB.InEffect < GetDate()

Does not mean that I won't see all records from TableB. It only means that the TableB records will shared with TableA records where both the Key values match AND the TableB.InEffect value is less that GetDate(). TableB records that have an InEffect date greater than GetDate() will appear in records by themselves even though they may have Key values that match TableA records.

Darn! SQL Server and Microsoft win again (I keep thinking I'm smarted than they are).

So if I want to limit the records returned from one of the tables in a full outer join, I have to do it as I did in my 2nd query - by using a subquery to define the recordset I want to join to.
0
 
LVL 1

Author Closing Comment

by:psyche6
Comment Utility
Underlying principal wasn't clearly stated.
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
Thanks!

Good luck in the future!
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now