如果条件匹配两个表中的行，则创建返回id的查询 [英] Creating query that returns id if condition is matched in rows from two tables
问题描述
我正在学习SQL / dbms并使用Postgres。我想返回在特定列中都具有特定值的行。例如，在表地毯和窗帘中，我想获取 id 浅黄色 的行中的$ c> s条。我想我需要为此加入JOIN，但不知道是哪种类型。
这是我所拥有的：
选择id
从地毯
颜色='浅黄色'
内联接Carpets.colour = Curtains.colour;
两个表都具有 id 属性。 / p>
关于学习 JOIN ，我应该首先学习哪个？如果尝试一次全部学习（因为不同的资源包括不同的变体），我就会在脚下开枪。
重要 strong>我正在寻找一个答案，其中仅当窗帘和地毯均为浅黄色时，才会返回 id 。
已阅读，您的问题是错误的，让我解释一下为什么这三个答案的确是正确的-您的计算方式也是如此。
我提供了所有三个答案以及它们正在使用的模式的示例：
数据库已更改
mysql>创建桌子地毯（id int（3），材料varchar（10），颜色varchar（15））;
查询正常，受影响的0行（0.02秒）
mysql>创建桌子窗帘（id int（3），材料varchar（10），颜色varchar（15））;
查询正常，受影响的0行（0.00秒）
（插入语句束）
mysql>从地毯上选择*；
+ ------ + ----------- + -------------- +
| id |材料颜色
+ ------ + ----------- + -------------- +
| 1 |羊毛|浅黄色|
| 2 |羊毛|米色|
| 3 |聚酯|浅黄色|
| 4 |聚酯|浅红色|
+ ------ + ----------- + -------------- +
设置4行（0.00秒）
mysql>从窗帘中选择*；
+ ------ + ---------- + -------------- +
| id |材料颜色
+ ------ + ---------- + -------------- +
| 1 |天鹅绒|紫色|
| 2 |棉|白|
| 3 |棉|浅黄色|
| 4 |棉|浅蓝色|
+ ------ + ---------- + -------------- +
集合中有4行（0.00秒）
一个相交使用两个select语句并返回匹配结果。在这种情况下，您要查找具有匹配颜色浅黄色的所有行。
我无法在MySQL中提供示例，因为它没有不支持它（如下所示，不需要给出相同的结果。）
两个选择语句的联合查询，每个选择语句仅带有where子句浅黄色的颜色将返回相同的数据。尽管可以使用联合返回不匹配的数据，但是每个select语句中的where子句意味着它仅会返回您想要的行。
mysql>从地毯
中选择编号，材料，颜色->工会
->从窗帘中选择编号，材料，颜色；
+ ------ + ----------- + -------------- +
| id |材料颜色
+ ------ + ----------- + -------------- +
| 1 |羊毛|浅黄色|
| 2 |羊毛|米色|
| 3 |聚酯|浅黄色|
| 4 |聚酯|浅红色|
| 1 |天鹅绒|紫色|
| 2 |棉|白|
| 3 |棉|浅黄色|
| 4 |棉|浅蓝色|
+ ------ + ----------- + -------------- +
集合中的8行（0.00秒）
Aww，这不好吗？当然，我们没有指定where子句：
mysql>从地毯上选择id，材料，颜色，其中color =‘light Yellow’
->工会
->从窗帘中选择id，材料，颜色，其中color =浅黄色；
+ ------ + ----------- + -------------- +
| id |材料颜色
+ ------ + ----------- + -------------- +
| 1 |羊毛|浅黄色|
| 3 |聚酯|浅黄色|
| 3 |棉|浅黄色|
+ ------ + ----------- + -------------- +
集合中的3行（0.00秒）
颜色上的两个表之间的联接将使您可以返回两个表中的行单行数据。您可以在两个表上为项目颜色指定联接，并使用where子句仅返回要查找的行。
mysql>选择a.id，a.material，a.color，b.id，b.material
->从窗帘a.color = b.color上的连接地毯b开始；
+ ------ + ---------- + -------------- + ------ + ----- ------ +
| id |材料颜色id |材料
+ ------ + ---------- + -------------- + ------ + ----- ------ +
| 3 |棉|浅黄色| 1 |羊毛|
| 3 |棉|浅黄色| 3 |聚酯|
+ ------ + ---------- + -------------- + ------ + ----- ------ +
集合中的2行（0.00秒）
当您可以看到，这只返回了具有匹配颜色的行，并允许您将结果集中的两个表中的列都放在结果集中的一行中。
现在，我显然没有不能很好地计划这个，因为除了两个表中的浅黄色之外，我没有其他匹配结果，因此，如果我在其中添加更多条目，我们将得到：
mysql>从窗帘中选择*；
+ ------ + ---------- + -------------- +
| id |材料颜色
+ ------ + ---------- + -------------- +
| 1 |天鹅绒|紫色|
| 2 |棉|白|
| 3 |棉|浅黄色|
| 4 |棉|浅蓝色|
| 5 |羊毛白|
| 6 |绒毛|米色|
+ ------ + ---------- + -------------- +
设置6行（0.00秒）
mysql>从地毯上选择*；
+ ------ + ----------- + -------------- +
| id |材料颜色
+ ------ + ----------- + -------------- +
| 1 |羊毛|浅黄色|
| 2 |羊毛|米色|
| 3 |聚酯|浅黄色|
| 4 |聚酯|浅红色|
| 5 |绒毛|浅蓝色|
+ ------ + ----------- + -------------- +
设置5行（0.00秒）
现在我们可以再次运行它，这次得到：
mysql>选择a.id，a.material，a.color，b.id，b.material
->从窗帘a.color = b.color上的连接地毯b开始；
+ ------ + ---------- + -------------- + ------ + ----- ------ +
| id |材料颜色id |材料
+ ------ + ---------- + -------------- + ------ + ----- ------ +
| 3 |棉|浅黄色| 1 |羊毛|
| 3 |棉|浅黄色| 3 |聚酯|
| 4 |棉|浅蓝色| 5 |绒毛|
| 6 |绒毛|米色| 2 |羊毛|
+ ------ + ---------- + -------------- + ------ + ----- ------ +
集合中的4行（0.00秒）
哦不！
现在这是我们一起使用join和where子句的地方：
mysql>选择a.id，a.material，a.color，b.id，b.material
->从窗帘开始，在a.color = b.color
->上加入地毯b。其中a.color =浅黄色;
+ ------ + ---------- + -------------- + ------ + ----- ------ +
| id |材料颜色id |材料
+ ------ + ---------- + -------------- + ------ + ----- ------ +
| 3 |棉|浅黄色| 1 |羊毛|
| 3 |棉|浅黄色| 3 |聚酯|
+ ------ + ---------- + -------------- + ------ + ----- ------ +
设置2行（0.00秒）
，在SQL中，通常有更多的方法可以通过不同的方式获得相同的结果，而不是表中相同数据的变化形式。
编辑：好的，如果只需要所有数据匹配的行，只需将其包括在连接语法中即可。
mysql>选择a.id，a.material，a.color，b.id，b.material
->从窗帘a
->在a.color = b.color
中加入地毯b->和a.id = b.id
->其中a.color =浅黄色;
+ ------ + ---------- + -------------- + ------ + ----- ------ +
| id |材料颜色id |材料
+ ------ + ---------- + -------------- + ------ + ----- ------ +
| 3 |棉|浅黄色| 3 |聚酯|
+ ------ + ---------- + -------------- + ------ + ----- ------ +
集合中的1行（0.00秒）
当您可以看到，现在我们告诉联接，两个表之间的 id 和 color 字段都必须匹配-结果不言而喻。现在，在这种情况下，由于材料不同，我在技术上 still 并没有匹配所有列。如果您想进一步匹配，查询将不会返回任何结果，因为我没有ID，材质和颜色匹配的匹配记录，但是语法如下：
mysql>选择a.id，a.material，a.color，b.id，b.material
->从窗帘a
->在a.color = b.color
中加入地毯b->和a.id = b.id
->和a.material = b.material
->其中a.color =浅黄色;
空集（0.00秒）
不过，在大多数情况下，您是不想所有列匹配。通常，表的ID仅用于该表，并且是一个自动递增的值。您要使用它来标识该表中的唯一行，而不要使用它来匹配不相关的表。如果有的话，我建议您在材质和颜色上匹配-但不要添加ID。
I'm learning SQL/dbms and using Postgres. I want to return rows that both have a certain value in a certain column. For example in tables Carpets and Curtains, I want to get the ids of rows where the colour is 'light yellow'. I think I need JOIN for this, but am not sure what type.
Here's what I've got:
SELECT id
FROM Carpets
WHERE colour = 'light yellow'
INNER JOIN Curtains ON Carpets.colour = Curtains.colour;
Both tables have the id attribute.
About learning JOIN, which one should I learn first? I'm shooting my self in the foot if I try learning all of them at once (as different resources included different 'variants').
IMPORTANT I was looking for an answer where the id would be returned only if both curtains and carpet were 'light yellow'.
Have read your question on Meta about this particular question, let me explain why all three answers are indeed correct - as is the way that you worked it out.
I have included examples of all three answers and the schema that they are working on:
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)
(bunch of insert statements)
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)
An intersect uses two select statements and brings back matching results. In this case, you are looking for all the rows that have a matching color of 'Light Yellow'.
I can't give you an example in MySQL as it doesn't support it (As you can see below, it's not needed to give the same results).
A union query of two select statements each with a where clause only allowing the color of 'Light Yellow' will return the same data. Although a union can be used to return data that doesn't match, the where clause in each select statement means that it will ondeed only return the rows that you want.
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)
Aww, that's bad right? Of course, we didn't specify the where clause:
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)
A join between two tables on the color will allow you to return the rows from both the tables in a single row of data. You can specify the join on the two tables for the item color, and use a where clause to only return the rows that you are looking for.
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)
As you can see, this has returned only the rows with a matching color and allowed you to have columns from both tables in a single row of your resultset.
Now, I clearly didn't plan this very well as I have no other matching results apart from the 'Light Yellow' in both tables, so if I add a few more entries in we get this:
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)
Now we can run that again, and this time get:
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)
Oh noes!
This is now where we use the join and the where clause together:
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)
You see, in SQL there are often more ways to get the same result through different means than there are variations of the same data in your tables.
Edit: Okay, so if you only want rows where all the data matches, just include it in the join syntax:
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)
As you can see, now we tell the join that both the id and color fields must match between the two tables - and the results speak for themselves. Now, in this case, I technically still didn't match ALL the columns as the material is different. If you wanted to match further, the query would not return any results as I have no matching records where the id, material AND color match, but the syntax would be as follows:
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)
On that note though, you in most cases, you don't want all the columns to match. Very often tables have an ID that is only used for that table and is an automatically incrementing value. You want to use it to identify a unique row in that table, but not to use it to match unrelated tables. If anything, I would have suggested that you match on material and color - but leave the ID out of it.
这篇关于如果条件匹配两个表中的行，则创建返回id的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
