CODEWITHSUNDEEP
mysqlsqlleft-joinmysql-workbenchsql-insert
malhel
malhel

Reputation: 61

How to change default Null value after joining two tables?

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

table_a:
---------------------------------------
employee_number   name    e_mail
---------------------------------------
1                 Tom     [email protected]
2                 Ali     [email protected]
3                 Anna    [email protected]
4                 Clay    [email protected]
---------------------------------------

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     [email protected]
2                 Ali     [email protected]    45678964
3                 Anna    [email protected]   79845275
4                 Clay    [email protected]
---------------------------------------------------

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?

Upvotes: 2

Views: 680

Answers (2)

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

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);

Upvotes: 0

GMB
GMB

Reputation: 222582

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

Upvotes: 1

Related Questions