LEETCODE database 183. Customers Who Never Order

      创建2张表  Customer and Order

Create Table Customers(
Id int not null,
Name varchar(90) not null
);

Create Table Orders(
Id int not null,
CustomerId int not null
);

插入的数据如下

+----+-------+
| Id | Name  |
+----+-------+
| 1  | Joe   |
| 2  | Henry |
| 3  | Sam   |
| 4  | Max   |
+----+-------+

+----+------------+
| Id | CustomerId |
+----+------------+
| 1  | 3          |
| 2  | 1          |
+----+------------+

值得注意的是 SELECT NAME FROM CUSTOMS,Orders 出来的结果是

+-------+
| NAME  |
+-------+
| Joe   |
| Joe   |
| Henry |
| Henry |
| Sam   |
| Sam   |
| Max   |
| Max   |
+-------+

并不是只有一行Name 相当于把2个表给相乘了起来 order表里面只有2个数据 所以是name*2  如果是3个数据就是name*3 也就是name会出现3次

INSERT INTO ORDER VALUE(3,4);

SELECT出来的结果是

mysql> select * from customers,orders;
+----+-------+----+------------+
| Id | Name  | Id | CustomerId |
+----+-------+----+------------+
|  1 | Joe   |  1 |          3 |
|  1 | Joe   |  2 |          1 |
|  1 | Joe   |  3 |          4 |
|  2 | Henry |  1 |          3 |
|  2 | Henry |  2 |          1 |
|  2 | Henry |  3 |          4 |
|  3 | Sam   |  1 |          3 |
|  3 | Sam   |  2 |          1 |
|  3 | Sam   |  3 |          4 |
|  4 | Max   |  1 |          3 |
|  4 | Max   |  2 |          1 |
|  4 | Max   |  3 |          4 |
+----+-------+----+------------+

所以我之前的做法

SELECT NAME FROM Customers,Orders
WHERE Customers.Id!=Orders.CustomerId; 是错误的

应该是

SELECT NAME FROM CUSTOMERS c WHERE c.Id
NOT IN(SELECT CustomerId FROM Orders o)