SQL复合索引查询

Question

假设我有一个表items，其中列为id type number room，id是主键，(type, number)是唯一的复合键；具有item_type、item_number、owner、id是主键，(type, number)的表inventory是唯一的复合键。

示例：

items

| id | type    | number | room |
+----+---------+--------+------+
|  1 |  laptop |      1 |   12 |
|  2 |  laptop |      2 |   13 |
|  3 | desktop |      1 |   13 |

inventory

| id | item_type | item_number | owner |
+----+-----------+-------------+-------+
|  1 |    laptop |           1 |   Joe |
|  2 |    laptop |           2 |   Joe |
|  3 |   desktop |           1 | Susan |

如何查询乔拥有的所有items？如果我做了

SELECT *
FROM items
WHERE (type, number) IN (
    SELECT item_type, item_number FROM inventory WHERE owner = 'Joe'
)

结果中只有一行，尽管子查询返回多行。我似乎也不能对多列执行join，例如

SELECT *
FROM items
JOIN inventory ON inventory.item_type = items.type,
    inventory.item_number = items.number`
WHERE inventory.owner = 'Joe'

Answer 1

您应该将联接条件与AND结合起来，而不是用逗号。