You may want to employ
LEFT JOIN and
SELECT Persons.LastName, Persons.FirstName
LEFT JOIN Orders ON Persons.id = Orders.Person_id
WHERE Orders.Person_id IS NULL;
The result of a left join constantly holds all records of the "left" table (Persons), even in case the join-condition does not trace any matching record in the "right" table (Orders). At the time there is no match, the columns of the "right" table will
NULL in the result set.
This must work. theres more than one method to do it.
select * from persons where person.id not in (select person_id from orders)
Only for fulfillment, here is the
not exists version:
select * from persons p
where not exists
(select null from orders o where o.person_id = p.id)
Approach: Employing sub-query and
NOT IN clause
In case we have a list of customers who have ever ordered, it will be facile to know who never ordered.
We can employ the following code to obtain such list.
select customerid from orders;
Then, we can employ
NOT IN to query the customers who are not in this list.
select customers.name as 'Customers'
where customers.id not in
select customerid from orders
One of the most usual solutions to this issue is by employing the SQL JOIN clause. You can employ the LEFT OUTER JOIN to solve this issue, as displayed below:
SELECT C.Name FROM Customers C LEFT JOIN Orders O ON C.Id = O.CustomerId WHERE O.CustomerId is NULL
Read more: https://javarevisited.blogspot.com/2016/01/sql-exists-example-customers-who-never-ordered.html#ixzz6O2CbX1wD
At the time you link two tables in SQL employing a LEFT OUTER JOIN, then a big table will be made with NULL values in the column which don't subsists in another table.
For example, the big table will have four columns C.Id, C.Name, O.Id, and O.CustomerId, for Customers who have never ordered anything, the O.CustomerId will be NULL.
Many programmers practice the mistake of employing != in JOIN condition to resolve this problem, with the assumption that if = returns matching rows, then != will return those ids which are not subsistent in another table.
In case you struggle to perceive join, then I point out you take a look at Jose Portilla's fantastic SQL course The Complete SQL Bootcamp on Udemy. His teaching method is amazing and you will perceive joins in no time.
Anyhow, this issue is really a fantastic example of how and when to employ EXISTS clause:
SELECT C.Name FROM Customers C WHERE NOT EXISTS (SELECT 1 FROM Orders O WHERE C.Id = O.CustomerId)
Read more: https://javarevisited.blogspot.com/2016/01/sql-exists-example-customers-who-never-ordered.html#ixzz6O2DCsNKN
This is a correlated subquery, where the inner query will execute for every row of the outer query, and just those customers will be returned who have not ordered anything.
However, the most easy solution is by using NOT IN Clause.
SELECT A.Name FROM Customers A WHERE A.Id NOT IN (SELECT B.CustomerId FROMs Orders B)
Read more: https://javarevisited.blogspot.com/2016/01/sql-exists-example-customers-who-never-ordered.html#ixzz6O2DQWRKz