Write an SQL query to report all customers who
never order anything. Return the result table in any order.
table name : Customers
table name: Orders
If you want to solve this question very easily
make sure to read this article first
SQL Join
Question Explanation: We need to fetch all
customer_id which is only present in customer table. Those customer_id which is
also there in order_tbl or which is both in tables are present should be
removed.
Input:
Solution Explanation:
Using left join get all customer_ids from customer table.
select * from Customers as c
left join Orders as o on c.id = o.customerId
Left Join: Left join is going to return all the rows of
customer_id column which is present in left table or customer_tbl. It doesn't
matter the mattched customer_id is present in order_tbl(table2) or not. If it
is present in order_tbl then it will
fetched the details like (order_id,customer_id). And if it is not present in
order_tbl then values like(order_id, customer_id) will be Null as output.
This basically means, order_id and customer_id is null from order_tbl then this customer doesn't have any matching rows which means he/she haven't ordered anything yet!
Use where clause to select those customer ids
whose customerid and orderid values are null.
select c.name as customers from customers as c
left join orders as o on c.id = o.customerId
where o.id is null
Output:
SQL Script:
CREATE TABLE [dbo].[Customers](
[id][int] NOT NULL,
[name][varchar](50) NULL,
CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED
(
[id]
ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Orders](
[id][int] NOT NULL,
[customerId][int] NULL,
CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED
(
[id]
ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[Customers] ([id], [name]) VALUES (1, N'Joe')
GO
INSERT [dbo].[Customers] ([id], [name]) VALUES (2, N'Henry')
GO
INSERT [dbo].[Customers] ([id], [name]) VALUES (3, N'Sam')
GO
INSERT [dbo].[Customers] ([id], [name]) VALUES (4, N'Max')
GO
INSERT [dbo].[Orders] ([id], [customerId]) VALUES (1, 3)
GO
INSERT [dbo].[Orders] ([id], [customerId]) VALUES (2, 1)
GO