Your logic is wrong 😉 Take two tables:
CREATE TABLE T1 ( x int not null ); INSERT INTO T1 (x) VALUES (1),(2),(3),(4); CREATE TABLE T2 ( x int not null ); INSERT INTO T2 (x) VALUES (1),(12),(13),(14);
Your first example returns 1 line:
SELECT * FROM T1 JOIN T2 ON T1.x = T2.x 1 1
whereas your second example returns 4 * 4 – 1 = 15 lines. If you think about it, in a set of 4×4 = 16 lines in which the predicate is true for 1 row, for how many rows if the predicate is wrong?
What you're probably looking for is MINUS or EXCEPT (not sure, as it says in SQL Server):
SELECT * from test2.dbo.new_services EXCEPT SELECT * from MSCRM.dbo.new_services
or possibly vice versa (not sure which table is which)