How to change default Null value after joining two tables?

user_12845271 0
Viewed 225

In MySQL workbench, I want to left join two tables, like the following example:

table_a:
---------------------------------------
employee_number   name    e_mail
---------------------------------------
1                 Tom     tom@mail.com
2                 Ali     ali@mail.com
3                 Anna    anna@mail.com
4                 Clay    clay@mail.com
---------------------------------------

table_b:
--------------------------
employee_number   phone
2                 45678964
3                 79845275
--------------------------

I create a new table, table_c and join the two tables:

#create table_c:
CREATE TABLE table_c (
  employye_number VARCHAR(255) NOT NULL,
  name VARCHAR(255) DEFAULT '',
  e_mail VARCHAR(255) DEFAULT '',
  phone VARCHAR(255) DEFAULT ''
);

#perform join of the two tables:
INSERT INTO table_c
SELECT *
FROM table_a
LEFT JOIN table_b USING (employee_number);

The desired output is as follows. With an empty string ('') as the empty value, not NULL.

table_c:
----------------------------------------------------
employee_number   name    e_mail          phone
----------------------------------------------------
1                 Tom     tom@mail.com
2                 Ali     ali@mail.com    45678964
3                 Anna    anna@mail.com   79845275
4                 Clay    clay@mail.com
---------------------------------------------------

However, the phone values of employee 1 and 4 is still NULL when I do this in MySQL.

I do not want to just change the output value in a query using COALESE(), I want the value to be stored in the table as '', not NULL.

Does anyone know how to change the NULL values into '' in table_c?

sql-insert mysql-workbench left-join mysql sql
2 Answers

Consider:

INSERT INTO table_c(employee_number, name, email, phone)
SELECT 
    a.employee_number,
    a.name,
    a.email,
    COALESCE(b.phone, '')
FROM table_a a
LEFT JOIN table_b b USING (employee_number);

Notes:

  • COALESCE() turns null values to the value given as second argument (here, you want the empty string)

  • you should really enumerate the columns that you want to select rather than using select *

  • I would also recommend explicly listing the columns for insert

user_106767160

Use COALESCE() :

COALESCE(phone, '')

Note : Always qualify all column names while using INSERT INTO

INSERT INTO table_c (employee_number, name, email, phone)
   SELECT a.employee_number, a.name, a.email, COALESCE(b.phone, '') AS phone
   FROM table_a a LEFT JOIN 
        table_b b 
        USING (employee_number);
user_55294450
Related