0

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.

Rick Yoder
  • 131
  • 11
  • `left join orders on orders.userid = users.userid where orders.userid is null` – Paul Spiegel Feb 05 '17 at 21:36
  • 1
    Possible duplicate of [SQL - find records from one table which don't exist in another](http://stackoverflow.com/questions/367863/sql-find-records-from-one-table-which-dont-exist-in-another) – Paul Spiegel Feb 05 '17 at 21:38
  • Paul, your solution worked just as Mithilesh's did. The only difference is that the join just gave a bunch of nulled columns. Is there any reason why someone would want to use a join for this? – Rick Yoder Feb 05 '17 at 21:39
  • You are selecting `orders.*` even though you only want entries that have no orders. – Paul Spiegel Feb 05 '17 at 21:41
  • True, but regardless, even though orders.* may not exist, wouldn't SQL just fill in the gaps there and just null everything like with the query you just showed me? – Rick Yoder Feb 05 '17 at 21:43
  • Sorry i don't get your problem. If there are no orders, you just don't select enything from that table. – Paul Spiegel Feb 05 '17 at 21:49
  • It's fine haha. I'm just a beginner with SQL. If you have any good references on it that you personally would recommend, let me know. – Rick Yoder Feb 05 '17 at 21:51
  • [LEFT JOIN and RIGHT JOIN Optimization](https://dev.mysql.com/doc/refman/5.7/en/left-join-optimization.html) "If there is a row in A that matches the WHERE clause, but there is no row in B that matches the ON condition, an extra B row is generated with all columns set to NULL." – Paul Spiegel Feb 05 '17 at 21:55
  • https://dev.mysql.com/doc/refman/5.7/en/rewriting-subqueries.html – Paul Spiegel Feb 05 '17 at 21:59

1 Answers1

2

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
Mithilesh Gupta
  • 2,800
  • 1
  • 17
  • 17
  • But hypothetically, if I needed to use a join, what would that even look like? And what type of join is it? – Rick Yoder Feb 05 '17 at 21:34
  • Thank you! I will accept as best answer to close the question. You and Paul were very helpful! – Rick Yoder Feb 05 '17 at 21:40
  • you can also use **DISTINCT** in the subquery like **(select DISTINCT userid from orders)** . so each userid is only one time in the result. – Bernd Buffen Feb 05 '17 at 21:43