Difference Between Union And Join In SQL

by

UNION

The SQL UNION operator is used to combine the result sets of two or more SELECT statements. It removes duplicate rows between the various SELECT statements. Each SELECT statement within the UNION must have the same number of fields in the result sets with similar data types.

What You Need To Know To Know About Union In SQL

  • UNION is used to combine multiple result sets into single result. Each select statement which are using union must have the same number of columns in same order with compatible (similar) data types.
  • Types of UNION are UNION and UNION all.
  • It combines data into new columns.
  • UNION is used to combine the result of two or more queries.
  • The UNION is applicable when two relations (tables) have the same number of attributes and domains of corresponding attributes are same.
  • UNION removes duplicate rows between the various select statements.
  • In UNION, the number of the tuples is more when compared to the number of tuples present in each relation involved in the query.
  • The output of UNION is a new vertical set of rows having same set of columns but can have different number of rows.

JOIN

SQL JOIN combines columns from one or more tables in a relational database. It creates a set that can be saved as a table or used as it is. A JOIN is a means for combining columns from one (self-join) or more tables by using values common to each.

Types of join

  • INNER JOIN: Select records that have matching values in both tables.
  • FULL (OUTER) JOIN: Selects all records that match either left or right table records.
  • RIGHT (OUTER) JOIN: Select records from the second (right-most) table with matching left table records.
  • LEFT (OUTER) JOIN: Select records from the first (left-most) table with matching right table records.

A programmer declares a JOIN statement to identify rows for joining. If the evaluated predicate is true, the combined row is then produced in the expected format, a row set or a temporary table.

What You Need To Know To Know About Join In SQL

  • JOINs are used to retrieve data from two or more tables (combine rows from multiple tables).
  • There are four types of JOINs available in MS SQL server. They include: INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN.
  • JOIN combines data into new rows.
  • JOIN is used to fetch data from different tables using SQL query.
  • The JOIN clause is applicable only when two relations (tables) involved have at least one attribute common in both.
  • JOIN does not remove duplicate data.
  • In JOIN, the length of the resultant tuples is more as compared to the length of the tuples of the involved relations.
  • The output of JOIN is a new horizontal set of rows having same number of rows but can have different number of columns.

Also Read: Difference Between Normalization And Denormalization

Difference Between Union And Join In Tabular Form

BASIS OF COMPARISON UNION JION
Description UNION is used to combine multiple result sets into single result. Each select statement which are using union must have the same number of columns in same order with compatible (similar) data types.   JOINs are used to retrieve data from two or more tables (combine rows from multiple tables).  
Types Types of UNION are UNION and UNION all.   There are four types of JOINs available in MS SQL server. They include: INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN.  
Data It combines data into new columns.   JOIN combines data into new rows.  
Use UNION is used to combine the result of two or more queries.   JOIN is used to fetch data from different tables using SQL query.  
Applicability The UNION is applicable when two relations (tables) have the same number of attributes and domains of corresponding attributes are same.   The JOIN clause is applicable only when two relations (tables) involved have at least one attribute common in both.  
Duplicate Data UNION removes duplicate rows between the various select statements.   JOIN does not remove duplicate data.  
Tuple In UNION, the number of the tuples is more when compared to the number of tuples present in each relation involved in the query.   In JOIN, the length of the resultant tuples is more as compared to the length of the tuples of the involved relations.  
Output The output of UNION is a new vertical set of rows having same set of columns but can have different number of rows.   The output of JOIN is a new horizontal set of rows having same number of rows but can have different number of columns.