MySQL LEFT OUTER JOIN does not filter records

I am trying a simple LEFT JOIN query where I want the records from the LEFT table where there are no matches in the RIGHT table.

SELECT
LeftTable.ID,
RightTable.ID as NullID
FROM
LeftTable
LEFT OUTER JOIN RightTable ON RightTable.ID = LeftTable.ID

So, how do I select the rows where the result only contains records where NullID is NULL? I thought an LEFT OUTER JOIN was what was required but all the Left records are being returned with matching and unmatching right records where I only want left records with unmatching right records. The database engine is InnoDB.

For example, the query is returning the following:

LeftTable.ID, NullID
1, 1
2, Null

I only want result 2 i.e. where NullID = Null

I tried the following LEFT JOIN and is the kind of query I have been trying but I get the error "[Err] 1054 - Unknown column 'NullID' in 'where clause'".

SELECT
LeftTable.ID,
RightTable.ID as NullID
FROM
LeftTable
LEFT JOIN RightTable ON RightTable.ID = LeftTable.ID
where NullID = Null;

Hopefully this explains my problem. Thanks.

---

As documented under Working with NULL Values:

To test for NULL, use the IS NULL and IS NOT NULL operators, as shown here:

mysql> SELECT 1 IS NULL, 1 IS NOT NULL;
+-----------+---------------+
| 1 IS NULL | 1 IS NOT NULL |
+-----------+---------------+
|         0 |             1 |
+-----------+---------------+

You cannot use arithmetic comparison operators such as =, <, or <> to test for NULL. To demonstrate this for yourself, try the following query:

mysql> SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;
+----------+-----------+----------+----------+
| 1 = NULL | 1 <> NULL | 1 < NULL | 1 > NULL |
+----------+-----------+----------+----------+
|     NULL |      NULL |     NULL |     NULL |
+----------+-----------+----------+----------+

Because the result of any arithmetic comparison with NULL is also NULL, you cannot obtain any meaningful results from such comparisons.

Also, as documented under Problems with Column Aliases:

Standard SQL disallows references to column aliases in a WHERE clause. This restriction is imposed because when the WHERE clause is evaluated, the column value may not yet have been determined.

Therefore, in your case:

SELECT LeftTable.ID, RightTable.ID as NullID
FROM   LeftTable LEFT JOIN RightTable ON RightTable.ID = LeftTable.ID
WHERE  RightTable.ID IS NULL