Difference Between JOIN and UNION in SQL

---

---

Both JOIN and UNION are used in SQL to combine data from multiple tables, but they differ significantly in how the data is combined and presented.

• JOIN combines columns from two or more tables based on a related column between them.
• UNION combines rows from two or more queries, stacking the results vertically, regardless of whether the tables are directly related.

What is JOIN in SQL?

A JOIN in SQL is used to combine columns from two or more tables based on a common field between them.

• It returns a single result set with columns from multiple tables.
• If there are rows in one table without a corresponding match in another table, the type of join (INNER, LEFT, RIGHT, FULL) determines whether those rows appear in the result.
• JOINs are usually used to create relational views of data where information from multiple tables needs to be displayed together.

There are two main types of JOINs in SQL:

• Inner Join
• Outer Join

An Inner Join is the basic type of join and retrieves only the rows where there is a match in the common columns of both tables. It is the default join type.

An Outer Join returns both matched and unmatched rows from the tables, depending on the type. It has three subtypes:

• Left Join: Returns all rows from the left table, with matching rows from the right table. Unmatched right table rows appear as NULL.
• Right Join: Returns all rows from the right table, with matching rows from the left table. Unmatched left table rows appear as NULL.
• Full Join: Returns all rows when there is a match in either table. Unmatched rows from both tables appear as NULL where there is no match.

Syntax

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

SELECT table1.column1, table2.column2...
FROM table1
JOIN table2
ON table1.column_name = table2.column_name;

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 join the tables COURSES_PICKED and EXTRA_COURSES_PICKED, using the JOIN query as follows:

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

The resultant table will be displayed as follows:

What is UNION in SQL

A UNION in SQL is used to combine the results of two or more SELECT queries into a single result set.

• It returns all rows from multiple SELECT queries as a single vertical stack.
• Each SELECT query must have the same number of columns, and the corresponding columns must have same data types.
• By default, UNION removes duplicate rows. Using UNION ALL preserves duplicates.

In UNION operation, 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 combine the tables COURSES_PICKED and EXTRA_COURSES_PICKED created above using the UNION query as follows:

SELECT * FROM COURSES_PICKED 
UNION 
SELECT * FROM EXTRA_COURSES_PICKED;

The resultant table obtained after performing the UNION operation is:

Difference Between JOIN and UNION in SQL

JOIN and UNION are both used to combine data from multiple tables in SQL, but they work in very different ways. The table below summarizes their differences:

Conclusion

JOIN and UNION in SQL are both used to combine data, but in different ways. JOIN merges columns horizontally from multiple tables based on a relationship, while UNION stacks rows vertically from multiple SELECT queries.

Choosing between them depends on whether you want to combine data side-by-side from related tables (JOIN) or stack multiple query results into a single list (UNION).