Azure上SQL Server 2016跨库查询：找出不存在的SourceId记录

Hey there, let's work through this problem step by step—you've got two key hurdles here: fixing the broken query syntax, and working around Azure's restriction on specifying database names directly. Let's break it down:

First: Correct Your Query Syntax #

Your original SQL had a few syntax mistakes (wrong JOIN structure, invalid NOT EXISTS usage, and mismatched field references). Since you need records from Database1.Table1 where DataSourceId doesn't exist in Database2.Table1's SourceId column, here are two valid cross-database query options:

Option 1: Using NOT EXISTS (Clean and Efficient) #

SELECT DataSourceID, OrderNo 
FROM Database1.dbo.Table1 t1
WHERE NOT EXISTS (
    SELECT 1 
    FROM Database2.dbo.Table1 t2
    WHERE t2.SourceId = t1.DataSourceId
);

Option 2: Using LEFT JOIN + IS NULL #

If you prefer a join-based approach:

SELECT t1.DataSourceID, t1.OrderNo 
FROM Database1.dbo.Table1 t1
LEFT JOIN Database2.dbo.Table1 t2 
    ON t2.SourceId = t1.DataSourceId
WHERE t2.SourceId IS NULL;

Why your original query failed:

• You didn't include an ON clause for your JOIN (required for valid join syntax)
• Your NOT EXISTS clause was missing a proper subquery structure
• You mixed up the field names (DataSourceId in Database1 vs SourceId in Database2)

Second: Get Around Azure's Database Name Restriction #

Since production Azure blocks direct database name references in queries, here are three practical workarounds for your same-instance scenario:

1. Create a Synonym (Recommended for Simplicity) #

Synonyms act as aliases for objects in other databases. Run this in Database1 to create an alias for Database2.Table1:

USE Database1;
GO
CREATE SYNONYM dbo.Db2_SourceIds FOR Database2.dbo.Table1;
GO

Now you can query without referencing the database name directly:

SELECT DataSourceID, OrderNo 
FROM dbo.Table1 t1
WHERE NOT EXISTS (
    SELECT 1 
    FROM dbo.Db2_SourceIds t2
    WHERE t2.SourceId = t1.DataSourceId
);

2. Create a View #

If you have permission, create a view in Database1 that pulls the necessary data from Database2:

USE Database1;
GO
CREATE VIEW dbo.Db2_SourceIds_View AS
SELECT SourceId FROM Database2.dbo.Table1;
GO

Then query using the view:

SELECT DataSourceID, OrderNo 
FROM dbo.Table1 t1
WHERE NOT EXISTS (
    SELECT 1 
    FROM dbo.Db2_SourceIds_View t2
    WHERE t2.SourceId = t1.DataSourceId
);

3. Use OPENQUERY (Alternative) #

If your instance allows it, you can use OPENQUERY to target the other database, though this is less straightforward for same-instance scenarios:

SELECT DataSourceID, OrderNo 
FROM dbo.Table1 t1
WHERE NOT EXISTS (
    SELECT 1 
    FROM OPENQUERY([Your_Azure_SQL_Instance_Name], 'SELECT SourceId FROM Database2.dbo.Table1') t2
    WHERE t2.SourceId = t1.DataSourceId
);

Note: You may need to enable Ad Hoc Distributed Queries for this to work, so the synonym/view approach is usually better.

Performance Tips #

With 3 million records in Database1.Table1, make sure to optimize for speed:

• Add a non-clustered index on Database1.Table1.DataSourceId
• Ensure Database2.Table1.SourceId has a clustered index (since it only has 2000 rows, this will make matching lightning fast)

内容的提问来源于stack exchange，提问作者Stpete111