SQL Server: How to add a full join to two tables but have matches line up?

Question

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:

1. List each alternate code from each table once.
2. If there are any matching alternate codes from the two tables, I want to align them side by side.
3. 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

Answer 1

a full outer join on the item_id and alternate_code should do the trick.

    SELECT COALESCE([a].[item_id], [b].[item_id]) as item_id,
       [a].[alternate_code_ERP],
       [b].[web_alternate_code]
FROM [alt_code_ERP] AS [a]
     FULL OUTER JOIN [web_alt_code] AS [b]
     ON [a].[item_id] = [b].[item_id]
    AND [a].[alternate_code_ERP] = [b].[web_alternate_code];

Answer 2

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.

Answer 3

I learned a pretty cool trick of writing a FULL OUTER JOIN using a left join union all anti-semi-join

SELECT 
    TA.item_id, 
    TA.alternate_code_ERP, 
    TB.web_alternate_code
FROM dbo.alt_code_erp AS TA
LEFT JOIN dbo.web_alt_code AS TB 
    ON TB.item_id = TA.item_id
    and TB.web_alternate_code = TA.alternate_code_ERP

UNION ALL
SELECT 
    TB.item_id, 
    Foo = NULL, 
    TB.web_alternate_code 
FROM dbo.web_alt_code AS TB
WHERE NOT EXISTS 
(
    SELECT NULL
    FROM dbo.alt_code_erp AS TA 
    WHERE TA.item_id = TB.item_id
    and TB.web_alternate_code = TA.alternate_code_ERP
)

You get the desired output:

╔══════════════════════════════════════════════════════════════════════════════════════════════════════════════════╗
║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                                               ║
║ part10     part10_alt                                         NULL                                               ║
║ part2      NULL                                               part2d_alt                                         ║
║ part8      NULL                                               part8_alt                                          ║
║ part10     NULL                                               part10b_alt                                        ║
╚══════════════════════════════════════════════════════════════════════════════════════════════════════════════════╝

Below is the execution plan :

Bloating the table using below gives the following execution plan :

UPDATE STATISTICS [dbo].[alt_code_ERP] WITH ROWCOUNT = 10000000, pagecount = 10000000
UPDATE STATISTICS [dbo].[web_alt_code] WITH ROWCOUNT = 10000000, pagecount = 10000000

To my surprise, in my tests on a laptop with 4 logical processors, 16GB RAM with SSD, the above solution performs/scales really good when dealing with large number of rows (tried with 20,40 and 140 rows). (Its already 12:30 AM EST .. so it might be looking good). Note that you dont have any Primary Key or Indexes defined in your table, so you will see sql server doing table scans.