CODEWITHSUNDEEP
mysql
user3675188
user3675188

Reputation: 7409

How could I use join query to find the intersection

I want to find the users who haven't make any order

My idea is that, Get all user ids and substract the unique ids in orders table

How could I convert it in MySQL query syntax.

mysql> select * from users;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

mysql> select * from orders;
+------+---------+--------+
| id   | user_id | amount |
+------+---------+--------+
|    1 |       1 |  10.00 |
|    1 |       2 |   5.00 |
+------+---------+--------+
2 rows in set (0.00 sec)

Upvotes: 1

Views: 35

Answers (2)

1000111
1000111

Reputation: 13519

The idea is to make make a left join between users (left table) and orders table.

Then from this joined table you need to filter those records which don't have any order. So in this case orders.id would be NULL.

SELECT 
users.id
FROM users
LEFT JOIN orders
ON users.id = orders.user_id
WHERE orders.id IS NULL

Visual Understanding:

enter image description here

SQL Joins explained better here.

Upvotes: 1

Richard Theobald
Richard Theobald

Reputation: 777

select * from users where id not in (select user_id from orders)

Upvotes: 0

Related Questions