Full Outer Joins

**benwizzle** · Jul 27 '10, 01:35 PM

Ive never had to do this before but I think a UNION ALL would serve the purpose your looking for.

**benwizzle** · Jul 27 '10, 05:09 PM

Actually I take that back...Union would require that all the tables have the same amount of columns and DataTypes. After thinking about this I think just doing some LEFT and RIGHT JOINS would work. As they return all columns even if there are no matches.

**akhos1** · Jul 27 '10, 08:01 PM

Originally posted by benwizzle

Actually I take that back...Union would require that all the tables have the same amount of columns and DataTypes. After thinking about this I think just doing some LEFT and RIGHT JOINS would work. As they return all columns even if there are no matches.

It is my understanding that outer joins return ALL values of one of the tables (i.e. left join returns all values for the left table and shows matching values between tables, even if the right table is null and vice versa for right join).

My issue is that any table can be empty. Either table 1 or table 2 or table 3. The outer join would work fine if I knew which table would be empty, but I don't. I'm trying to avoid outer joins all together.

I would really appreciate the help. I'm stumped

**ck9663** · Jul 28 '10, 06:47 AM

It looks like you want to return all rows regardless if it's matched or not. I don't think there's any other way than FULL OUTER JOIN...

Good Luck!!!

~~ CK

**akhos1** · Jul 28 '10, 06:15 PM

Originally posted by b0010100

After reading and rereading your post I think I have a solution for you.

You are performing two operations on four datasets, TableA, TableB, TableAB(INNER JOIN),TableC.

We start with TableA, TableB, and TableC available to us. We need to create dataset TableAB
by INNER JOINing tables TableA and TableB as our first operation.

Code:

SELECT A.[column1],B.[column2] FROM
TableA A
INNER JOIN
TableB B
ON
A.joinField = B.joinField

This produces dataset TableAB. Now we can proceed to our second operation, retrieving all rows
from TableC and appending them to the dataset TableAB.

Code:

SELECT column1,column2 as [ccolumn12] FROM TableAB
UNION ALL
SELECT [ccolumn7] as [column1],[ccolumn12] FROM TableC

To successfully use the UNION ALL, you will need to make sure the column names in TableC match with the column names in the dataset TableAB. In addition to matching column names you will have to match data types as well. You can accomplish this easily by assigning incongruous
fields an homogenizing alias. In the code above I give [ccolumn7] the alias [column1] which matches the field from the TableAB dataset. The second alias in the code sample assigns column2 from the TableAB dataset the alias [ccolumn12] which matches the field name from the TableC dataset.

Now that we have all the pieces we can put this together in one big script.

Code:

SELECT A.[Column1],B.[column2] as [ccolumn12] FROM TableA A
INNER JOIN TableB B ON A.joinField = B.joinField

UNION ALL
SELECT [ccolumn7] as [column1], [ccolumn12] FROM TableC

This script combines rows from INNER JOINed datasets TableA and TableB with any and all rows from dataset
TableC.

Let me know how this works out for you!

Maybe I'm not being clear. Let's make a random assumption that table B is the blank table. An inner join with tables A and B would yield no results, therefore the union with table AB and table C would show all values in table C.
The code you put up there works perfectly, but I want to find out how to modify it such that any table may be null at any time, but I can still find matched values in the tables that actually are populated.

**Jerry Winston** · Jul 28 '10, 07:42 PM

Easy, just throw in conditionals for you table scenarios:

Code:

DECLARE @TableARowCount INT
DECLARE @TableBRowCount INT
DECLARE @TableCRowCount INT
DECLARE @Tables TABLE (tableID INT IDENTITY(1,1),tName  varchar(40))
DECLARE @populatedTables INT
DECLARE @sqlString VARCHAR(4096) 

SET @TableARowCount = (SELECT COUNT(*) FROM TableA)
SET @TableBRowCount = (SELECT COUNT(*) FROM TableB)
SET @TableCRowCount = (SELECT COUNT(*) FROM TableC)

