NULL value

In SQL, Null  means nothing at all. This is not 0, it's NULL. When a field is 0, it is included in calculations, but when it's NULL, it's completely excluded from calculations.

 

If you want to exclude Null in the result, you can use Where and is not null query.

 

select a.order_id,
       a.customer_id,
       a.restaurant_name,
       a.price,
       b.name,
       b.age,
       b.gender
from food_orders a left join customers b on a.customer_id=b.customer_id
where b.customer_id is not null

 

 

 

Using the query "where b.customer_id is not null" to remove null values results in the same output as the query using Inner join.

 

 

The keyword used to replace NULL values is "COALESCE."

 

select a.order_id,
       a.customer_id,
       a.restaurant_name,
       a.price,
       b.name,
       b.age,
       coalesce(b.age, 20) "null 제거",
       b.gender
from food_orders a left join customers b on a.customer_id=b.customer_id
where b.customer_id is null

 

 

Result

 

 

I have another question. Is there a way to change null to another number without using coalesce?

 

Answer

 -> It's also possible to change null values using the case function, as shown below.

 

SELECT 
  CASE 
    WHEN column_name IS NULL THEN replacement_value
    ELSE column_name 
  END AS new_column_name
FROM your_table;