SQL JOIN 和 UNION 有什么区别？

What is the difference between JOIN and UNION?

提问by Gold

What is the difference between JOINand UNION? Can I have an example?

JOIN和 和有UNION什么区别？我可以举个例子吗？

回答by Alex Martelli

UNIONputs lines from queries after each other, while JOINmakes a cartesian product and subsets it -- completely different operations. Trivial example of UNION:

UNION将查询中的行放在一起，同时JOIN制作笛卡尔积并将其子集 - 完全不同的操作。的简单例子UNION：

mysql> SELECT 23 AS bah
    -> UNION
    -> SELECT 45 AS bah;
+-----+
| bah |
+-----+
|  23 | 
|  45 | 
+-----+
2 rows in set (0.00 sec)

similary trivial example of JOIN:

类似的简单例子JOIN：

mysql> SELECT * FROM 
    -> (SELECT 23 AS bah) AS foo 
    -> JOIN 
    -> (SELECT 45 AS bah) AS bar
    -> ON (33=33);
+-----+-----+
| foo | bar |
+-----+-----+
|  23 |  45 | 
+-----+-----+
1 row in set (0.01 sec)

回答by Humoyun Ahmad

Joins and Unions can be used to combine data from one or more tables. The difference lies in how the data is combined.

联接和联合可用于组合来自一个或多个表的数据。不同之处在于数据的组合方式。

In simple terms, joins combine data into new columns. If two tables are joined together, then the data from the first table is shown in one set of column alongside the second table's column in the same row.

简单来说，联接将数据合并到新列中。如果两个表连接在一起，则第一个表中的数据显示在同一行中第二个表列旁边的一组列中。

Unions combine data into new rows.If two tables are “unioned” together, then the data from the first table is in one set of rows, and the data from the second table in another set. The rows are in the same result.

联合将数据合并到新行中。如果两个表“联合”在一起，则第一个表中的数据在一组行中，而第二个表中的数据在另一组中。行在相同的结果中。

Here is a visual depiction of a join. Table A and B's columns are combined into a single result.

这是连接的可视化描述。表 A 和 B 的列合并为一个结果。

Each row in the result contains columns from BOTH table A and B. Rows are created when columns from one table match columns from another. This match is called the join condition.

结果中的每一行都包含来自表 A 和表 B 的列。当一个表中的列与另一个表中的列匹配时，就会创建行。这种匹配称为连接条件。

This makes joins really great for looking up values and including them in results. This is usually the result of denormalizing (reversing normalization) and involves using the foreign key in one table to look up column values by using the primary key in another.

这使得连接非常适合查找值并将它们包含在结果中。这通常是非规范化（反向规范化）的结果，涉及使用一个表中的外键通过使用另一个表中的主键来查找列值。

Now compare the above depiction with that of a union. In a union each row within the result is from one table OR the other. In a union, columns aren't combined to create results, rows are combined.

现在将上面的描述与联合的描述进行比较。在联合中，结果中的每一行都来自一个表或另一个表。在联合中，不会组合列来创建结果，而是组合行。

Both joins and unions can be used to combine data from one or more tables into a single results. They both go about this is different ways. Whereas a join is used to combine columns from different tables, the union is used to combine rows.

联接和联合均可用于将一个或多个表中的数据合并为一个结果。他们都以不同的方式解决这个问题。连接用于组合来自不同表的列，而联合用于组合行。

Source

来源

回答by Kirtan

UNIONcombines the results of two or more queries into a single result set that includes all the rows that belong to all queries in the union.

UNION将两个或多个查询的结果组合到一个结果集中，该结果集中包含属于联合中所有查询的所有行。

By using JOINs, you can retrieve data from two or more tables based on logical relationships between the tables. Joins indicate how SQL should use data from one table to select the rows in another table.

通过使用JOIN，您可以根据表之间的逻辑关系从两个或多个表中检索数据。联接指示 SQL 应如何使用一个表中的数据来选择另一个表中的行。

The UNION operation is different from using JOINs that combine columns from two tables.

UNION 操作不同于使用 JOIN 组合来自两个表的列。

UNION Example:

联合示例：

SELECT 1 AS [Column1], 2 AS [Column2]
UNION
SELECT 3 AS [Column1], 4 AS [Column2]

Output:

输出：

Column1    Column2
-------------------
1          2
3          4

JOIN Example:

加入示例：

SELECT a.Column1, b.Column2 FROM TableA a INNER JOIN TableB b ON a.Id = b.AFKId

This will output all the rows from both the tables for which the condition a.Id = b.AFKIdis true.

这将输出两个表中条件a.Id = b.AFKId为真的所有行。

回答by Java Main

You may see the same schematic explanations for both, but these are totally confusing.

您可能会看到两者的相同原理图解释，但这些解释完全令人困惑。

For UNION:

对于联合：

For JOIN:

对于加入：

回答by Ganesh Narayan Tharol

JOIN:

加入：

A join is used for displaying columns with the same or different names from different tables. The output displayed will have all the columns shown individually. That is, the columns will be aligned next to each other.

