Where clause in select does not work with Inequality of guid

Question

I want to query our CRM Db. I have a backup of CRM Db and the existing one. There is a custom entity,in both of them I have created. Some of my custom entity records has deleted from the existing db. I want to compare which records has deleted. Number of existing records is 17 and the backup contains 19 records. The custom entity is named new_services. The backup database is named test2 and the existing one is MSCRM The table name is new_services and When I query this equal ones :

select * from test2.dbo.new_services as t1,
 MSCRM.dbo.new_services as t2
where t1.new_service_Id = t2.new_service_Id;

And return correct records, But when I try this :

select * from test2.dbo.new_services as t1,
 MSCRM.dbo.new_services as t2
where t1.new_service_Id != t2.new_service_Id;

It returns 308 records ! The new_service_id is the guid of the record. Where is wrong?

Answer 1

Your logic is wrong ;-) Assume 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 will return 1 row:

SELECT * 
FROM T1
JOIN T2
    ON T1.x = T2.x

1  1

whereas your second example will return 4*4 - 1 = 15 rows. If you think about it, in a set of 4x4=16 rows where the predicate is true for 1 row, how many rows if the predicate false for?

What you probably are looking for is MINUS or EXCEPT (not sure what it is called in sql-server):

SELECT * from test2.dbo.new_services
EXCEPT
SELECT * from MSCRM.dbo.new_services

or possibly the other way around (not sure which table is which)

Edit: As pointed out in the comments MySQL (not sure of latest version), does not support the basic set operations INTERSECT and EXCEPT. EXCEPT is SET MINUS and can be implemented using NOT EXISTS or NOT IN:

SELECT t1.* FROM T
WHERE NOT EXISTS (
    SELECT 1 FROM T2 WHERE T1.... = T2... AND ...
);

Another option is using an OUTER JOIN.

Worth noting is that the above corresponds to EXCEPT ALL since any duplicates in T1 is preserved. For a true EXCEPT operator, DISTINCT should be used:

SELECT DISTINCT t1.* FROM T
WHERE NOT EXISTS (
    SELECT 1 FROM T2 WHERE T1.... = T2... AND ...
);

I will not delve into the shadowlands of null, and the consequences it has for logical equality between expressions

INTERSECT on the other hand corresponds closely to multiplication. We can mimic that with the IN or EXISTS predicate in the same manner as above.