php - MYSQL - 获取没有推荐人的记录

Question

我有两张 table

table users - id, username, ref_id
table ref - id, name

如何获取没有 ref 的 ID？ (ref_id == NULL)

示例数据:

table users:
1, admin, 1
2, mike, NULL

table ref:
1, test
2, tester
3, nick

结果:2,3(免费 ID)

Answer 1

SELECT r.id AS id
 FROM users AS u
RIGHT JOIN ref as r
 ON ( u.ref_id = r.id )
WHERE u.ref_id IS NULL