Sunny Books
What we have

SQL JOIN vs UNION

SQL JOIN and UNION are two different operations. A Union and Join are similar in that they both are combining two tables to form another table. However they do this combining in different ways.

Joins are used to extract information from more than one table based on the related column or columns (primary key and foreign key). The output set displayed will have all the columns from the tables in the SELECT statement shown individually. The columns will be aligned next to each other ( horizontally).

The union operator combines the results of two or more SELECT statements into a single result set. All select statements must have same number of of columns, with similar data types, and in the same order. The columns in the output set are determined by the first SELECT statement, and the second SELECT statement may contain ORDER BY for the entire result. The rows in the output set are coming from row results from one table with row results of another table (vertically).

The followings are the differences between columns and rows of JOIN and UNION

Columns:

JOIN

In Join the rows of one table may be different from the rows of another table. The result table can contain columns from both the first and second tables. It can contain all the columns of first table and all the columns of second tables.

Union

In Union the Number of columns in the result table is the same as the number in the first SELECT statement. No new Columns are added.

Rows:

JOIN

The number of rows can be retrieved based on matching columns. Maximum Number of rows = Product of rows in two tables.

UNION

The number of rows can be retrieved is the sum of number of rows in the two tables. The UNION operator returns distinct values from two tables, while UNION ALL returns all values from two tables including duplicates.

SUNWEB EXPERT