心得
這題要show出沒有訂過東西的客戶,所以可以使用LEFT JOIN
來關聯兩張表。
問題
Suppose that a website contains two tables, the
Customers
table and theOrders
table. Write a SQL query to find all customers who never order anything.Table:
Customers
.
12345678 +----+-------+| Id | Name |+----+-------+| 1 | Joe || 2 | Henry || 3 | Sam || 4 | Max |+----+-------+Table:
Orders
.
123456 +----+------------+| Id | CustomerId |+----+------------+| 1 | 3 || 2 | 1 |+----+------------+Using the above tables as example, return the following:
123456 +-----------+| Customers |+-----------+| Henry || Max |+-----------+
答案
LEFT JOIN
123456# Write your MySQL query statement belowSELECT a.Name AS CustomersFROM Customers AS aLEFT JOIN Orders AS bON a.Id = b.CustomerIdWHERE b.CustomerId IS NULLSub Query
123456# Write your MySQL query statement belowSELECT a.Name AS CustomersFROM Customers AS aWHERE ( SELECT COUNT(1)FROM Orders AS zWHERE z.CustomerId = a.Id) = 0