SQL provides various relational operators to handle data that is spread across multiple tables in a relational database. Out of them, UNION and JOIN queries are fundamentally used to combine data from multiple tables.

Even though they are both used for the same purpose, i.e. to combine tables, there are many differences between the working of these operators. The major difference is that the UNION operator combines data from multiple similar tables irrespective of the data relativity, whereas, the JOIN operator is only used to combine relative data from multiple tables.

Working of UNION

UNION is a type of operator/clause in SQL, that works similar to the union operator in relational algebra. It does nothing more than just combining information from multiple tables that are union compatible.

The tables are said to be union compatible if they follow the conditions given below −

• The tables to be combined must have same number of columns with the same datatype.
• The number of rows need not be same.

Once these criteria are met, UNION operator returns all the rows from multiple tables, after eliminating duplicate rows, as a resultant table.

Note − Column names of first table will become column names of resultant table, and contents of second table will be merged into resultant columns of same data type.

Syntax

Following is the syntax of the SQL UNION operator −

SELECT * FROM table1
UNION
SELECT * FROM table2;

Example

Let us first create two table “COURSES_PICKED” and “EXTRA_COURSES_PICKED” with the same number of columns having same data types.

Create table COURSES_PICKED using the following query −

CREATE TABLE COURSES_PICKED(
   STUDENT_ID INT NOT NULL, 
   STUDENT_NAME VARCHAR(30) NOT NULL, 
   COURSE_NAME VARCHAR(30) NOT NULL
);

Insert values into the COURSES_PICKED table with the help of the query given below −

INSERT INTO COURSES_PICKED VALUES
(1, ''JOHN'', ''ENGLISH''),
(2, ''ROBERT'', ''COMPUTER SCIENCE''),
(3, ''SASHA'', ''COMMUNICATIONS''),
(4, ''JULIAN'', ''MATHEMATICS'');

Create table EXTRA_COURSES_PICKED using the following query −

CREATE TABLE EXTRA_COURSES_PICKED(
   STUDENT_ID INT NOT NULL, 
   STUDENT_NAME VARCHAR(30) NOT NULL, 
   EXTRA_COURSE_NAME VARCHAR(30) NOT NULL
);

Following is the query to insert values into the EXTRA_COURSES_PICKED table −

INSERT INTO EXTRA_COURSES_PICKED VALUES
(1, ''JOHN'', ''PHYSICAL EDUCATION''),
(2, ''ROBERT'', ''GYM''),
(3, ''SASHA'', ''FILM''),
(4, ''JULIAN'', ''PHOTOGRAPHY'');

Now, let us combine the tables COURSES_PICKED and EXTRA_COURSES_PICKED, using the UNION query as follows −

SELECT * FROM COURSES_PICKED 
UNION 
SELECT * FROM EXTRA_COURSES_PICKED;

Output

The resultant table obtained after performing the UNION operation is −

Working of JOIN

The Join operation is used to combine information from multiple related tables into one, based on their common fields. This operation can be used with various clauses like ON, WHERE, ORDER BY, GROUP BY etc.

There are two types of Joins −

• Inner Join
• Outer Join

The basic type of join is an Inner Join, which only retrieves the matching values of common columns. It is a default join.

The result table of the Outer join includes both matched and unmatched rows from the first table. It is divided into subtypes like Left Join, Right Join, and Full Join.

Syntax

Following is the basic syntax of a Join operation in SQL −

SELECT column_name(s)
FROM table1
JOIN table2
ON table1.column_name = table2.column_name;

Example

In the following example, we will join the same tables we created above, i.e., COURSES_PICKED and EXTRA_COURSES_PICKED, using the query below –

SELECT c.STUDENT_ID, c.STUDENT_NAME, COURSE_NAME, COURSES_PICKED 
FROM COURSES_PICKED c
JOIN EXTRA_COURSES_PICKED e
ON c.STUDENT_ID = e.STUDENT_ID;

Output

The resultant table will be displayed as follows −

UNION Vs JOIN

As we saw in the examples given above, the UNION operator is only executable on tables that are union compatible, whereas, the JOIN operator joins two tables that need not be compatible but should be related.

Let us summarize all the difference between these queries below −