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:
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.
Union vs. Join
Let us discuss the essential differences between Union and Join using the following comparison chart.
Now, we are going to understand it with the help of an example.
Suppose our database has the following tables: "Student1" and "Student2" that contains the data below:
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:
Suppose our database has the following tables: "Students" and "Technologies" that contains the data below:
We can fetch records from both tables using the following query:
We will get the following output: