UNION vs JOIN

该中心提供各种关系操作者,处理多个表格在关系数据库中传播的数据。 其中,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是KQ的一个经营/企业类型,与联盟运营商在关系方面的类似工作。 它只是将多个符合工会的表格的信息结合起来。

这些表格如果符合以下条件,即符合工会的要求:

The tables to be combined must have same number of columns with the same datatype.

The number of rows need not be same.

一旦达到这些标准,联合国构想的运营商将全部从多个表格中回收,然后将重复浏览作为结果表。

Syntax

Following is the syntax of UNION operator in SQL −

SELECT * FROM table1
UNION
SELECT * FROM table2;

Example

让我们首先创建两个表格“CourSES_PICKED”和“EXTRA_COURSES_PICKED”,其数据类型相同。

创建表格 COURSES_PICKED, 采用以下程序:

CREATE TABLE COURSES_PICKED(
   STUDENT_ID INT NOT NULL, 
   STUDENT_NAME VARCHAR(30) NOT NULL, 
   COURSE_NAME VARCHAR(30) NOT NULL
);

在下文所述问询的帮助下,将数值列入消费物价指数表中:

INSERT INTO COURSES_PICKED VALUES(1,  JOHN ,  ENGLISH );
INSERT INTO COURSES_PICKED VALUES(2,  ROBERT ,  COMPUTER SCIENCE );
INSERT INTO COURSES_PICKED VALUES(3,  SASHA ,  COMMUNICATIONS );
INSERT INTO COURSES_PICKED VALUES(4,  JULIAN ,  MATHEMATICS );

创建表格 采用以下程序:

CREATE TABLE EXTRA_COURSES_PICKED(
   STUDENT_ID INT NOT NULL, 
   STUDENT_NAME VARCHAR(30) NOT NULL, 
   EXTRA_COURSE_NAME VARCHAR(30) NOT NULL
);

下面是将价值观列入贸易与投资司表格的问题。

INSERT INTO EXTRA_COURSES_PICKED VALUES(1,  JOHN ,  PHYSICAL EDUCATION );
INSERT INTO EXTRA_COURSES_PICKED VALUES(2,  ROBERT ,  GYM );
INSERT INTO EXTRA_COURSES_PICKED VALUES(3,  SASHA ,  FILM );
INSERT INTO EXTRA_COURSES_PICKED VALUES(4,  JULIAN ,  PHOTOGRAPHY );

现在,让我们努力利用“联合国意见”把这两个表格合并为“灯塔”;

SELECT * FROM COURSES_PICKED
UNION
SELECT * FROM EXTRA_COURSES_PICKED;

Output

执行联合国意见后获得的表格是:

+------------+--------------+--------------------+
| STUDENT_ID | STUDENT_NAME | COURSE_NAME        |
+------------+--------------+--------------------+
|          1 | JOHN         | ENGLISH            |
|          1 | JOHN         | PHYSICAL EDUCATION |
|          2 | ROBERT       | COMPUTER SCIENCE   |
|          2 | ROBERT       | GYM                |
|          3 | SASHA        | COMMUNICATIONS     |
|          3 | SASHA        | FILM               |
|          4 | JULIAN       | MATHEMATICS        |
|          4 | JULIAN       | PHOTOGRAPHY        |
+------------+--------------+--------------------+

Working of JOIN

若因业务将多个相关表格的信息根据其共同领域合并为一个表格。

In this operation, every row of the first table will be combined with every row of the second table. The resultant table obtained will contain the rows present in both tables. This operation can be used with various clauses pke ON, WHERE, ORDER BY, GROUP BY etc.

共有两种婚姻:

Inner Join

Outer Join

加入的基本类型是内部的Join,只收回共同栏的对应值。 这是一起违约。 其他加入“十字”组织、“自然儒昂”、“有条件的约恩”等都是内华人。

外包商在表上包括表上表上与表相匹配的和未配对的行。 它分为不同的子类,如Join、Join和Fud Join。

尽管合并运作可以合并多个表格,但合并两个表格的最简单方式是不使用《欧安办事处条款》以外的任何条款。

Syntax

以下是Joinkou行动的基本辛迪加——

SELECT column_name(s)
FROM table1
JOIN table2
ON table1.column_name = table2.column_name;

Example

In the following example, we will try to 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;

Output

下表将列出:

+------------+--------------+------------------+--------------------+
| STUDENT_ID | STUDENT_NAME | COURSE_NAME      | COURSES_PICKED     |
+------------+--------------+------------------+--------------------+
|          1 | JOHN         | ENGLISH          | PHYSICAL EDUCATION |
|          2 | ROBERT       | COMPUTER SCIENCE | GYM                |
|          3 | SASHA        | COMMUNICATIONS   | FILM               |
|          4 | JULIAN       | MATHEMATICS      | PHOTOGRAPHY        |
+------------+--------------+------------------+--------------------+

UNION Vs JOIN

正如我们在上文列举的例子中看到的那样,“联合国决策”经营者只是在符合工会的表格上受到迫害,而“国际自由行动网”经营者则加入两个不需要兼容但应该相关的表格。

让我们总结以下这些询问之间的所有区别: