0

I'm trying to SELECT some result related between 2 tables by codes.

**Table_1**
+---------+-------------------------------------+
| code    | description                         |
|---------|-------------------------------------|
| 00001   | Code 00001                          |
| 00002   | Code 00002                          |
| 00003   | Code 00003                          |
| 00004   | Code 00004                          |
+---------+-------------------------------------+

**Table_2**
+---------+---------------+
| id      | code_ref      |
|---------|---------------|
| 1       | 00001         |
| 2       | 00002         |
| 3       |               |
| 4       | NULL          |
+---------+---------------+

I use this query to select the code from Table 1 which doesn't exist in Table 2:

SELECT *
FROM Table_1
WHERE code NOT IN (
   SELECT DISTINCT code_ref
   FROM Table_2
)

With this query it returned empty result. Then i tried to add WHERE code_ref <> '' into the subquery, it can return result properly. I this the problem or some rule about the empty string and null value is different?

Hung Dang
  • 55
  • 5
  • 2
    It's the null. Null is special. Does this answer your question? [NULL values inside NOT IN clause](https://stackoverflow.com/questions/129077/null-values-inside-not-in-clause) – Schwern Oct 14 '21 at 04:18
  • @Schwern, thanks for answer. I mention to the empty string, not null. The NULL value not cause the problem. As you can see the record has id ````3```` in ````Table_2````, if i set value ````code_ref```` of that row to NULL, then the query can return code ````00001```` and ````00002````. If i leave it value is ````''````, the result is nothing. – Hung Dang Oct 14 '21 at 06:12
  • Sorry, I can't replicate that behavior. Whether id 3 is `''` or `null` has no effect. [Here is a demonstration](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=ed8a8723037093f340e71cb0b0e1b7d0). There's nothing special about an empty string, there is something special about null. – Schwern Oct 14 '21 at 06:18
  • 1
    You instead want to use `not exists` [demonstration](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=ca54e8578a9a4e8ac8478ecf798b6875) or a `left outer join` [demonstration](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=20517a9a43fc9f3669e0053528fdb994). [Explanation](https://www.red-gate.com/hub/product-learning/sql-prompt/consider-using-not-exists-instead-not-subquery). – Schwern Oct 14 '21 at 06:26
  • Oh. i see, the problem come from ````NULL```` value also :O – Hung Dang Oct 14 '21 at 06:36
  • @Schwern, thanks so much, i choose to use ````IFNULL(code_ref, '')```` to solve the problem. – Hung Dang Oct 14 '21 at 06:42

0 Answers0