Are there any other ways to get results from Two tables without using Sub Query

Question

I have a Customer table

+--------+---------+
| Id     |  Name   |
+--------+---------+
| 1      |   A     |
| 2      |   b     |
| 3      |   c     |
| 4      |   d     |
| 5      |   3     |
| 6      |   f     |
| 7      |   g     |
+--------+---------+

and an order table

+-----+------+--------------------------+
| ID  | C_Id | OrderDate                |
+-----+------+--------------------------+
| 1   | 1    |  2017-05-12 00:00:00.000 |
| 2   | 2    |  2017-12-12 00:00:00.000 |
| 3   | 3    |  2017-11-12 00:00:00.000 |
| 4   | 4    |  2017-12-12 00:00:00.000 |
| 5   | 1    |  2017-12-12 00:00:00.000 |
| 6   | 2    |  2017-12-12 00:00:00.000 |
| 7   | 3    |  2017-12-12 00:00:00.000 |
| 8   | 4    |  2017-11-12 00:00:00.000 |
| 9   | 2    |  2017-06-12 00:00:00.000 |
| 10  | 3    |  2017-07-12 00:00:00.000 |
+-----+------+--------------------------+

I need the result of orders with customers who did not buy in last month. That is from the order table Customer 3 and 4 have purchased in last month(November). The result should not include customer 3 and 4 even they had purchase in earlier months.

I have this query which returns the result perfectly.

SELECT C_ID , MONTH(OrderDate) from [Order] WHERE MONTH(OrderDate) <> MONTH(GETDATE()) - 1 AND C_ID NOT IN (SELECT C_ID FROM [Order] WHERE MONTH(OrderDate) = MONTH(GETDATE()) - 1)

Can anyone help me to write this query without using subquery

For better clarity, assume the Orders table contains current year orders only and I need to exclude the customers from the result(get all orders for current year) if they had any purchase in November. Which means if the customer John has an order in April, June and November the result(orders with customer name) should not include the customer John and all his orders as he made a purchase in November..

For learning purpose I need to know how can we achieve the above in other ways without using sub query.

Answer 1

I need the result of the customers who did not buy in last month.

That is from the order table Customer 3 and 4 have purchased in last month(November). The result should not include customer 3 and 4 even they had purchase in earlier months.

I am a bit confused with your question.

If you want to see customers who did not purchase last month (in this case, November) Then below query should be able to handle it.

SELECT DISTINCT
    c.id,
    c.name 
FROM #Customer c
LEFT JOIN #Orders o ON c.id = o.c_id AND MONTH(o.OrderDate) = MONTH(GETDATE()) - 1
WHERE o.c_id IS NULL

If you want to see customers who only purchased last month (in this case, November) Then below query should be able to handle it.

 SELECT DISTINCT
        c.id,
        c.name 
    FROM #Customer c
    INNER JOIN #Orders o ON c.id = o.c_id AND MONTH(o.OrderDate) = MONTH(GETDATE()) - 1