Difference between Union And Join in SQL

[18136 views]

Both UNIONS and Joins can be used in SQL to combine data from two or more tables. Read this article to discover each command's strengths and when to use them in your query. First let us discuss what each term actually do in detail and then we will compare both of them

What is Union in SQL?

Difference between Union and Join in DBMS

Image Source: Essential SQL

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

Example of Union

SELECT 1 AS table1 UNION SELECT 2 AS table1;

Output: table1 ------- 1 2

What is Join in SQL?

Difference between Joins and Union in DBMS

A join is used for showing columns with the same or different names from different tables. The output showed will have all the columns shown demonstrated independently. That is, the columns will be aligned besides each other.

Example of Join

SELECT * FROM (SELECT 1 AS table1) AS table1 JOIN (SELECT 2 AS table2) AS table2 ON (1=1);

Output: table1 table2 ------------------- 1 2

From above definitions of Union and Join, most of differences between them might have been cleared. Now lets compare them together in a tabular form

UNION Vs JOIN in SQL

JOIN	UNION
Join's combines the data into new Columns	Union's combines the data into new Rows.
The Output of JOIN is a new horizontal set of rows having same number of Rows but can have different number of Columns.	The Output of UNION is a new vertical set of rows having same number of Columns but can have different number of Rows.
The JOIN clause in SQL is applicable only when the two Tables involved have at least one Attribute Common in both Tables.	The UNION in SQL is applicable when the two Tables have the same number of Attribute and the domains of corresponding Attributes are also Same.
Types of JOIN are SELF, INNER, LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN	Types of UNION are UNION and UNION ALL

Conclusion

Both Union and Join can be used to combine data from one or more tables into one single result. They both do this is different ways. A Join is used to combine Columns from different Tables, whereas the Union is used to combine Rows.