Samuel Robert Samuel Robert - 2 months ago 8
MySQL Question

Comparing NULL values with empty string in mysql

I've 2 tables namely table_1 and table_2. table_1 has the following details

+------+--------+-------+--------+
| id | col_1 | col_2 | col_3 |
+------+--------+-------+--------+
| 100 | red | | yellow |
| 101 | | black | white |
| 102 | orange | black | white |
+------+--------+-------+--------+


table_2 has the following details

+-------+-------+--------+
| col_1 | col_2 | col_3 |
+-------+-------+--------+
| red | NULL | yellow |
| NULL | black | white |
+-------+-------+--------+


Now, how do I find out the ids of the table_1 which have the entries in the table_2. In the above example, I need to get answers as 100 and 101.

I tried out couple of join queries with this 2 tables, but the problem was equating the NULL and empty string in the queries. Is there a way to equate them? Or can I use views to convert the table_1 empty string to NULL and compare the view and table_2?

Answer

You can use COALESCE:

SELECT t1.*
FROM table_1 t1
JOIN table_2 t2 
   ON COALESCE(t1.col1, '') = COALESCE(t2.col1, '') AND
      COALESCE(t1.col2, '') = COALESCE(t2.col2, '') AND
      COALESCE(t1.col3, '') = COALESCE(t2.col3, '')
Comments