Help!
I am trying to find the customer that are in VBSTEST that are not in VMFG9. the tables have identical schema. Whenever i run the query i never get a result returning.
`SELECT [ROWID] ,[ID] ,[NAME] ,[ADDR_1] ,[ADDR_2] ,[ADDR_3] ,[CITY] ,[STATE] ,[ZIPCODE] ,[COUNTRY] FROM [VMFG9].[dbo].[CUSTOMER] where NOT EXISTS( select v6.[ID] from [VBSTEST].[dbo].[CUSTOMER] as v6 left outer join [VMFG9].[dbo.CUSTOMER] as v9 on v9.id = v6.[id] where v9.id is null);` The subquery that you want doesn't use a join:
select . . . from [VMFG9].[dbo].[CUSTOMER] c where not exists (select 1 from [VBSTEST].[dbo].[CUSTOMER] v6 where c.id = v6.id ); You can use left join/where in the outer query, if you prefer. But a single table reference is sufficient for the not exists.
The easy way to do this is:
select id from [VBSTEST].[dbo].[CUSTOMER] except select id from [VMFG9].[dbo].[CUSTOMER]; You should also find that this kind of query is faster too.
Apart from being much longer, I think that there is an error in your query. Since null never equals anything else, it doesn't even equal null, then your condition:
where v9.id is null means that the condition
v9.id = v6.[id] can never be true.
With your NOT EXISTS you are currently saying, select all rows only when the subquery returns no results (else return nothing).
The subquery itself should be adequate:
select v6.* from [VBSTEST].[dbo].[CUSTOMER] as v6 left outer join [VMFG9].[dbo.CUSTOMER] as v9 on v9.id = v6.[id] where v9.id is null 