Full Outer Joins

[akhos1]

I had a similar question.

I was wondering how I should approach a situation where any one of the three tables can be null.

Your suggestion works if the third table is the only table that is null. How do I alter this so that accommodations can be made for the fact that any one of the tables may be blank at any given time?

Thanks!

** Admin Edit **
This thread was created as a result of a hijack in another thread (How to modify an inner join search query to return all fields even if a table is null).

This conversation can now properly be continued in here.

-NeoPa.

[benwizzle]

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

[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.

[akhos1]

@benwizzle
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]

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]

@b0010100
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]

Easy, just throw in conditionals for you table scenarios:

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]

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

Thanks a million!

[ck9663]

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:

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

do this:

 
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]

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

[NeoPa]

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]

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 :

(([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]

@NeoPa
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]

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]

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.