views:

781

answers:

5

How can you do a full outer join in sqlserver 2005?

Seems like there is full outer join in sqlserver 2008 but I need to do this in sqlserver 2005.

In other words, I am merging two views based on the ACCTNUM col in both views (The views show aggregates so there is at most one ACCTNUM record in each view for each account) and I would like every record in each table to show up, even when there is no match in the other (ie, full outer join).

+1  A: 

Full outer joins should be supported by SQL Server 2005 - what makes you think they aren't?

Dave Cluderay
+1  A: 

SQL Server 2005 supports full joins:

In fact, I think FULL JOIN works at least as far back SQL Server 7.

Joel Coehoorn
+1  A: 

Note that if you are using Access to connect to a DB, you can't use full outer join, since Access does not support it.

mgroves
..except for pass-through queries or ADPs where it's run on the SQL box and not in JET
gbn
+1  A: 
SELECT A.*, B.* FROM TABLE1 A FULL JOIN TABLE2 B ON A.Id = B.TableAID
Chris B. Behrens
+3  A: 

This will work in SQL 2005

Select
    tableA.Column,
    tableA.AnotherColumn,
    tableB.Column
From
    tableA
Full Outer Join
    tableB On tableA.Id = tableB.Id

Note you can use Full Join or Full Outer Join, it doesnt make a difference.

Chalkey