How to show only the left part of the table(Not intersecting section) in Left Join in sql?

Question

I am new to sql and practising from w3schools. https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_join_left

In venn diagram,it has shown the different types of Join.But,I am trying to get this part of venn diagram.

So,I tried this way using subquery.:

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders
WHERE CUSTOMERS.CUSTOMERID NOT IN (SELECT CUSTOMERID FROM ORDERS);

But,is it possible to get only the left part of the venn diagram(Not the intersecting data) without using subqueries and not using "In" statements?If,it is possible then,please share me the solution.

Answer 1

All those records from customers that have no orders:

SELECT *
FROM Customers c
LEFT JOIN Orders o ON o.CustomerId = c.CustomerId
WHERE o.CustomerId IS NULL 

Not really sure I would represent your customers orders relationship like that vent diagram because it implies to me that there can be orders that have no customers but I'll overlook that

When we left join we get all the customers plus any orders they have made. Customers that have made no orders have a null in the related o.CustomerId and that is what we look for in the where clause

It is most reliable to use (one of) the column(s) specified in the join condition when doing this test. Any other column from orders might be null for other reasons (product type not known, for example) unless it is specified as NOT NULL in the table definition. To save looking this up we rely on the fact that the only way o.CustomerId can be null in this particular query (where it is mentioned in the join) is if there is no matching order row for that customer

You could also use either these:

SELECT *
FROM Customers c
WHERE c.CustomerId NOT IN (SELECT CustomerId FROM orders)

SELECT *
FROM Customers c
WHERE NOT EXISTS (SELECT null FROM orders o WHERE o.CustomerID = c.CustomerID)

In most high end database systems these will all be implemented the same under the hood; the query compiler will recognise the job they're trying to do and carry them out in the same way. There's a risk that the NOT IN will perform poorly in some databases, particularly older or more naively written ones, and it's perhaps a reasonable rule of thumb to follow that "do not use IN for lists longer than you would happily type in manually". The EXISTS version is called a coordinated subquery and is often a fairly succinct and high performing way of doing things like this- for a long time databases have had specific optimisations for EXISTS that they formerly might not have had for a JOIN based route but that's again something that has largely gone away nowadays. Seeing someone exhibit a preference for EXISTS may indicate they've been using SQL a loooong time, as it was frequently the best performing way of answering this type of query in ancient databases

Of all 3 I find the join method clearest to read and understand - coordinated subqueries always require a bit more mental effort to see how they hook into the bigger picture because they refer to columns that aren't part of their local scope. It's also possible to make a mistake with a coordinated subquery more easily than the other forms, and particularly with the IN, by typoing a column from the outer query into the inner query, changing the results it emits.

Use whatever works for you; being able to read and understand all these forms will help you when you read other people's code

Answer 2

Below query will only select Customers that don't have Orders as per requirements.

SELECT c.CustomerName, o.OrderID
FROM Customers c
LEFT JOIN Orders o on o.CUSTOMERID = c.CUSTOMERID
WHERE o.OrderID IS NULL

Customer  Orders
1         1
2         null
3         2

should only give

Customer   Orders
2          null

Answer 3

If you are looking for customers who have not ordered, their order id will be null anyway (this is what your Venn diagram says):

SELECT Customers.CustomerName
FROM Customers
WHERE CUSTOMERS.CUSTOMERID NOT IN (SELECT CUSTOMERID FROM ORDERS);

Answer 4

You need to use EXCEPT clause. Also you need to define which tables to join.

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders on Orders.CustomerID=Customers.CustomerID

EXCEPT

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders on Orders.CustomerID=Customers.CostomerID
WHERE CUSTOMERS.CUSTOMERID NOT IN (SELECT CUSTOMERID FROM ORDERS);    

Answer 5

If a Customer is not having Orders, you will not have OrderID. You can check like below:

SELECT Customers.CustomerName, NULL AS OrderID
FROM Customers AS C
WHERE NOT EXISTS (
SELECT CUSTOMERID FROM ORDERS AS O WHERE O.CustomerID = C.CustomerID);

You can also use SET operator MINUS(ORACLE), EXCEPT(SQL Server) to perform this

SELECT CustomerID, NULL AS OrderID
FROM
(SELECT CustomerID FROM Customers 
MINUS
SELECT CustomerID FROM Orders)