Delete rows with null values mysql-CodePudding

I want to delete the rows with null values in the column
How can i delete it?

SELECT employee.Name, 
      `department`.NUM, 
       SALARY  
FROM employee  
LEFT JOIN `department`  ON employee.ID = `department`.ID  
ORDER BY NUM;

 -------------------- ------- ---------- 
|  Name              | NUM   | SALARY   |
 -------------------- ------- ---------- 
|       Gallegos     |  NULL |     NULL |
|       Lara         |  NULL |     NULL |
|       Kent         |  NULL |     NULL |
|       Lena         |  NULL |     NULL |
|       Flores       |  NULL |     NULL |
|       Alexandra    |  NULL |     NULL |
|       Hodge        |  8001 |   973.45 |
 -------------------- ------- ----------

Should be like this

 -------------------- ------- ---------- 
|  Name              | NUM   | SALARY   |
 -------------------- ------- ---------- 
|                    |       |          |
|       Hodge        |  8001 |   973.45 |
 -------------------- ------- ----------

CodePudding user response：

If by deleting you mean that you don't want to see rows with null values in your table, you can use inner join instead of left join.

CodePudding user response：

You are asking to delete, but to me it seems more like removing nulls from the result of select statement, if so use:

SELECT employee.Name, 
      `department`.NUM, 
       SALARY  
FROM employee  
LEFT JOIN `department`  ON employee.ID = `department`.ID 
WHERE (`department`.NUM IS NOT NULL AND SALARY IS NOT NULL) 
ORDER BY NUM;

Note: The parentheses are not required but it’s good practice to enclose grouped comparators for better readability.

The above query will exclude the even if the NUM column is not null and the SALARY column is null and vice versa