Difference between Join and Union in SQL


In SQL, we have two different clauses for combining data from two different tables. They are union and join. Although the function of both these operators are same that is to create a new table with combined data, their way of approach to this is different. Union uses the concept of conjunction whereas the join uses the concept of intersection.

Union Clause in SQL

Union clause in SQL is used to combine records in multiple select statements. It combines the results of two or more select statements. The resultant table is the union of data present in both the select statements. It prevents the duplicate entries in the table.

Union operator adds rows from select statements and then display them in a single table. If some of the data is same in both the tables, it returns only single entries. Thus, the returned rows are distinct. There are certain conditions in order to perform the union operation. They are:

  • All the select statements involved in union operation must have the same number of columns selected

  • All the selected columns should be in same order

  • Columns should be of same data type

The union all operator is used if we want to include duplicate values within our resultant table. It returns all the entries in both the select statements.

Syntax

The following is the syntax for using the union operator:

Select column_name from table_name 1
Union
Select column_name from table_name 2;

Here, column_name represents the name of columns that we wanted to fetch. Table_name 1 and table_name 2 are the names of tables that we are combining. We can union more than two tables too.

Join Clause in SQL

Join is also used to combine the data of two or more tables based on the common column between them. All the tables are connected to each other using a single column which is known as a foreign key. This join operator uses this foreign key to fetch the columns or data.

Join can be used on two or more tables. The only condition to perform join is that the tables must be related to each other. There are many kinds of joins used. They are:

  • Inner join − returns records that are common between two tables

  • Left join − returns all the records of the left table along with the common records between the two tables

  • Right join − returns all the records of the right table along with the common records between the two tables

  • Full join − returns all the records of two tables

Syntax

Following is the syntax to use the Join operator:

Select column_name from table_name 1 
Join table_name 2 
On condition;

Here, column_name are the names of columns that we want in our output. Table_name 1 and table_name 2 are the tables that we want to join. The condition is the one that specifies on which condition should we join the table. It contains the common columns between the two based on which they are joining and some other conditions (where, group by, order by etc.,).

Joins can be used on more than two tables based on our need.

Union vs Join in SQL

The following table highlights the major differences between the Union and Join clauses in SQL:

Union

Join

Union is used to combine multiple select statements

Join is used to retrieve the specified columns from multiple tables

There is no need to have any relation between the tables to perform union operation

In order to use join, there must be a relation between those tables

Union adds the new records into rows

Join combines the data into columns

Same number of columns should be selected from each table

One can select any number of columns from each table

Data types of the columns specified from both tables should be same

It is not necessary for the columns to be in the same data type

All the specified columns should be in a specified order

Columns specified can be in any order

There is no need to have a common column or connection between the two tables

Tables must have at least one common column between them

It increases the table size vertically that is data is added vertically into rows

It increases the table size horizontally that is data is added horizontally into columns

Union doesn’t allow duplicate entries

Join allows duplicate entries

It uses the concept of conjunction

It works on the concept of intersection

Union is of two types − union and union all

Join is of four different types − inner join (or join), left join, right join, full join

Conclusion

Union and Join are used combine data and give output as a single table. Union combines data from multiple select statements and join combines data from multiple tables. But for join, there must be relationship between the tables whereas for union, the number of records fetching from both the select statements should be same.

Updated on: 12-Jul-2023

3K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements