Query works on SQLite but fails on SQL Server

The following query works for me in SQLite.

SELECT Id, (SELECT Id
            FROM Vehicles v
            WHERE v.Id = t.VehicleId) AS VehId
FROM Transactions t
WHERE t.VehicleId IS NOT NULL AND VehId IS NULL;

But in SQL Server, it gives me an error.

Invalid column name ‘VehId’.

And the editor puts a red squiggly line under the last VehId (but not the first).

As you might expect, Vehicles.Id is the primary key. I’m trying to find all foreign keys in Transactions that don’t reference any row in Vehicles. What’s the best way to accomplish this?

Answer

The problem with your query is that, in SQL Server, the column list is evaluated after the WHERE clause. That’s why you can’t reference a column alias from the WHERE clause.

There are several ways you can rewrite your query. Here are 3 of them.

SELECT *
FROM (
    SELECT Id, (SELECT Id
                FROM Vehicles v
                WHERE v.Id = t.VehicleId) AS VehId
    FROM Transactions t) x
WHERE VehicleId IS NOT NULL AND VehId IS NULL;

SELECT Id, VehId
FROM Transactions t
OUTER APPLY (SELECT Id
            FROM Vehicles v
            WHERE v.Id = t.VehicleId) v(VehId)
WHERE t.VehicleId IS NOT NULL AND VehId IS NULL;

SELECT Id, NULL AS VehId
FROM Transactions t
WHERE t.VehicleId IS NOT NULL 
AND NOT EXISTS (SELECT Id
            FROM Vehicles v
            WHERE v.Id = t.VehicleId);