[sql] What is the difference between JOIN and UNION?

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

I like to think of the general difference as being:

• JOINS join tables
• UNION (et all) combines queries.

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.

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.

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.

for example: table 1 customers/table 2 orders

inner join:

SELECT ID, NAME, AMOUNT, DATE

FROM CUSTOMERS?

INNER JOIN ORDERS?

ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

union:

SELECT ID, NAME, AMOUNT, DATE

?FROM CUSTOMERS?

LEFT JOIN ORDERS?

ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID

UNION

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

RIGHT JOIN ORDERS?

ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

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.

In the abstract, they are similar, in that two tables or result sets are being combined , but UNION is really for combining result sets with the SAME NUMBER OF COLUMNS with the COLUMNS HAVING SIMILAR DATA TYPES. The STRUCTURE is the same, only new rows are being added.

In joins, you can combine tables/result sets with any possible structure, including a cartesian join where there are NO shared/similar columns.

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.

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.

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

The UNION operator is just for combining two or more SELECT statements.

While JOIN is for selecting rows from each table, either by the inner, outer, left or right method.

Refer to here and here . There is a better explanation with examples.

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

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

id,value

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

1,3,1,3

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

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

For UNION:

For JOIN:

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

Ussing UNION

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

UNION Example:
SELECT 121 AS [Column1], 221 AS [Column2]
UNION
SELECT 321 AS [Column1], 422 AS [Column2]

Output:

Column1    Column2
-------------------
121          221
321          422

Ussing JOINs

JOINs, you can retrieve data from two or more tables based on logical relationships between the tables.

JOIN Example:
SELECT a.Column1, b.Column2 FROM TblA a INNER JOIN TblB b ON a.Id = b.id

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.

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