I have two tables which I want to connect.
我有两个表要连接。
TABLE_A:
+-----------+-----------+---------+
| row_id | category | val_1 |
+-----------+-----------+---------+
| 1067 | cat1 | 6.5 |
| 2666 | test | 6.5 |
| 2710 | cat1 | 2.1 |
| 2710 | test | 7.1 |
| 2767 | test | 3 |
| 71142 | cat1 | 5 |
| 50666 | other | 6.5 |
| 71142 | other | 1 |
| 345342 | cat1 | 6.5 |
| 345342 | test | 2.8 |
+-----------+-----------+---------+
TABLE_B:
+-----------+-----------+
| row_id | val_2 |
+-----------+-----------+
| 1067 | 2.0 |
| 2666 | 9 |
| 2701 | 2.2 |
| 2708 | 1 |
| 2709 | 6.5 |
| 2710 | 5.2 |
| 2765 | 6.5 |
| 2766 | 15 |
| 2767 | 8 |
| 71142 | 5 |
| 2783 | 4.5 |
| 50666 | 6.5 |
| 101588 | 9 |
| 101588 | 3 |
| 3452 | 8.0 |
| 23422 | 5 |
| 345342 | 6.5 |
+-----------+-----------+
RESULT_TABLE:
+-----------+-----------+-----------+------------+
| row_id | val_2 | val_1 | category |
+-----------+-----------+-----------+------------+
| 1067 | 2.0 | 6.5 | cat1 |
| 2666 | 9 | 6.5 | test |
| 2701 | 2.2 | 2.2 | NULL |
| 2708 | 1 | 1 | NULL |
| 2709 | 6.5 | 1 | NULL |
| 2710 | 5.2 | 2.1 | cat1 |
| 2710 | 5.2 | 7.1 | test |
| 2765 | 6.5 | 1 | NULL |
| 2766 | 15 | 1 | NULL |
| 2767 | 8 | 3 | test |
| 71142 | 5 | 5 | cat1 |
| 71142 | 5 | 1 | other |
| 2783 | 4.5 | 1 | NULL |
| 50666 | 6.5 | 6.5 | other |
| 101588 | 9 | 1 | NULL |
| 101588 | 3 | 1 | NULL |
| 3452 | 8.0 | 1 | NULL |
| 23422 | 5 | 1 | NULL |
| 345342 | 6.5 | 6.5 | cat1 |
| 345342 | 6.5 | 2.8 | test |
+-----------+-----------+-----------+------------+
I tried to use something like this:
我尝试使用这样的东西:
SELECT TABLE_A.row_id, TABLE_A.category, TABLE_A.val_1, TABLE_B.val_2
FROM TABLE_A
INNER JOIN TABLE_B ON TABLE_B.row_id = TABLE_A.row_id
ORDER BY row_id;
However, the result included only rows where the row_id
column exists in TABLE_A.
但是,结果仅包括TABLE_A中存在row_id列的行。
Is there way to connect TABLE_A and TABLE_B to produce the result shown in RESULT_TABLE?
有没有办法连接TABLE_A和TABLE_B以产生RESULT_TABLE中显示的结果?
3 个解决方案
#1
6
If you want all the results, you need an outer join, not an inner one. (Inner only returns the rows where there is a match; outer returns all rows, with the matching rows "stitched together")
如果您想要所有结果,则需要外连接,而不是内连接。 (Inner仅返回匹配的行; outer返回所有行,匹配的行“拼接在一起”)
#2
5
Try an outer join.
尝试外连接。
SELECT TABLE_A.row_id, TABLE_A.category, TABLE_A.val_1, TABLE_B.val_2
FROM TABLE_B
LEFT OUTER JOIN TABLE_A ON TABLE_B.row_id = TABLE_A.row_id
ORDER BY row_id;
#3
2
This ought to do it:
这应该这样做:
SELECT
TABLE_B.row_id row_id,
TABLE_A.category category,
COALESCE(TABLE_A.val_1,1) val_1,
TABLE_B.val_2 val_2
FROM TABLE_A
RIGHT OUTER JOIN TABLE_B
ON TABLE_B.row_id = TABLE_A.row_id
ORDER BY TABLE_B.row_id;
The RIGHT OUTER JOIN
pulls all the records from Table_B even if they don't exist in Table_A, and the COALESCE
statement is a function that returns its first non-NULL parameter. In this case, if there is no value in Table_A, it will return 1
, which is what your example result lists as the desired output.
RIGHT OUTER JOIN从Table_B中提取所有记录,即使它们在Table_A中不存在,并且COALESCE语句是返回其第一个非NULL参数的函数。在这种情况下,如果Table_A中没有值,它将返回1,这是您的示例结果列为所需输出的值。
#1
6
If you want all the results, you need an outer join, not an inner one. (Inner only returns the rows where there is a match; outer returns all rows, with the matching rows "stitched together")
如果您想要所有结果,则需要外连接,而不是内连接。 (Inner仅返回匹配的行; outer返回所有行,匹配的行“拼接在一起”)
#2
5
Try an outer join.
尝试外连接。
SELECT TABLE_A.row_id, TABLE_A.category, TABLE_A.val_1, TABLE_B.val_2
FROM TABLE_B
LEFT OUTER JOIN TABLE_A ON TABLE_B.row_id = TABLE_A.row_id
ORDER BY row_id;
#3
2
This ought to do it:
这应该这样做:
SELECT
TABLE_B.row_id row_id,
TABLE_A.category category,
COALESCE(TABLE_A.val_1,1) val_1,
TABLE_B.val_2 val_2
FROM TABLE_A
RIGHT OUTER JOIN TABLE_B
ON TABLE_B.row_id = TABLE_A.row_id
ORDER BY TABLE_B.row_id;
The RIGHT OUTER JOIN
pulls all the records from Table_B even if they don't exist in Table_A, and the COALESCE
statement is a function that returns its first non-NULL parameter. In this case, if there is no value in Table_A, it will return 1
, which is what your example result lists as the desired output.
RIGHT OUTER JOIN从Table_B中提取所有记录,即使它们在Table_A中不存在,并且COALESCE语句是返回其第一个非NULL参数的函数。在这种情况下,如果Table_A中没有值,它将返回1,这是您的示例结果列为所需输出的值。