JOIN and UNION in SQL, what is the difference?

Have you ever been confused between JOIN and UNION function in SQL, when the ven diagrams of JOIN and UNION look like this:

It is a bit confused me the first time when I saw this, but let see how they work.

1. JOIN

A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

SELECT a.column1, a.column2, b.column3

FROM table1 AS a

JOIN table2 AS b

ON a.key1 = b.key2

Here are the different types of the JOINs in SQL:

• (INNER) JOIN: Returns records that have matching values in both tables
• LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table
• RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table
• FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table
• CROSS JOIN: Returns all possible combinations records from both tables

2. UNION

The UNION operator is used to combine the result set of two or more SELECT statements into a single result set including all the rows that belong to all queries. Remember:

• Every SELECT statement within UNION must have the same number of columns
• The columns must also have similar data types
• The columns in every SELECT statement must also be in the same order

SELECT column1, column2, column3

FROM table1

UNION

SELECT column1, column2, column3

FROM table2;

Here are the different types of “UNIONs” in SQL:

• UNION: combines the results of 2 or more queries into a single result-set including all the rows that belong to all queries in the UNION, it selects only distinct values.
• UNION ALL: like UNION but include all duplicate results.
• INTERSECT: Keep only results in common to both queries.
• EXCEPT: keep results from the first query that are not included in the second query

In conclusion, JOINs are used to combine tables based on logical relationships between tables, and UNIONs are used to combine queries.