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.IdNOT IN(SELECT CustomerId FROM Orders o)