mysql - MySQL 中的内部连接

Question

有我的两张表:

表名:FRIENDS

+-------+---------+---------+-------------------+
|  id   |  firstName   |  lastName  | city      |
+-------+--------------+------------+-----------+
| 1     | dudi         | edri       | london    |
| 2     | maor         | azulay     | madrid    |
| 3     | batel        | azulay     | tel aviv  |
| 4     | nir          | cohen      | barcelona |
| 5     | evia         | perez      | miami     |
| 6     | neria        | perez      | new-york  | 
| 7     | nevo         | kakoun     | roma      |
+-------+---------+---------+-------------------+

表名:ORDERS

+-------+---------+---------+-----------------+
|  id   |  firstName   |  amount  | status    |
+-------+--------------+----------+-----------+
| 1     | dudi         | 5684     | shipped   |
| 2     | maor         | 4896     | shipped   |
| 3     | batel        | 2496     | delay     |
+-------+--------------+----------+-----------+

我的问题是:我想要没有订单的 friend 。答案:

| 4     | nir          | cohen      | barcelona |
| 5     | evia         | perez      | miami     |
| 6     | neria        | perez      | new-york  | 
| 7     | nevo         | kakoun     | roma      |
+-------+---------+---------+-------------------+

我是如何使用内连接编写查询的。谢谢。

Answer 1

您不应该在 ORDERS 中使用 firstName 作为外键。外键应该引用主键。使用 FRIENDS 的 ID，例如:

表名:ORDERS

+-------+---------+---------+-----------------+
|  id   |  friendID    |  amount  | status    |
+-------+--------------+----------+-----------+
| 1     | 1            | 5684     | shipped   |
| 2     | 2            | 4896     | shipped   |
| 3     | 3            | 2496     | delay     |
+-------+--------------+----------+-----------+

并使用 LEFT OUTER JOIN 进行查询:

SELECT f.*
FROM FRIENDS f
LEFT JOIN  ORDERS o
  ON f.ID = o.friendID
WHERE o.ID IS NULL;

LiveDemo

另一种可能性是使用相关子查询:

SELECT f.*
FROM FRIENDS f
WHERE NOT EXISTS (SELECT 1
                  FROM orders o
                  WHERE o.friendID = f.ID);

LiveDemo2

---

表格:

CREATE TABLE friends(
  id        INTEGER  NOT NULL PRIMARY KEY     -- you can add AUTO_INCREMENT if needed
   ...
);

CREATE TABLE orders(
  id       INTEGER  NOT NULL PRIMARY KEY     
  ,friendID INTEGER  NOT NULL
  ,FOREIGN KEY (friendID) REFERENCES friends(id)
  ...
);