Except not working in MySQL?

You cannot use except in MySQL. You can work with NOT IN operator to get the same result. Let us first create a table −

<span class="pln">mysql</span><span class="pun">></span><span class="pln"> create table </span><span class="typ">DemoTable1</span>
<span class="pun"> (</span>
<span class="typ"> Number1</span><span class="pln"> </span><span class="kwd">int</span>
<span class="pun"> );</span>
<span class="typ">Query</span><span class="pln"> OK</span><span class="pun">,</span><span class="pln"> </span><span class="lit">0</span><span class="pln"> rows affected </span><span class="pun">(</span><span class="lit">0.71</span><span class="pln"> sec</span><span class="pun">)</span>

Insert some records in the table using insert command −

<span class="pln">mysql</span><span class="pun">></span><span class="pln"> insert </span><span class="kwd">into</span><span class="pln"> </span><span class="typ">DemoTable1</span><span class="pln"> values</span><span class="pun">(</span><span class="lit">100</span><span class="pun">);</span>
<span class="typ">Query</span><span class="pln"> OK</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> row affected </span><span class="pun">(</span><span class="lit">0.14</span><span class="pln"> sec</span><span class="pun">)</span>

<span class="pln">mysql</span><span class="pun">></span><span class="pln"> insert </span><span class="kwd">into</span><span class="pln"> </span><span class="typ">DemoTable1</span><span class="pln"> values</span><span class="pun">(</span><span class="lit">200</span><span class="pun">);</span>
<span class="typ">Query</span><span class="pln"> OK</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> row affected </span><span class="pun">(</span><span class="lit">0.13</span><span class="pln"> sec</span><span class="pun">)</span>

<span class="pln">mysql</span><span class="pun">></span><span class="pln"> insert </span><span class="kwd">into</span><span class="pln"> </span><span class="typ">DemoTable1</span><span class="pln"> values</span><span class="pun">(</span><span class="lit">300</span><span class="pun">);</span>
<span class="typ">Query</span><span class="pln"> OK</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> row affected </span><span class="pun">(</span><span class="lit">0.13</span><span class="pln"> sec</span><span class="pun">)</span>

Display all records from the table using select statement:

<span class="pln">mysql</span><span class="pun">></span><span class="pln"> </span><span class="kwd">select</span><span class="pln"> </span><span class="pun">*</span><span class="kwd">from</span><span class="pln"> </span><span class="typ">DemoTable1</span>

This will produce the following output −

+---------+
| Number1 |
+---------+
|     100 |
|     200 |
|     300 |
+---------+
3 rows in set (0.00 sec)

Following is the query to create second table −

<span class="pln">mysql</span><span class="pun">></span><span class="pln"> create table </span><span class="typ">DemoTable2</span>
<span class="pun">   (</span>
<span class="typ">   Number1</span><span class="pln"> </span><span class="kwd">int</span>
<span class="pun">   );</span>
<span class="typ">Query</span><span class="pln"> OK</span><span class="pun">,</span><span class="pln"> </span><span class="lit">0</span><span class="pln"> rows affected </span><span class="pun">(</span><span class="lit">0.52</span><span class="pln"> sec</span><span class="pun">)</span>

Insert some records in the table using insert command −

<span class="pln">mysql</span><span class="pun">></span><span class="pln"> insert </span><span class="kwd">into</span><span class="pln"> </span><span class="typ">DemoTable2</span><span class="pln"> values</span><span class="pun">(</span><span class="lit">100</span><span class="pun">);</span>
<span class="typ">Query</span><span class="pln"> OK</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> row affected </span><span class="pun">(</span><span class="lit">0.17</span><span class="pln"> sec</span><span class="pun">)</span>

<span class="pln">mysql</span><span class="pun">></span><span class="pln"> insert </span><span class="kwd">into</span><span class="pln"> </span><span class="typ">DemoTable2</span><span class="pln"> values</span><span class="pun">(</span><span class="lit">400</span><span class="pun">);</span>
<span class="typ">Query</span><span class="pln"> OK</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> row affected </span><span class="pun">(</span><span class="lit">0.14</span><span class="pln"> sec</span><span class="pun">)</span>

<span class="pln">mysql</span><span class="pun">></span><span class="pln"> insert </span><span class="kwd">into</span><span class="pln"> </span><span class="typ">DemoTable2</span><span class="pln"> values</span><span class="pun">(</span><span class="lit">300</span><span class="pun">);</span>
<span class="typ">Query</span><span class="pln"> OK</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> row affected </span><span class="pun">(</span><span class="lit">0.11</span><span class="pln"> sec</span><span class="pun">)</span>

Display all records from the table using select statement −

<span class="pln">mysql</span><span class="pun">></span><span class="pln"> </span><span class="kwd">select</span><span class="pln"> </span><span class="pun">*</span><span class="kwd">from</span><span class="pln"> </span><span class="typ">DemoTable2</span><span class="pun">;</span>

This will produce the following output −

+---------+
| Number1 |
+---------+
|     100 |
|     400 |
|     300 |
+---------+
3 rows in set (0.00 sec)

Following is the query to learn how to use NOT IN operator in place of except −

<span class="pln">mysql</span><span class="pun">></span><span class="pln"> </span><span class="kwd">select</span><span class="pln"> </span><span class="typ">Number1</span><span class="pln"> </span><span class="kwd">from</span><span class="pln"> </span><span class="typ">DemoTable1</span>
<span class="kwd">where</span><span class="pln"> </span><span class="typ">Number1</span><span class="pln"> </span><span class="kwd">not</span><span class="pln"> </span><span class="kwd">in</span><span class="pln"> </span><span class="pun">(</span><span class="pln">SELECT </span><span class="typ">Number1</span><span class="pln"> FROM </span><span class="typ">DemoTable2</span><span class="pun">);</span>

This will produce the following output −

+---------+
| Number1 |
+---------+
|     200 |
+---------+
1 row in set (0.04 sec)