IF (@TableARowCount > 0)
BEGIN
INSERT INTO @Tables VALUES ('TableA')
END

IF(@TableBRowCount > 0)
BEGIN
INSERT INTO @Tables VALUES ('TableB')
END

IF(@TableCRowCount > 0)
BEGIN
INSERT INTO @Tables VALUES ('TableC')
END

SET @populatedTables = (SELECT COUNT(*) FROM @Tables)
IF @populatedTables = 3
	BEGIN
		SET @sqlString = 'SELECT [cols] FROM ' + (SELECT tName FROM @Tables WHERE tableID = 1) + ' T1 INNER JOIN '+(SELECT tName FROM @Tables WHERE tableID = 2)+' ON T1.jf = T2.jf UNION ALL SELECT [cols] FROM ' + (SELECT tName FROM @Tables WHERE tableID = 3)
		EXEC sp_executesql @sqlString
	END
ELSE 
	BEGIN
		IF @populatedTables = 2
			BEGIN
				SET @sqlString = 'SELECT [cols] FROM ' + (SELECT tName FROM @Tables WHERE tableID = 1) + ' T1 INNER JOIN '+(SELECT tName FROM @Tables WHERE tableID = 2)+' ON T1.jf = T2.jf '
				EXEC sp_executesql @sqlString
			END
		ELSE 
			BEGIN
				IF @populatedTables = 1
					BEGIN
						SET @sqlString = 'SELECT [cols] FROM ' + (SELECT tName FROM @Tables WHERE tableID = 1) 
						EXEC sp_executesql @sqlString
					END	
			END
	END

INNER JOINing tables based on whether or not they are null is a requirement I have never faced. That's why I love Bytes.com, you get to solve the most unique problems!

**akhos1** · Jul 28 '10, 07:45 PM

Originally posted by b0010100

Easy, just throw in conditionals for you table scenarios:

Code:

DECLARE @TableARowCount INT
DECLARE @TableBRowCount INT
DECLARE @TableCRowCount INT
DECLARE @Tables TABLE (I INT IDENTITY(1,1),tName  varchar(40))
DECLARE @populatedTables INT
DECLARE @sqlString VARCHAR(4096) 

SET @TableARowCount = (SELECT COUNT(*) FROM TableA)
SET @TableBRowCount = (SELECT COUNT(*) FROM TableB)
SET @TableCRowCount = (SELECT COUNT(*) FROM TableC)

IF (@TableARowCount > 0)
BEGIN
INSERT INTO @Tables VALUES ('TableA')
END

IF(@TableBRowCount > 0)
BEGIN
INSERT INTO @Tables VALUES ('TableB')
END

IF(@TableCRowCount > 0)
BEGIN
INSERT INTO @Tables VALUES ('TableC')
END

SET @populatedTables = (SELECT COUNT(*) FROM @Tables)
IF @populatedTables = 3
	BEGIN
		SET @sqlString = 'SELECT [cols] FROM ' + (SELECT tName FROM @Tables WHERE tableID = 1) + ' T1 INNER JOIN '+(SELECT tName FROM @Tables WHERE tableID = 2)+' ON T1.jf = T2.jf UNION ALL SELECT [cols] FROM ' + (SELECT tName FROM @Tables WHERE tableID = 3)
		EXEC sp_executesql @sqlString
	END
ELSE 
	BEGIN
		IF @populatedTables = 2
			BEGIN
				SET @sqlString = 'SELECT [cols] FROM ' + (SELECT tName FROM @Tables WHERE tableID = 1) + ' T1 INNER JOIN '+(SELECT tName FROM @Tables WHERE tableID = 2)+' ON T1.jf = T2.jf '
				EXEC sp_executesql @sqlString
			END
		ELSE 
			BEGIN
				IF @populatedTables = 1
					BEGIN
						SET @sqlString = 'SELECT [cols] FROM ' + (SELECT tName FROM @Tables WHERE tableID = 1) 
						EXEC sp_executesql @sqlString
					END	
			END
	END

