sql - 如果条件在两个表的行中匹配，则创建返回 id 的查询

Question

我正在学习 SQL/dbms 并使用 Postgres。我想返回在特定列中都具有特定值的行。例如在表 Carpets 和 Curtains 中，我想获取颜色为 'light yellow'< 的行的 id/。我想为此我需要 JOIN，但不确定是什么类型。

这是我得到的:

SELECT id
  FROM Carpets
  WHERE colour = 'light yellow'
        INNER JOIN Curtains ON Carpets.colour = Curtains.colour;

两个表都有 id 属性。

关于学习JOIN，我应该先学哪一个？如果我尝试一次学习所有这些资源，那我就是搬起石头砸自己的脚(因为不同的资源包含不同的“变体”)。

重要 我一直在寻找一个答案，其中只有当窗帘和地毯都是“浅黄色”时才会返回 id。

Answer 1

已阅读your question在 Meta 上关于这个特定问题，让我解释一下为什么所有三个答案确实都是正确的 - 就像您解决问题的方式一样。

我已经包含了所有三个答案的示例以及他们正在处理的架构:

Database changed
mysql> create table carpet(id int(3), material varchar(10), color varchar(15));
Query OK, 0 rows affected (0.02 sec)

mysql> create table curtain(id int(3), material varchar(10), color varchar(15));
Query OK, 0 rows affected (0.00 sec)

(一堆插入语句)

mysql> select * from carpet;
+------+-----------+--------------+
| id   | material  | color        |
+------+-----------+--------------+
|    1 | wool      | Light Yellow |
|    2 | wool      | Beige        |
|    3 | polyester | Light Yellow |
|    4 | polyester | Light Red    |
+------+-----------+--------------+
4 rows in set (0.00 sec)

mysql> select * from curtain;
+------+----------+--------------+
| id   | material | color        |
+------+----------+--------------+
|    1 | Velvet   | Purple       |
|    2 | cotton   | White        |
|    3 | cotton   | Light Yellow |
|    4 | cotton   | Light Blue   |
+------+----------+--------------+
4 rows in set (0.00 sec)

相交使用两个选择语句并返回匹配结果。在这种情况下，您要查找具有匹配颜色“浅黄色”的所有行。

我不能在 MySQL 中给你一个例子，因为它不支持它(正如你在下面看到的，它不需要给出相同的结果)。

两个 select 语句的联合查询每个都带有一个 where 子句，只允许颜色为“浅黄色”，将返回相同的数据。虽然联合可用于返回不匹配的数据，但每个 select 语句中的 where 子句意味着它只会返回您想要的行。

mysql> select id, material, color from carpet
    -> union 
    -> select id, material, color from curtain;
+------+-----------+--------------+
| id   | material  | color        |
+------+-----------+--------------+
|    1 | wool      | Light Yellow |
|    2 | wool      | Beige        |
|    3 | polyester | Light Yellow |
|    4 | polyester | Light Red    |
|    1 | Velvet    | Purple       |
|    2 | cotton    | White        |
|    3 | cotton    | Light Yellow |
|    4 | cotton    | Light Blue   |
+------+-----------+--------------+
8 rows in set (0.00 sec)

噢，这很糟糕吧？当然，我们没有指定 where 子句:

mysql> select id, material, color from carpet where color='Light Yellow'
    -> union
    -> select id, material, color from curtain where color='Light Yellow';
+------+-----------+--------------+
| id   | material  | color        |
+------+-----------+--------------+
|    1 | wool      | Light Yellow |
|    3 | polyester | Light Yellow |
|    3 | cotton    | Light Yellow |
+------+-----------+--------------+
3 rows in set (0.00 sec)

两个表之间的颜色连接将允许您在单个数据行中返回两个表中的行。您可以为项目颜色指定两个表的连接，并使用 where 子句仅返回您要查找的行。

mysql> select a.id, a.material, a.color, b.id, b.material 
    -> from curtain a join carpet b on a.color=b.color;
