5 SQL mistakes that silently return wrong answers

Some SQL mistakes crash with a red error message. Those are the friendly ones. The dangerous mistakes run perfectly, return a neat result, and the numbers are simply wrong. Nobody notices until a report goes out.

This guide covers five of them. Each one follows the same pattern: the mistake, a diagram of the query's logical effect, and the fix. The stated results use the unmodified DDI SQL Server practice dataset v1.0.0; if you change its rows, your results may differ.

Mistake 1: Checking for missing values with = NULL

You want the customers who have not given an email address. This looks reasonable:

SELECT CustomerID, FirstName, LastName
FROM dbo.Customers
WHERE Email = NULL;

It runs without an error and returns 0 rows. But four customers have no email address. Where did they go?

Why it happens. NULL marks a missing value, which may mean unknown, not applicable or not yet recorded; it is not the same as 0 or an empty string. With ANSI_NULLS ON (the default in SSMS and modern drivers; switching it OFF is deprecated), comparing any value with NULL using = gives UNKNOWN. A WHERE clause only keeps rows where the test is TRUE, so every row disappears.

SELECT CustomerID, FirstName, LastName
FROM dbo.Customers
WHERE Email IS NULL
ORDER BY CustomerID;

Mistake 2: NOT IN with a list that contains NULL

You want the employees who don't manage anyone. Managers are the IDs that appear in the ManagerID column, so you write:

SELECT EmployeeID, FirstName
FROM dbo.Employees
WHERE EmployeeID NOT IN (SELECT ManagerID FROM dbo.Employees);

Again: no error, 0 rows. Yet only three of the twelve employees are managers.

Why it happens. The three top managers (Amina, Oliver and Priya) have no manager themselves, so their ManagerID is NULL. The subquery's results therefore contain 1, 5, 9 (several times over) plus NULL. NOT IN means “not equal to every item in the list”, joined with AND. For a non-manager, the known-ID checks are TRUE but the NULL check is UNKNOWN, so the result is UNKNOWN. For a manager, one known-ID check is FALSE, so the result is FALSE. WHERE keeps neither.

SELECT e.EmployeeID, e.FirstName
FROM dbo.Employees AS e
WHERE NOT EXISTS (
    SELECT 1
    FROM dbo.Employees AS reports
    WHERE reports.ManagerID = e.EmployeeID
)
ORDER BY e.EmployeeID;

Mistake 3: A WHERE filter that cancels your LEFT JOIN

You want every department with its number of new hires since 1 January 2023, including departments with none. A LEFT JOIN keeps every department, so this should work:

SELECT d.DepartmentName, COUNT(e.EmployeeID) AS NewHires
FROM dbo.Departments AS d
LEFT JOIN dbo.Employees AS e
    ON e.DepartmentID = d.DepartmentID
WHERE e.HireDate >= '20230101'
GROUP BY d.DepartmentName
ORDER BY d.DepartmentName;

Why it happens. Logically, the LEFT JOIN preserves departments before WHERE filters the result. Nobody in Operations was hired in 2023 or later, so its joined rows fail the date test. The final result omits Operations, just as an INNER JOIN with this filter would. SQL Server may use a different physical execution plan.

SELECT d.DepartmentName, COUNT(e.EmployeeID) AS NewHires
FROM dbo.Departments AS d
LEFT JOIN dbo.Employees AS e
    ON e.DepartmentID = d.DepartmentID
   AND e.HireDate >= '20230101'
GROUP BY d.DepartmentName
ORDER BY d.DepartmentName;

Mistake 4: A join that doubles payment totals

You want total payment amounts and item quantities for paid orders, grouped by payment method. Payments holds one amount per paid order, and OrderItems holds the quantities, so you join them:

SELECT p.PaymentMethod,
       SUM(p.Amount)     AS TotalPaid,
       SUM(oi.Quantity)  AS ItemsSold
FROM dbo.Payments AS p
JOIN dbo.OrderItems AS oi
    ON oi.OrderID = p.OrderID
GROUP BY p.PaymentMethod
ORDER BY p.PaymentMethod;

Why it happens. Each paid order has one payment but two order lines. A join produces one row per matching pair, so every payment appears twice. SUM(p.Amount) then adds each payment twice. Each item quantity still appears once because this dataset has exactly one payment per paid order. That makes the mistake harder to spot.

Quick check: SELECT SUM(Amount) FROM dbo.Payments; returns 743,347.50. Add up the TotalPaid column above and you get 1,486,695.00: exactly double. Every payment was counted twice.

WITH ItemsPerOrder AS (
    SELECT OrderID, SUM(Quantity) AS Items
    FROM dbo.OrderItems
    GROUP BY OrderID
)
SELECT p.PaymentMethod,
       SUM(p.Amount) AS TotalPaid,
       SUM(i.Items)  AS ItemsSold
FROM dbo.Payments AS p
JOIN ItemsPerOrder AS i
    ON i.OrderID = p.OrderID
GROUP BY p.PaymentMethod
ORDER BY p.PaymentMethod;

Mistake 5: Integer division that truncates to zero

What share of orders were cancelled? Count the cancelled ones and divide by all orders:

SELECT SUM(CASE WHEN OrderStatus = N'Cancelled' THEN 1 ELSE 0 END)
       / COUNT(*) AS CancelledRate
FROM dbo.Orders;

Why it happens. SUM(...) and COUNT(*) both return whole numbers (int). In SQL Server, int divided by int gives an int, and the decimal part is simply cut off. 0.25 becomes 0. No rounding, no warning.

SELECT CAST(100.0 * SUM(CASE WHEN OrderStatus = N'Cancelled' THEN 1 ELSE 0 END)
            / COUNT(*) AS decimal(5,2)) AS CancelledPct
FROM dbo.Orders;

Quick checklist

Keep this next to your editor. Each line is a two-second check before you trust a result.

Try it yourself

SELECT COUNT(*) AS CustomersWithEmail
FROM dbo.Customers
WHERE Email IS NOT NULL;

SELECT o.CustomerID, COUNT(DISTINCT o.OrderID) AS Orders
FROM dbo.Orders AS o
JOIN dbo.OrderItems AS oi ON oi.OrderID = o.OrderID
WHERE o.CustomerID = 2
GROUP BY o.CustomerID;

Where to go next

Each mistake is covered in depth in the SQL Server roadmap:

• IS NULL and missing values: mistake 1
• EXISTS and NOT EXISTS: mistake 2
• LEFT and RIGHT OUTER JOIN: mistake 3
• Diagnosing duplicate or missing rows after joins: mistake 4
• CAST, CONVERT, and safe conversions: mistake 5