/    /  MySQL – Union vs join

MySQL – Union vs join

 

v1

The union and join SQL clauses are used to perform operations on more than one table in a relational database management system (RDBMS). By combining data from two or more tables, they produce a result. Both clauses, however, use different methods for combining data from two or more relations. In order to make a comparison, we will briefly discuss these clauses.

Union clause:

With the MySQL Union clause, we can combine two or more relations using multiple SELECT queries into a single result set. By default, it removes duplicate rows from the result set.

  • All tables must have the same number and order of columns.
  • There must be a compatibility between the data type and the corresponding positions of each select query.
  • The column names should be in the same order in the SELECT queries.

Syntax

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

Join clause

The join statement in MySQL is used to retrieve data from multiple tables using the SELECT statement. It is performed whenever we need to retrieve records from multiple tables at the same time. Only records matching the specified conditions are returned from the tables.

Syntax

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

Union vs. Join

UNIONJOIN
Using SQL queries, it is used to combine the results from multiple tables.Using SQL queries, it retrieves records from multiple tables at once.
A new row is created by combining the records.The records are combined into new columns.
As a result, the tables can be connected vertically.This will allow us to join the tables vertically.
As a result, multiple tables are combined and all records are summed.This method produces results based on the intersection of two tables.
There must be the same number and order of columns in all tables in this case.In this case, the order and number of columns do not need to be the same in all tables.
By default, it removes duplicate rows from the result set.The duplicate rows are not eliminated from the result set.
The data type must be the same in all SELECT statements.This does not require that the data types be the same. There can be differences.
Union clauses apply only when the number of columns and corresponding attributes are the same.Join clauses are applicable only when at least one column exists in each of the two tables that are going to be used.
There are two main types of union clauses, as follows:

  • Union
  • Union All
There are several types of Join clauses, as follows:

  • Inner Join (Sometimes Join)
  • Left Join (Left Outer Join)
  • Right Join (Right Outer Join)
  • Full Join (Outer Join)