Javatpoint Logo
Javatpoint Logo

Difference between MySQL Union and Join

Union and Join are SQL clauses used to perform operations on more than one table in a relational database management system (RDBMS). They produce a result by combining data from two or more tables. But, the way of combining the data from two or more relations differ in both clauses. Before making a comparison, we are going to discuss in brief about these clauses.

What is the Union clause?

MySQL Union clause allows us to combine two or more relations using multiple SELECT queries into a single result set. By default, it has a feature to remove the duplicate rows from the result set.

Union clause in MySQL must follow the rules given below:

  • The order and number of the columns must be the same in all tables.
  • The data type must be compatible with the corresponding positions of each select query.
  • The column name in the SELECT queries should be in the same order.

Syntax

What is the Join clause?

Join in MySQL is used with SELECT statement to retrieve data from multiple tables. It is performed whenever we need to fetch records from more than one tables. It returns only those records from the tables that match the specified conditions.

Syntax

Union vs. Join

Let us discuss the essential differences between Union and Join using the following comparison chart.

MySQL Union vs Join
SN UNION JOIN
1. It is used to combine the result from multiple tables using SQL queries. It is used to fetch the record from more than one table using SQL queries.
2. It combines the records into new rows. It combines the records into new columns.
3. It allows us to connect the tables vertically. It will enable us to join the tables vertically.
4. It works as the conjunction of the more than one tables that sum ups all records. It produces results in the intersection of the tables.
5. In this, the order and number of the columns must be the same in all tables. In this, the order and number of the columns do not need to be the same in all tables.
6. It has a default feature to remove the duplicate rows from the result set. It does not eliminate the duplicate rows from the result set.
7. In this, the data type must be the same in all SELECT statements. In this, there is no need to be the same data type. It can be different.
8. The Join clause is applicable only when the two tables that are going to be used have at least one column. The Union clause is applicable only when the number of columns and corresponding attributes has the same domain.
9. The Join clause can have different types that are given below:
  • Inner Join (Sometimes Join)
  • Left Join (Left Outer Join)
  • Right Join (Right Outer Join)
  • Full Join (Outer Join)
The Union clause can have mainly two types that are given below:
  • Union
  • Union All

Now, we are going to understand it with the help of an example.

Union Example

Suppose our database has the following tables: "Student1" and "Student2" that contains the data below:

MySQL Union vs Join

The following statement produces output that contains all student names and subjects by combining both tables.

After the successful execution, we will get the output that contains all unique student names and subjects:

MySQL Union vs Join

Join Example

Suppose our database has the following tables: "Students" and "Technologies" that contains the data below:

MySQL Union vs Join

We can fetch records from both tables using the following query:

We will get the following output:

MySQL Union vs Join
Next TopicMySQL Copy Table




Help Others, Please Share

facebook twitter pinterest

Learn Latest Tutorials


Preparation


Trending Technologies


B.Tech / MCA