Comparing two tables by NOT NULL cells in different columns

Question

Hi,

We have two tables:

Table1
Servers|Databases|Users|Names
Server1|Database1|User1|Name1
Server2|Database2|User2|Name2
Server3|Database3|User3|Name3
Server4|Database4|User4|Name4
Server5|Database5|User5|Name5

Table2
Servers|Databases|Users|Names
NULL    |Database1|NULL |Name1
NULL    |NULL        |User1|Name2
NULL    |NULL        |NULL |Name3
Server5|Database5|NULL |NULL

I need to check Table1 by Table2 only on NOT NULL cells and if all of them in the row match do not return that row as the result. In this case it will be:

Results:
Servers|Databases |Users |Names
Server2|Database2|User2|Name2
Server4|Database4|User4|Name4

I used query like this:

SELECT a.Server, a.Databases, a.Users, a.Names FROM Table1
EXCEPT
SELECT ISNULL(b.Server,c.Server), ISNULL(b.Databases,c.Databases), ISNULL(b.Users,c.Users), ISNULL(b.Names,c.Names) FROM Table2 AS a, Table1 AS c

But for many rows (like 100 000) it takes ages to get results, any better way to work on this? 
Thanks

Answer 1

When you want to show us table data and DDL, use a method similar to my example. It makes it easier for us to help you.

Try this solution out:

DECLARE @Table1 TABLE (Servers NVARCHAR(10), Databases NVARCHAR(10), Users NVARCHAR(10), Names NVARCHAR(10))
INSERT INTO @Table1 (servers, databases, users, names) VALUES
('Server1','Database1','User1','Name1'),
('Server2','Database2','User2','Name2'),
('Server3','Database3','User3','Name3'),
('Server4','Database4','User4','Name4'),
('Server5','Database5','User5','Name5')

DECLARE @Table2 TABLE (Servers NVARCHAR(10), Databases NVARCHAR(10), Users NVARCHAR(10), Names NVARCHAR(10))
INSERT INTO @Table2 (servers, databases, users, names) VALUES
(NULL    ,'Database1'  ,NULL ,'Name1'),
(NULL    ,NULL       ,'User1','Name2'),
(NULL    ,NULL       ,NULL ,'Name3'),
('Server5' ,'Database5'  ,NULL ,NULL	)

SELECT t1.*
  FROM @Table1 t1
    INNER JOIN @Table2 t2
	  ON (t1.Servers = t2.Servers OR t2.Servers IS NULL)
	  AND (t1.databases = t2.databases OR t2.databases IS NULL)
	  AND (t1.users = t2.users OR t2.users IS NULL)
	  AND (t1.names = t2.names OR t2.names IS NULL)

---

Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
Really enjoyed it? See my profile!
My Tech Net Articles.

Answer 2

That works, thanks Patrick