title: MySQL: null becomes 0 in INSERT and SELECT statement
body: <p>I am trying to do an INSERT statement using SELECT query statement into a table with two columns (item_id that is not null and price that can be null). The item_id is not auto-increment and I would do a select query from another table. So an example would be:</p>
  
  <pre><code>INSERT INTO myTable 
  SELECT ((SELECT id FROM myCatalogue where name="Item X"), "500");
  </code></pre>
  
  <p>By doing just the SELECT statement alone, if Item X does not exist in list, the query would return:</p>
  
  <pre>
  | SELECT id FROM myCatalogue where name="Item X" | 500 |
  | NULL | 500 |
  </pre>
  
  <p>However, when the whole INSERT + SELECT statement, the following is inserted into 'myTable' instead:</p>
  
  <pre>
  | item_id | price |
  | 0 | 500 |
  </pre>
  
  <p>I want MySQL to prompt me an error when item does not exist in 'myCatalogue', such as 'Cannot insert the value NULL in column item_id' so i can go back to 'myCatalogue' to verify if this item exists. However it automatically change the value from NULL to 0 and inserts successfully. Hence I would like to ask how do I need to modify the statement such that it does not change NULL to 0?</p>
  
  <p>This is my table definition:</p>
  
  <pre><code>CREATE TABLE myTable 
  ( 
      item_id int(11) unsigned NOT NULL, 
      price float unsigned DEFAULT NULL, 
      PRIMARY KEY (item_id) 
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1 
  </code></pre>