?
Solved

SQL Server Full Outer Join problem

Posted on 2012-04-02
7
Medium Priority
?
456 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 69

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
What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

 
LVL 69

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 69

Expert Comment

by:Scott Pletcher
ID: 37816116
Thanks!

Good luck in the future!
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

770 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