Difference between MySQL Union and Join

Data Science, AI and ML
#1

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

SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2;

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

SELECT column_name(s) FROM table_name1
JOIN table_name2 ON conditions;

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 Union clause is applicable only when the number of columns and corresponding attributes has the same domain. The Join clause is applicable only when the two tables that are going to be used have at least one column.
9. The Union clause can have mainly two types that are given below:

  • Union

  • Union All|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)|

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.

SELECT stud_name, subject FROM student1
UNION
SELECT stud_name, subject FROM student2;

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:

SELECT students.stud_fname, students.stud_lname, students.city, technologies.technology
FROM students
JOIN technologies
ON students.student_id = technologies.tech_id;

We will get the following output:

MySQL Union vs Join