CODEWITHSUNDEEP
mysqlsqljoin
Rick Yoder
Rick Yoder

Reputation: 127

How To Select Records that Don't Exist In a Table in SQL?

I have 2 SQL tables with the following columns:

"users": userid, firstname, lastname

"orders": orderid, userid, orderdate, shippingdate

I've been told I can use a join to achieve what I am looking for, but I don't see how a join would work.

The idea is to select all users from "users" that have NOT placed an order; users that do not have a row in the "orders" table as defined by the userid.

This is all I have so far:

select users.userid, users.firstname, users.lastname, orders.*
from users
join orders on orders.userid = users.userid

But that only returns users who DID place an order. How would I expand upon that to get the users that HAVEN'T placed an order? From what I understand, a join is meant to get MORE information, not the lack thereof.

Some info: I'm using the latest version of HeidiSQL on Windows 10.

Upvotes: 0

Views: 836

Answers (1)

Mithilesh Gupta
Mithilesh Gupta

Reputation: 2930

You don't need a join to do this, Do:

select * from users where userid not in (select userid from orders)

You can use LEFT JOIN also:

SELECT * FROM users
LEFT JOIN orders ON users.userid= orders.userid
WHERE orders.users IS NULL

Upvotes: 2

Related Questions