JOIN and UNION are the clauses in SQL, used to combine the data of two or more relations. But the way in which they combine data and format of the result obtained, differs. The JOIN clause combines the attributes of two relations to form the resultant tuples whereas, UNION clause combines the result of two queries.
Let us discuss the difference between JOIN and UNION with the help of comparison chart shown below.
Content: JOIN Vs UNION
Comparison Chart
| Basis for Comparison | JOIN | UNION |
|---|---|---|
| Basic | JOIN combines attributes of the tuples present in the two different relations that share some common fields or attributes. | UNION combines tuples of the relations that are present in the query. |
| Condition | JOIN is applicable when the two involved relations have at least one common attribute. | UNION is applicable when the number of columns present in query are same and the corresponding attributes has the same domain. |
| Types | INNER, FULL (OUTER), LEFT JOIN, RIGHT JOIN. | UNION and UNION ALL. |
| Effect | The length of the resultant tuples is more as compared to the length of tuples of the involved relations. | The number of the resultant tuples is more as compared to the number of tuples present in the each relation involved in the query. |
| Diagram |  |  |
Definition of JOIN
JOIN clause in SQL combines the tuples from two relations or tables resulting in a longer tuple size. The resultant tuple contains attributes from both the relation. Attributes are combined based on the common attributes between them. The different types of JOIN in SQL are INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN.
- INNER JOIN combines tuples from both the tables as long as there is a common attribute between both of them.
- LEFT JOIN results in all the tuples of the left table and matching tuple from the right table.
- RIGHT JOIN results in all the tuples from the right table and only matching tuple from the left table.
- FULL OUTER JOIN results in all the tuples from both the table though they have matching attributes or not.
INNER JOIN is same as the JOIN. You can also drop INNER keyword and simply use JOIN to perform INNER JOIN.
Definition of UNION
UNION is a set operation in SQL. UNON combines the result of two queries. The result of UNION includes the tuples from both the relations present in the query. The conditions that must be satisfied take the UNION of two relations are:
- The two relations must have the same number of attributes.
- The domains of the corresponding attribute must be same.
There are two types of UNION that are UNION and UNION ALL. The result obtained using UNION do not include duplicates. On the other hand, the result obtained using UNION ALL retains duplicate.
Key Differences Between JOIN and UNION in SQL
- The primary difference between JOIN and UNION is that JOIN combines the tuples from two relations and the resultant tuples include attributes from both the relations. On the other hand, the UNION combines the result of two SELECT queries.
- The JOIN clause is applicable only when the two relations involved have at least one attribute common in both. On the other hands, the UNION is applicable when the two relations have the same number of attribute and the domains of corresponding attributes are same.
- There are four types of JOIN INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN. But there are two types of UNION, UNION and UNION ALL.
- In JOIN, the resultant tuple has the larger size as it includes attributes from both the relation. On the other hands, in UNION the number of tuples are increased as a result include the tuple from both the relations present in the query.
Conclusion
Both being data combining operations are used in different situations. JOIN is used when we want to combine attributes of two relations having at least one attribute in common. UNION is used when we want to combine the tuples of the two relations that are present in the query.
Leave a Reply