Question: I wanted to run a simple query to throw up all the rows of `Table1` where a principal column value is not present in a column in another table (`Table2`).

I tried using:

```
SELECT * FROM Table1 WHERE Table1.principal NOT IN Table2.principal

```

This is instead throwing a syntax error. Google search led me to forums where people were saying that MySQL does not support `NOT IN` and something extremely complex needs to be used. Is this true? Or am I making a horrendous mistake?

Answer: 
The subquery option has already been answered, but note that in many cases a `LEFT JOIN` can be a faster way to do this:

```
SELECT table1.*
FROM table1 LEFT JOIN table2 ON table2.principal=table1.principal
WHERE table2.principal IS NULL

```

If you want to check multiple tables to make sure it's not present in any of the tables (like in SRKR's comment), you can use this:

```
SELECT table1.*
FROM table1
LEFT JOIN table2 ON table2.name=table1.name
LEFT JOIN table3 ON table3.name=table1.name
WHERE table2.name IS NULL AND table3.name IS NULL

```
Unfortunately it seems to be a issue with MySql usage of "NOT IN" clause, the screen-shoot below shows the sub-query option returning wrong results:

```
mysql> show variables like '%version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 1.1.8                        |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| version                 | 5.5.21                       |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Linux                        |
+-------------------------+------------------------------+
7 rows in set (0.07 sec)

mysql> select count(*) from TABLE_A where TABLE_A.Pkey not in (select distinct TABLE_B.Fkey from TABLE_B );
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.07 sec)

mysql> select count(*) from TABLE_A left join TABLE_B on TABLE_A.Pkey = TABLE_B.Fkey where TABLE_B.Pkey is null;
+----------+
| count(*) |
+----------+
|      139 |
+----------+
1 row in set (0.06 sec)

mysql> select count(*) from TABLE_A where NOT EXISTS (select * FROM TABLE_B WHERE TABLE_B.Fkey = TABLE_A.Pkey );
+----------+
| count(*) |
+----------+
|      139 |
+----------+
1 row in set (0.06 sec)

mysql> 

```