CODEWITHSUNDEEP
mysqlsql
anti-k
anti-k

Reputation: 317

MySQL query does not return needed result. Customers who does not have any orders, does not show

Im still doing my SQL training. So I have two tables CUSTOMERS and ORDERS( foreign key table). Now I need to get all customers without any ORDERS. Here is my query

select LASTNAME, FIRSTNAME 
  from CUSTOMERS 
  WHERE CUSTOMERS.ID NOT IN 
      (select ID_CUSTOMER from ORDERS);

+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| ID        | int(11)      | NO   | PRI | NULL    |       |
| FIRSTNAME | varchar(50)  | YES  |     | NULL    |       |
| LASTNAME  | varchar(50)  | YES  |     | NULL    |       |
| ADDRESS   | varchar(100) | YES  |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+


+---------------+--------------+------+-----+---------+-------+
| Field         | Type         | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| ID            | int(11)      | NO   | PRI | NULL    |       |
| PRODUCT_NAME  | varchar(100) | YES  |     | NULL    |       |
| PRODUCT_PRICE | double(10,4) | YES  |     | NULL    |       |
| DATE_ORDER    | date         | YES  |     | NULL    |       |
| ID_CUSTOMER   | int(11)      | YES  |     | NULL    |       |
| AMOUNT        | int(11)      | YES  |     | NULL    |       |
+---------------+--------------+------+-----+---------+-------+

But I do recieve Empty set (0,00 sec)/ But Im sure about that those are present;

Upvotes: 1

Views: 55

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93694

I suspect there are some NULL values in ID_CUSTOMER column of ORDERS table. NOT IN fails when there is a NULL value present in sub-query.

Use NOT EXISTS 

select LASTNAME, FIRSTNAME 
  from CUSTOMERS C
  WHERE NOT EXISTS (select ID_CUSTOMER from ORDERS O Where C.ID = O.ID_CUSTOMER );

Upvotes: 4

Related Questions