INNER JOINing tables based on whether or not they are null is a requirement I have never faced. That's why I love Bytes.com, you get to solve the most unique problems!

WOW, this looks great! I'll try it out this afternoon and I'll let you know how it works.

Thanks a million!

**ck9663** · Jul 28 '10, 10:02 PM

I did not read b0010100's entire solution. The only other thing I would recommend is on the checking if a table is empty. And this is true to most situation.

If you only need to check if a table is not empty, instead of:

Code:

if (select count(*) from tablename) > 0
   select 'table is not empty'
else
   select 'table is empty'

do this:

Code:

if exists (select 1 from tablename) 
   select 'table is not empty'
else
   select 'table is empty'

The difference? You don't need the count of the entire table. The exists function returns the result set immediately and will not continue to read the entire table. The count(*) check is good for smaller tables and, if possible, should be avoided.

Happy Coding!!!

~~ CK

**Jerry Winston** · Jul 29 '10, 02:41 PM

Mmmm, refactoring.... :)
Thanks CK!

**NeoPa** · Aug 2 '10, 05:05 PM

If anyone feels their posts belong in the previous thread (I've made a mistake on any) then please let me know and I can move them back across for you.

It all got a bit messy and I wasn't clear exactly who you were all replying to (in some cases at least - not all).

**NeoPa** · Aug 2 '10, 05:21 PM

I think CK made the suggestion of FULL OUTER JOINs in post #5. There is much confusion over this so I think it may be worth revisiting that. SQL JOINs describes what's available generally, but mainly from a Jet (MS Access) perspective. The only difference for T-SQL is that the full term is FULL OUTER JOIN rather than the simpler OUTER JOIN as used in the article.

I think, if I understand your question correctly, that the following concept should work for you :

Code:

(([TableA] FULL OUTER JOIN [TableB]) FULL OUTER JOIN [TableC])

I suspect this is much as CK was suggesting when he mentioned it originally. You would need to provide the ON criteria for both joins of course.

**akhos1** · Aug 2 '10, 07:10 PM

Originally posted by NeoPa

I think CK made the suggestion of FULL OUTER JOINs in post #5. There is much confusion over this so I think it may be worth revisiting that. SQL JOINs describes what's available generally, but mainly from a Jet (MS Access) perspective. The only difference for T-SQL is that the full term is FULL OUTER JOIN rather than the simpler OUTER JOIN as used in the article.

I think, if I understand your question correctly, that the following concept should work for you :

Code:

(([TableA] FULL OUTER JOIN [TableB]) FULL OUTER JOIN [TableC])

I suspect this is much as CK was suggesting when he mentionned it originally. You would need to provide the ON criteria for both joins of course.

My apologies, I didn't realize I was hijacking anyone's post.

In any case, thanks a bunch guys! It took me a while to finish debugging, but I just got done today and it works like a charm. Excellent suggestions!

**NeoPa** · Aug 2 '10, 08:03 PM

Originally posted by akhos1

akhos1: My apologies, I didn't realize I was hijacking anyone's post.

Fair dos. I'm sure you'll know for next time :)

I'm interested to hear which of the posts was most helpful though. It seems you have found a solution anyway.

**akhos1** · Aug 2 '10, 08:31 PM

b0010100 made the suggestions about stepping through and checking whether or not tables were null. I'm not at all familiar with the syntax he was using (I had initially assumed that it was just in pseudocode). I was able to follow his general logic to obtain the visual basic code that I was looking for.
The inner joins worked exactly like I wanted them to and I'm so happy I didn't have to use any outer joins.

I really appreciated the quick responses I received from this website.

Full Outer Joins

Full Outer Joins

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment