Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Server Full Outer Join problem

Posted on 2012-04-02
7
Medium Priority
?
462 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 15

Expert Comment

by:Deepak Chauhan
ID: 37798255
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 70

Expert Comment

by:Scott Pletcher
ID: 37798458
>> 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
ID: 37798572
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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 1000 total points
ID: 37798647
>> 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
ID: 37814648
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
ID: 37814650
Underlying principal wasn't clearly stated.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 37816116
Thanks!

Good luck in the future!
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…

609 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