School of Information Systems

The Difference Between Join and Union

22 Feb 2024

Join  

  • Join is a SQL command query to combine data from two or more tables performed with certain columns that has relationship connected by a key (to the side) into a new complete data set. 
  • Join adds the result data set horizontally. 
  • Join can be used even though the number of columns/data types in the combined table is different. 
  • Join requires an associated value (PK FK) in tables to combine data from two or more tables. 
  • Join has several types, namely Inner Join, Left Outer Join, Right Outer Join, and Full Join. 

Example: 

Table: 

In the sample table that I provide, there are MsFish tables and MsFishType tables that have the same column (PK FK) namely FishTypeID. The use of JOIN requires certain columns that have a relationship with each other in the linked table, so in this example the FishTypeID column will be used which connects the two tables to be combined. 

Query JOIN: 

Union 

  • Union is a SQL command query to combine data from two or more tables (select queries) that have the same number of columns with different values ​​into a single new result set (downward). 
  • Union adds the result data set vertically. 
  • Union usage must have the same number of columns in the combined table. Also, the columns must have the same data type and the merged columns need to be in the same order. 
  • Union does not need to use related values ​​(PK FK) in tables to combine data from two or more tables. 
  • Union has two types, namely Union (all data but not dupliate data) and All Union (all data and the data can be duplicate data). 

Example: 

Table: 

In the sample table that I provide, I would like to see the joining of data from two tables with columns that meet the requirements for UNION. The column data that I want to display in the example is the FishID column combined with the FishTypeID. Both columns have the same data type with the same order and number of columns. The number of columns I want to display is only one so that in the query that is merged later. Kemudian, hasil query yang didapat ada dari FishID (FI010, FI013) dan FishTypeID (FT001). 

Query UNION: 

 

References: 

Lily Janvieka