Search code examples
mysqlselectnullcreate-table

CREATE TABLE... SELECT combining two MySQL tables and NULL values

I'm using this to join two tables (leaving out some of the fields here). All of the fields in the original tables are defined as VARCHAR, NOT NULL, with default ''.

  CREATE TABLE newtable
  SELECT
     table1.field1,
     table1.field2,
     ...
     table2.field1,
     table2.field2,
     ...
 FROM
    table1
 LEFT JOIN table2 ON table1.id = table2.id

The join itself is working, however, table 1 has some rows which are not in table 2. The fields from table 2 are being added as NULL for those entries, when I need them to be empty strings. Is there a way to do that when creating the table?

Solution 
  • IFNULL(table1.field1,'') AS table1_field1,

    IFNULL leaves the value if it is not null otherwise it replaces it with the second parameter, empty string in your case