连接用于显示来自不同表的具有相同或不同名称的列。显示的输出将单独显示所有列。也就是说，列将彼此相邻对齐。

UNION:

联盟：

The UNION set operator is used for combining data from two tables which have columns with the same datatype. When a UNION is performed the data from both tables will be collected in a single column having the same datatype.

UNION 集合运算符用于组合来自具有相同数据类型的列的两个表中的数据。执行 UNION 时，两个表中的数据将收集在具有相同数据类型的单个列中。

For example:

例如：

See the two tables shown below:

请参阅下面显示的两个表：

Table t1
Articleno article price manufacturer_id
1 hammer 3 $ 1
2 screwdriver 5 $ 2

Table t2
manufacturer_id manufacturer
1 ABC Gmbh
2 DEF Co KG

Now for performing a JOIN type the query is shown below.

现在为了执行 JOIN 类型，查询如下所示。

SELECT articleno, article, manufacturer
FROM t1 JOIN t2 ON (t1.manufacturer_id =
t2.manufacturer_id);

articelno article manufacturer
1 hammer ABC GmbH
2 screwdriver DEF Co KG

That is a join.

那是一个连接。

UNION means that you have to tables or resultset with the same amount and type of columns and you add this to tables/resultsets together. Look at this example:

UNION 意味着您必须使用具有相同数量和类型的列的表或结果集，并将其添加到表/结果集中。看这个例子：

Table year2006
Articleno article price manufacturer_id
1 hammer 3 $ 1
2 screwdriver 5 $ 2

Table year2007
Articleno article price manufacturer_id
1 hammer 6 $ 3
2 screwdriver 7 $ 4

SELECT articleno, article, price, manufactruer_id
FROM year2006
UNION
SELECT articleno, article, price, manufacturer_id
FROM year2007

articleno article price manufacturer_id
1 hammer 3 $ 1
2 screwdriver 5 $ 2
1 hammer 6 $ 3
2 screwdriver 7 $ 4

回答by adeshsuryan

They're completely different things.

它们是完全不同的东西。

A joinallows you to relate similar data in different tables.

一个加盟让您在不同的表中涉及的类似数据。

A union returns the results of two different queries as a single recordset.

联合将两个不同查询的结果作为单个记录集返回。

回答by James L

Union makes two queries look like one. Joins are for examining two or more tables in a single query statement

Union 使两个查询看起来像一个。联接用于在单个查询语句中检查两个或多个表

回答by VAHEED

Joins and unions can be used to combine data from one or more tables. The difference lies in how the data is combined.

联接和联合可用于组合来自一个或多个表的数据。不同之处在于数据的组合方式。

In simple terms, joins combine data into new columns. If two tables are joined together, then the data from the first table is shown in one set of column alongside the second table's column in the same row.

简单来说，联接将数据合并到新列中。如果两个表连接在一起，则第一个表中的数据显示在同一行中第二个表列旁边的一组列中。

Unions combine data into new rows. If two tables are “unioned” together, then the data from the first table is in one set of rows, and the data from the second table in another set. The rows are in the same result.

联合将数据合并到新行中。如果两个表“联合”在一起，则第一个表中的数据在一组行中，而第二个表中的数据在另一组中。行在相同的结果中。

回答by MonsterMagnet

Remember that union will merge results (SQL Serverto be sure)(feature or bug?)

请记住，联合将合并结果（当然是SQL Server）（功能还是错误？）

select 1 as id, 3 as value
union
select 1 as id, 3 as value

id,value

ID，值

1,3

1,3

select * from (select 1 as id, 3 as value) t1 inner join (select 1 as id, 3 as value) t2 on t1.id = t2.id

id,value,id,value

ID，值，ID，值

1,3,1,3

1,3,1,3

回答by ian

1. The SQL Joins clause is used to combine records from two or more tables in a database. A JOIN is a means for combining fields from two tables by using values common to each.

1. SQL Joins 子句用于合并来自数据库中两个或多个表的记录。JOIN 是一种通过使用每个表的公共值来组合来自两个表的字段的方法。

2. The SQL UNION operator combines the result of two or more SELECT statements. Each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.

2. SQL UNION 运算符组合两个或多个 SELECT 语句的结果。UNION 中的每个 SELECT 语句必须具有相同的列数。列也必须具有相似的数据类型。此外，每个 SELECT 语句中的列必须具有相同的顺序。

for example: table 1 customers/table 2 orders

例如：表 1 客户/表 2 订单

inner join:

内部联接：

SELECT ID, NAME, AMOUNT, DATE

选择 ID、姓名、金额、日期

FROM CUSTOMERS?

来自客户？

INNER JOIN ORDERS?

内部连接命令？

ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

union:

联盟：

SELECT ID, NAME, AMOUNT, DATE

选择 ID、姓名、金额、日期

?FROM CUSTOMERS?

来自客户？

LEFT JOIN ORDERS?

左加入订单？

ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID

ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID

UNION

联盟

SELECT ID, NAME, AMOUNT, DATE ? FROM CUSTOMERS?

选择 ID、姓名、金额、日期？来自客户？

RIGHT JOIN ORDERS?

正确的加入订单？

ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;