Suppose that a website contains two tables, the Customers
table and the Orders
table. Write a SQL query to find all customers who never order anything.
Table: Customers
.
+----+-------+ | Id | Name | +----+-------+ | 1 | Joe | | 2 | Henry | | 3 | Sam | | 4 | Max | +----+-------+
Table: Orders
.
+----+------------+ | Id | CustomerId | +----+------------+ | 1 | 3 | | 2 | 1 | +----+------------+
Using the above tables as example, return the following:
+-----------+ | Customers | +-----------+ | Henry | | Max | +-----------+
自己的代码:
Runtime: 276 ms, faster than 27.78% of MySQL online submissions for Customers Who Never Order.
SELECT
Name Customers
FROM
Customers
WHERE
id NOT IN (
SELECT
t1.Id
FROM
Customers t1,
Orders t2
WHERE
t1.Id = t2.CustomerId
)
小结:
1)not in 的用法
2)这种嵌套查询的方式
别人的代码:
Runtime: 264 ms, faster than 48.44% of MySQL online submissions for Customers Who Never Order.
SELECT A.Name from Customers A
LEFT JOIN Orders B on a.Id = B.CustomerId
WHERE b.CustomerId is NULL
小结:
1)左连接的用法与特点:右表无法匹配左表的那些记录行,会被置为NULL
Runtime: 249 ms, faster than 74.55% of MySQL online submissions for Customers Who Never Order.
SELECT A.Name from Customers A
WHERE A.Id NOT IN (SELECT B.CustomerId from Orders B)
小结:
使用这种方式,根本没有必要进行表连接,就可获得答案;