+------+----------+--------------+------+-----------+
| id   | material | color        | id   | material  |
+------+----------+--------------+------+-----------+
|    3 | cotton   | Light Yellow |    1 | wool      |
|    3 | cotton   | Light Yellow |    3 | polyester |
+------+----------+--------------+------+-----------+
2 rows in set (0.00 sec)

如您所见，这仅返回具有匹配颜色的行，并允许您在结果集的一行中包含来自两个表的列。

现在，我显然没有计划好这件事，因为除了两个表中的“浅黄色”之外我没有其他匹配结果，所以如果我在其中添加更多条目，我们会得到这个:

mysql> select * from curtain;
+------+----------+--------------+
| id   | material | color        |
+------+----------+--------------+
|    1 | Velvet   | Purple       |
|    2 | cotton   | White        |
|    3 | cotton   | Light Yellow |
|    4 | cotton   | Light Blue   |
|    5 | Wool     | White        |
|    6 | Fluff    | Beige        |
+------+----------+--------------+
6 rows in set (0.00 sec)

mysql> select * from carpet;
+------+-----------+--------------+
| id   | material  | color        |
+------+-----------+--------------+
|    1 | wool      | Light Yellow |
|    2 | wool      | Beige        |
|    3 | polyester | Light Yellow |
|    4 | polyester | Light Red    |
|    5 | Fluff     | Light Blue   |
+------+-----------+--------------+
5 rows in set (0.00 sec)

现在我们可以再次运行它，这次得到:

mysql> select a.id, a.material, a.color, b.id, b.material 
    -> from curtain a join carpet b on a.color=b.color;
+------+----------+--------------+------+-----------+
| id   | material | color        | id   | material  |
+------+----------+--------------+------+-----------+
|    3 | cotton   | Light Yellow |    1 | wool      |
|    3 | cotton   | Light Yellow |    3 | polyester |
|    4 | cotton   | Light Blue   |    5 | Fluff     |
|    6 | Fluff    | Beige        |    2 | wool      |
+------+----------+--------------+------+-----------+
4 rows in set (0.00 sec)

哦不!

这是我们一起使用 join 和 where 子句的地方:

mysql> select a.id, a.material, a.color, b.id, b.material 
    -> from curtain a join carpet b on a.color=b.color 
    -> where a.color='Light Yellow';
+------+----------+--------------+------+-----------+
| id   | material | color        | id   | material  |
+------+----------+--------------+------+-----------+
|    3 | cotton   | Light Yellow |    1 | wool      |
|    3 | cotton   | Light Yellow |    3 | polyester |
+------+----------+--------------+------+-----------+
2 rows in set (0.00 sec)

您会看到，在 SQL 中，通过不同的方式获得相同结果的方法通常比表中相同数据的变体要多。

编辑:好的，所以如果您只想要数据匹配的所有行，只需将其包含在连接语法中即可:

mysql> select a.id, a.material, a.color, b.id, b.material 
    -> from curtain a 
    -> join carpet b on a.color=b.color
    -> and a.id=b.id
    -> where a.color='Light Yellow';
+------+----------+--------------+------+-----------+
| id   | material | color        | id   | material  |
+------+----------+--------------+------+-----------+
|    3 | cotton   | Light Yellow |    3 | polyester |
+------+----------+--------------+------+-----------+
1 row in set (0.00 sec)

如您所见，现在我们告诉连接 id 和 color 字段必须在两个表之间匹配 - 结果不言自明。现在，在这种情况下，我在技术上仍然不匹配所有列，因为 Material 不同。如果你想进一步匹配，查询不会返回任何结果，因为我没有id、 Material 和颜色匹配的匹配记录，但语法如下:

mysql> select a.id, a.material, a.color, b.id, b.material 
    -> from curtain a 
    -> join carpet b on a.color=b.color
    -> and a.id=b.id
    -> and a.material=b.material
    -> where a.color='Light Yellow';
Empty set (0.00 sec)

尽管如此，在大多数情况下，您不希望所有 列都匹配。表通常有一个仅用于该表的 ID，并且是一个自动递增的值。您希望使用它来标识那个 表中的唯一行，而不是用它来匹配不相关的表。如果有的话，我会建议您在 Material 和颜色上进行匹配 - 但不要包含 ID。