Customer and Order Sql Statement

TSQL query to select all records from Customer that has an Order and also select all records from customer that does not have an Order. The table Customer contains a primar开发者_开发技巧y key of CustomerID. The table Order contains a primary key of OrderID and a foreign key of CustomerID.

Something like

Select yourcustomerfields, yourorderfields
From Customer
Left join Orders on Customer.OrderID = Orders.OrderID

I came up with this solutions.

Select CustomerName
from Customer
Where pk_CustomerID IN
(
Select fk_CustomerID from Orders
INNER JOIN Customer
on Customer.pk_CustomerID=Orders.fk_CustomerID)

/* NOT IN instead of IN will give the other customers who doesn't have an Order */