Write an SQL query to report all customers who never order anything.
Return the result table in any order.
Table: Customers
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| name | varchar |
+-------------+---------+
id is the primary key column for this table.
Each row of this table indicates the ID and name of a customer.
Table: Orders
+-------------+------+
| Column Name | Type |
+-------------+------+
| id | int |
| customerId | int |
+-------------+------+
id is the primary key column for this table.
customerId is a foreign key of the ID from the Customers table.
Each row of this table indicates the ID of an order and the ID of the customer who ordered it.
The query result format is in the following example.
Example 1:
Input:
Customers table:
+----+-------+
| id | name |
+----+-------+
| 1 | Joe |
| 2 | Henry |
| 3 | Sam |
| 4 | Max |
+----+-------+
Orders table:
+----+------------+
| id | customerId |
+----+------------+
| 1 | 3 |
| 2 | 1 |
+----+------------+
Output:
+-----------+
| Customers |
+-----------+
| Henry |
| Max |
+-----------+
Customers Who Never Order Sql
SELECT name As Customers FROM Customers
WHERE id NOT IN(SELECT customerId FROM Orders)
Explanation:
- The "SELECT" keyword is used to specify the columns that you want to retrieve from the table.
- "name" is the name of the column in the "Customers" table that you want to retrieve.
- "AS Customers" renames the output column to "Customers" for readability.
- "FROM Customers" specifies the table that you want to retrieve the data from.
- "WHERE" is used to filter the data based on a condition.
- "id NOT IN(SELECT customerId FROM Orders)" is a subquery that selects the customerIds from the "Orders" table, and the "NOT IN" operator is used to select customers who do not have an orderId in the "Orders" table.
Disclaimer: The above Problem is generated by Leetcode but the Solution is provided by ShouterFolk
