JOIN and UNION are the clauses in SQL, used to combine data from two or more relationships. But the way they combine the data and the format of the result obtained differs. The clause JOIN combines the attributes of two relationships to form the resulting tuples while the clause UNION combines the result of two queries. Let us discuss the difference between JOIN and UNION with the help of the comparison chart shown below.
|Basic||JOIN combines the attributes of the tuples present in the two different relationships that share some common fields or attributes.||UNION combines the relationship tuples that are present in the query.|
|Condition||JOIN applicable when the two relationships involved have at least one common attribute.||UNION applicable when the number of columns in the same query and the corresponding attributes have the same domain.|
|types||INTERNAL, FULL (EXTERNAL), LEFT JOIN, RIGHT RIGHT.||UNION and UNION ALL.|
|Effect||The length of the resulting tuples pi relative to the length of the tuples of the relationships involved.||The number of resulting tuples pi relative to the number of tuples present in each relationship involved in the query.|
Definition of JOIN
There clause JOIN in SQL it combines the tuples of two relationships or tables resulting in a longer tuple size. The resulting tuple contains attributes of both relationships. The 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 combine the tuples of both tables as long as there is a common attribute between both. LEFT JOIN generates all the tuples from the left table and the corresponding tuple from the right table. RIGHT JOIN generates all tuples from the right table and only the corresponding tuple from the left table. FULL OUTER JOIN generates all tuples of both tables even if they have corresponding attributes or not.
INNER JOIN same as JOIN. You can also delete the INNER keyword and simply use JOIN to run INNER JOIN.
Definition of UNION
UNION an operation set in SQL. UNON combines the result of two queries. The result of UNION includes the tuples of both relationships in the query. The conditions that must be met by the UNION of two reports are:
- The two relationships must have the same number of attributes.
- The domains of the corresponding attribute must be the same.
There are two types of UNION that are UNION is UNION ALL . The result obtained using UNION does not include duplicates. On the other hand, the result obtained using UNION ALL retains the duplicates.
Key differences between JOIN and UNION in SQL
- The main difference between JOIN and UNION that JOIN combines the tuples of two relationships and the resulting tuples include the attributes of both relationships. On the other hand, UNION combines the result of two SELECT queries.
- The JOIN clause applicable only when the two relationships involved have at least one common attribute in both. On the other hand, the UNION applicable when the two relationships have the same number of attributes and the domains of the corresponding attributes are equal.
- 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 resulting tuple has the largest size since it includes attributes of both relationships. On the other hand, in UNION the number of tuples increased accordingly includes the tuple from both relationships in the query.
Both data combination operations are used in different situations. JOIN is used when we want to combine the attributes of two relationships having at least one attribute in common. UNION is used when we want to combine the tuples of the two relationships in the query.