Sql 简明教程
UNION vs JOIN
SQL 提供了各种关系运算符来处理关系数据库中跨多表分散的数据。其中，UNION 和 JOIN 查询从本质上用于合并来自多表的数据。
SQL provides various relational operators to handle data that is spread across multiple tables in a relational database. Out of them, UNION and JOIN queries are fundamentally used to combine data from multiple tables.
即使它们都用于同一目的（即合并表），但这些运算符的工作方式之间存在许多差异。主要区别在于 UNION 运算符合并来自多个相似表的无关数据，而 JOIN 运算符仅用于合并来自多个表的相关数据。
Even though they are both used for the same purpose, i.e. to combine tables, there are many differences between the working of these operators. The major difference is that the UNION operator combines data from multiple similar tables irrespective of the data relativity, whereas, the JOIN operator is only used to combine relative data from multiple tables.
Working of UNION
UNION 是 SQL 中的一种运算符/子句，其作用类似于关系代数中的并集运算符。它只是合并来自多个并集兼容表的的信息。
UNION is a type of operator/clause in SQL, that works similar to the union operator in relational algebra. It does nothing more than just combining information from multiple tables that are union compatible.
如果满足以下条件，则表被认为是并集兼容的：
The tables are said to be union compatible if they follow the conditions given below −
-
The tables to be combined must have same number of columns with the same datatype.
-
The number of rows need not be same.
满足这些条件后，UNION 运算符将所有来自多个表的行（消除重复行后）作为结果表返回。
Once these criteria are met, UNION operator returns all the rows from multiple tables, after eliminating duplicate rows, as a resultant table.
Note - 第一张表的列名将成为结果表的列名，第二张表的的内容将合并到具有相同数据类型的结果列中。
Note − Column names of first table will become column names of resultant table, and contents of second table will be merged into resultant columns of same data type.
Syntax
以下是 SQL UNION 运算符的语法：
Following is the syntax of the SQL UNION operator −
SELECT * FROM table1
UNION
SELECT * FROM table2;Example
我们首先使用相同数量且具有相同数据类型的列创建两个表“COURSES_PICKED”和“EXTRA_COURSES_PICKED”。
Let us first create two table "COURSES_PICKED" and "EXTRA_COURSES_PICKED" with the same number of columns having same data types.
使用以下查询创建表 COURSES_PICKED：
Create table COURSES_PICKED using the following query −
CREATE TABLE COURSES_PICKED(
STUDENT_ID INT NOT NULL,
STUDENT_NAME VARCHAR(30) NOT NULL,
COURSE_NAME VARCHAR(30) NOT NULL
);使用下面给出的查询向 COURSES_PICKED 表中插入值：
Insert values into the COURSES_PICKED table with the help of the query given below −
INSERT INTO COURSES_PICKED VALUES
(1, 'JOHN', 'ENGLISH'),
(2, 'ROBERT', 'COMPUTER SCIENCE'),
(3, 'SASHA', 'COMMUNICATIONS'),
(4, 'JULIAN', 'MATHEMATICS');使用以下查询创建一个 EXTRA_COURSES_PICKED 表 −
Create table EXTRA_COURSES_PICKED using the following query −
CREATE TABLE EXTRA_COURSES_PICKED(
STUDENT_ID INT NOT NULL,
STUDENT_NAME VARCHAR(30) NOT NULL,
EXTRA_COURSE_NAME VARCHAR(30) NOT NULL
);以下是在 EXTRA_COURSES_PICKED 表中插入值的查询 −
Following is the query to insert values into the EXTRA_COURSES_PICKED table −
INSERT INTO EXTRA_COURSES_PICKED VALUES
(1, 'JOHN', 'PHYSICAL EDUCATION'),
(2, 'ROBERT', 'GYM'),
(3, 'SASHA', 'FILM'),
(4, 'JULIAN', 'PHOTOGRAPHY');现在，让我们使用 UNION 查询合并表 COURSES_PICKED 和 EXTRA_COURSES_PICKED，如下所示 −
Now, let us combine the tables COURSES_PICKED and EXTRA_COURSES_PICKED, using the UNION query as follows −
SELECT * FROM COURSES_PICKED
UNION
SELECT * FROM EXTRA_COURSES_PICKED;Working of JOIN
Join 操作用于将多个相关表中的信息基于它们共同的字段合并到一张表中。该操作可以与 ON、WHERE、ORDER BY、GROUP BY 等各种子句一起使用。
The Join operation is used to combine information from multiple related tables into one, based on their common fields. This operation can be used with various clauses like ON, WHERE, ORDER BY, GROUP BY etc.
有两种类型的 Join −
There are two types of Joins −
-
Inner Join
-
Outer Join
Join 的基本类型是内部联接，它只检索公用列的匹配值。它是默认的 join。
The basic type of join is an Inner Join, which only retrieves the matching values of common columns. It is a default join.
外部联接的结果表包含来自第一个表的匹配行和不匹配行。它分为左联接、右联接和全联接等子类型。
The result table of the Outer join includes both matched and unmatched rows from the first table. It is divided into subtypes like Left Join, Right Join, and Full Join.
Syntax
以下是 SQL 中 Join 操作的基本语法 −
Following is the basic syntax of a Join operation in SQL −
SELECT column_name(s)
FROM table1
JOIN table2
ON table1.column_name = table2.column_name;Example
在以下示例中，我们将使用以下查询联接我们在上面创建的相同表，即 COURSES_PICKED 和 EXTRA_COURSES_PICKED -
In the following example, we will join the same tables we created above, i.e., COURSES_PICKED and EXTRA_COURSES_PICKED, using the query below -
SELECT c.STUDENT_ID, c.STUDENT_NAME, COURSE_NAME, COURSES_PICKED
FROM COURSES_PICKED c
JOIN EXTRA_COURSES_PICKED e
ON c.STUDENT_ID = e.STUDENT_ID;UNION Vs JOIN
正如我们在上面给出的示例中看到的，UNION 运算符只能对可并集的表执行，而 JOIN 运算符则联接两个不必可并集但应该相关的表。
As we saw in the examples given above, the UNION operator is only executable on tables that are union compatible, whereas, the JOIN operator joins two tables that need not be compatible but should be related.
让我们在下面总结这些查询之间的所有区别 −
Let us summarize all the difference between these queries below −
UNION |
JOIN |
UNION operation is only performed on tables that are union compatible, i.e., the tables must contain same number of columns with same data type. |
JOIN operation can be performed on tables that has at least one common field between them. The tables need not be union compatible. |
The data combined will be added as new rows of the resultant table. |
The data combined will be adjoined into the resultant table as new columns. |
This works as the conjunction operation. |
This works as an intersection operation. |
UNION removes all the duplicate values from the resultant tables. |
JOIN retains all the values from both tables even if they’re redundant. |
UNION does not need any additional clause to combine two tables. |
JOIN needs an additional clause ON to combine two tables based on a common field. |
It is mostly used in scenarios like, merging the old employees list in an organization with the new employees list. |
This is used in scenarios where merging related tables is necessary. For example, combining tables containing customers list and the orders they made. |