What is the difference between JOIN and UNION?

2019-01-02 19:09发布

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

15条回答
千与千寻千般痛.
2楼-- · 2019-01-02 19:35

Union Operation is combined result of the Vertical Aggregate of the rows, Union Operation is combined result of the Horizontal Aggregate of the Columns.

查看更多
栀子花@的思念
3楼-- · 2019-01-02 19:39

Any modern DBS, like MariaDB, implements a UNION JOIN command. This is an SQL 3 command, but it isn't well known or used. Do learn more on UNION JOIN.

查看更多
人气声优
4楼-- · 2019-01-02 19:40

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.

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.

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:

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
查看更多
ら面具成の殇う
5楼-- · 2019-01-02 19:43

UNION combines 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.

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.

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

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.AFKId is true.

查看更多
骚的不知所云
6楼-- · 2019-01-02 19:45

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

For UNION:

Enter image description here

For JOIN:

Enter image description here

查看更多
不再属于我。
7楼-- · 2019-01-02 19:48

They're completely different things.

A join allows you to relate similar data in different tables.

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

查看更多
登录 后发表回答