SQL Server: How to add a full join to two tables but have matches line up? I am working with a system that has two databases. One for the ERP system and one for the website. Both DB's have tables for alternate codes. I am trying to write a query that will show me differences and matches between the two db's alternate code tables.

To setup my environment here is a small sample:

CREATE TABLE [dbo].[alt_code_ERP](
    [UID] [int] NOT NULL,
    [item_id] [nchar](10) NOT NULL,
    [alternate_code_ERP] [nvarchar](50) NOT NULL,
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[web_alt_code](
    [UID] [int] NOT NULL,
    [item_id] [nchar](10) NOT NULL,
    [web_alternate_code] [nchar](50) NOT NULL
) ON [PRIMARY]

GO

INSERT INTO [dbo].[alt_code_ERP] ([UID],[item_id],[alternate_code_ERP]) VALUES (1, 'part1' , 'part1_alt')
INSERT INTO [dbo].[alt_code_ERP] ([UID],[item_id],[alternate_code_ERP]) VALUES (2, 'part2' , 'part2a_alt')
INSERT INTO [dbo].[alt_code_ERP] ([UID],[item_id],[alternate_code_ERP]) VALUES (3, 'part2' , 'part2b_alt')
INSERT INTO [dbo].[alt_code_ERP] ([UID],[item_id],[alternate_code_ERP]) VALUES (4, 'part2' , 'part2c_alt')
INSERT INTO [dbo].[alt_code_ERP] ([UID],[item_id],[alternate_code_ERP]) VALUES (5, 'part10' , 'part10_alt')
GO

INSERT INTO [dbo].[web_alt_code] ([UID],[item_id],[web_alternate_code]) VALUES (1, 'part1' , 'part1_alt')
INSERT INTO [dbo].[web_alt_code] ([UID],[item_id],[web_alternate_code]) VALUES (2, 'part2' , 'part2a_alt')
INSERT INTO [dbo].[web_alt_code] ([UID],[item_id],[web_alternate_code]) VALUES (3, 'part2' , 'part2d_alt')
INSERT INTO [dbo].[web_alt_code] ([UID],[item_id],[web_alternate_code]) VALUES (4, 'part8' , 'part8_alt')
INSERT INTO [dbo].[web_alt_code] ([UID],[item_id],[web_alternate_code]) VALUES (5, 'part10', 'part10b_alt')
GO


I want a result that will have the following:


List each alternate code from each table once.
If there are any matching alternate codes from the two tables, I want to align them side by side.
Any without a match in the other table to be paired with a NULL


Something that looks like the following:


 item_id      alternate_code_erp   web_alternate_code

part1       part1_alt   part1_alt 
part2       part2a_alt  part2a_alt
part2       part2b_alt  NULL
part2       part2c_alt  NULL
part2       NULL        part2d_alt
part8       NULL        part8_alt
part10      part10_alt  NULL
part10      NULL        part10b_alt
You can use a FULL OUTER JOIN.

SELECT ISNULL(a.item_id, w.item_id) AS item_id,
    a.alternate_code_ERP, w.web_alternate_code
FROM alt_code_erp a
FULL OUTER JOIN web_alt_code w
    ON a.item_id = w.item_id
    AND a.alternate_code_ERP = w.web_alternate_code
ORDER BY 1, ISNULL(a.alternate_code_ERP, w.web_alternate_code)


I added an ORDER BY in case you need it.  It's still not exactly the same but without doing some string manipulation you aren't going to get an order of item_id like you want.