What is the difference between JOIN and UNION?

UNION puts lines from queries after each other, while JOIN makes a cartesian product and subsets it -- completely different operations. Trivial example of UNION:

mysql> SELECT 23 AS bah
    -> UNION
    -> SELECT 45 AS bah;
+-----+
| bah |
+-----+
|  23 | 
|  45 | 
+-----+
2 rows in set (0.00 sec)

similary trivial example of JOIN:

mysql> SELECT * FROM 
    -> (SELECT 23 AS bah) AS foo 
    -> JOIN 
    -> (SELECT 45 AS bah) AS bar
    -> ON (33=33);
+-----+-----+
| foo | bar |
+-----+-----+
|  23 |  45 | 
+-----+-----+
1 row in set (0.01 sec)

UNION combines the results of two or more queries into a single result set that includes all the rows that belong to all queries in the union.

By using JOINs, you can retrieve data from two or more tables based on logical relationships between the tables. Joins indicate how SQL should use data from one table to select the rows in another table.

The UNION operation is different from using JOINs that combine columns from two tables.

UNION Example:

SELECT 1 AS [Column1], 2 AS [Column2]
UNION
SELECT 3 AS [Column1], 4 AS [Column2]

Output:

Column1    Column2
-------------------
1          2
3          4

JOIN Example:

SELECT a.Column1, b.Column2 FROM TableA a INNER JOIN TableB b ON a.Id = b.AFKId

This will output all the rows from both the tables for which the condition a.Id = b.AFKId is true.

Union makes two queries look like one. Joins are for examining two or more tables in a single query statement

Remember that union will merge results (SQL Server to be sure)(feature or bug?)

select 1 as id, 3 as value
union
select 1 as id, 3 as value

id,value

1,3

select * from (select 1 as id, 3 as value) t1 inner join (select 1 as id, 3 as value) t2 on t1.id = t2.id

id,value,id,value

1,3,1,3

Union Operation is combined result of the Vertical Aggregate of the rows, Union Operation is combined result of the Horizontal Aggregate of the Columns.

Ussing UNION

UNION is combines the results of two or more queries into a single result set that includes all the rows that belong to all queries in the union.

UNION Example:
SELECT 121 AS [Column1], 221 AS [Column2]
UNION
SELECT 321 AS [Column1], 422 AS [Column2]

Output:

Column1    Column2
-------------------
121          221
321          422

Ussing JOINs

JOINs, you can retrieve data from two or more tables based on logical relationships between the tables.

JOIN Example:
SELECT a.Column1, b.Column2 FROM TblA a INNER JOIN TblB b ON a.Id = b.id

I like to think of the general difference as being:

• JOINS join tables
• UNION (et all) combines queries.