• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 464
  • Last Modified:

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?
0
psyche6
Asked:
psyche6
  • 3
  • 3
1 Solution
 
Deepak ChauhanSQL Server DBACommented:
Hi take a look on the below link it will be very help full.

http://msdn.microsoft.com/en-us/library/ms187518.aspx
0
 
Scott PletcherSenior DBACommented:
>> 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
 
psyche6Author Commented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Scott PletcherSenior DBACommented:
>> 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
 
psyche6Author Commented:
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
 
psyche6Author Commented:
Underlying principal wasn't clearly stated.
0
 
Scott PletcherSenior DBACommented:
Thanks!

Good luck in the future!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now