CODEWITHSUNDEEP
mysqlsqlsubqueryexists
Frightlin
Frightlin

Reputation: 161

How to use the exists operator properly in mysql?

I want to the EXISTS operator to list the customer names who have no orders

So I have two Tables (as shown below) One named Customer which holds CustomerName and CustomerID and another named orders which hold CustomerID as well as OrderID. I was trying to

SELECT CustomerName, CustomerID FROM Customer WHERE EXISTS (

SELECT CustomerID, OrderID FROM orders WHERE OrderID <> CustomerID );

but it's not working the way I wanted it to.

Upvotes: 0

Views: 95

Answers (2)

user2781942
user2781942

Reputation: 105

"NOT EXISTS" is a notoriously poor performer (unless MySQL has come up with some kind of optimization for it) because if forces a full-table scan.

The standard workaround is to use outer joins. Not intuitive, so it should be commented for future maintainers.

SELECT CustomerName, CustomerID from Customer C LEFT JOIN Orders O ON C.CustomerID = O.CustomerID WHERE O.CustomerID IS NULL

Upvotes: 1

cbarklow
cbarklow

Reputation: 26

One way you can do this is by using the NOT EXISTS syntax. Personally, it makes more sense to me. I think this query would work for you: 

Select CustomerName
, CustomerID 
FROM Customer C 
WHERE NOT EXISTS(Select 1 FROM Orders O where C.CustomerId = O.CustomerId)

Upvotes: 1

Related Questions