There's an obscure coding bug that I've seen multiple times in my 20 years of working with SQL Server, and it can be particularly difficult to spot as it doesn't cause a syntax error, yet the havoc it wreaks can be widespread. Consider the following scenario with two tables; they are similar but have differing column names. Now say I want to insert records from StagingTable into FinalTable, but only if they don't already exist there. Yes, there are other ways to handle this like Merge, Left Outer Join etc., but this is still commonly used and you will no doubt see it in the wild.

DROP TABLE IF EXISTS dbo.StagingTable;
CREATE TABLE dbo.StagingTable (Col1 INT, Col2 INT);
INSERT dbo.StagingTable (Col1, Col2)
VALUES (1,1), (2,2), (3,3), (4,4);

DROP TABLE IF EXISTS dbo.FinalTable;
CREATE TABLE dbo.FinalTable (Int1 INT, Int2 INT);
INSERT dbo.FinalTable (Int1, Int2)
VALUES (1,1), (2,2), (3,3);

--Insert new rows from stagingtable into FinalTable if rows don't already exists there.
--In this scenario, we should insert 1 row with values (4,4)
INSERT dbo.FinalTable (Int1, Int2)
SELECT Col1, Col2
FROM dbo.StagingTable stg
WHERE NOT EXISTS (SELECT * FROM dbo.FinalTable WHERE Col1 = stg.Col1)

--FinalTable still only has three rows, and (4,4) isn't among them.
SELECT * FROM dbo.FinalTable

Now, before reading on, run the script above and see if you can figure out why the new row isn't being added.

In the NOT EXISTS subquery (anti-semi-join), the coder assumed that Col1 exists in FinalTable, but that isn't the case. One might expect this would be cause for a syntax error, but in reality, Col1 DOES exist in the scope of the overall statement, so SQL Server assumes you know what you are doing and executes the query without error, but also without inserting any new data. It's the same as a subquery having a predicate of WHERE 1=1 which of course isn't the desired behavior. The simplest way to avoid this is to ensure that all columns are qualified with table name or alias. When qualified, SQL quickly notices that something is wrong, and points out the error either at parse-time or run-time.

My suggestion is for SQL Prompt to detect subqueries and call them out IF they use unqualified columns. I'm not suggesting a call-out of missing qualifications in ALL queries as it isn't always required, but by checking subqueries, it might go a long way to preventing this in the future.

The simple fix to the insert statement above is of course the following:

INSERT dbo.FinalTable (Int1, Int2)
SELECT Col1, Col2
FROM dbo.StagingTable stg
WHERE NOT EXISTS (SELECT * FROM dbo.FinalTable ft WHERE ft.Int1 = stg.Col1)