asked on

SQL Server Full Outer Join problem

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?

Deepak Chauhan

Hi take a look on the below link it will be very help full.

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

Scott Pletcher

>> 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())

psyche6

ASKER

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.

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

psyche6

ASKER

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.

psyche6

ASKER

Underlying principal wasn't clearly stated.

Scott Pletcher

Thanks!

Good luck in the future!