Difference Between Join and Union

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)

MySQL - UNION vs JOINS

UNION adds rows from multiple tables/views.
Whereas join make the filters between rows from different related tables in a single sql statement.

Union: used to combine the result set of two different SELECT statement with same datatype of result set.

Join: used to retrieve matched records between 2 or more tables.

Please visit this link, it will help you to clear your doubts.

Use A Union Or A Join - What Is Faster

Union will be faster, as it simply passes the first SELECT statement, and then parses the second SELECT statement and adds the results to the end of the output table.

The Join will go through each row of both tables, finding matches in the other table therefore needing a lot more processing due to searching for matching rows for each and every row.

EDIT

By Union, I mean Union All as it seemed adequate for what you were trying to achieve. Although a normal Union is generally faster then Join.

EDIT 2 (Reply to @seebiscuit 's comment)

I don't agree with him. Technically speaking no matter how good your join is, a "JOIN" is still more expensive than a pure concatenation. I made a blog post to prove it at my blog codePERF[dot]net. Practically speaking they serve 2 completely different purposes and it is more important to ensure your indexing is right and using the right tool for the job.

Technically, I think it can be summed using the following 2 execution plans taken from my blog post:

UNION ALL Execution Plan

JOIN Execution Plan

Practical Results

Practically speaking the difference on a clustered index lookup is negligible:

When is it best to use JOIN or UNION?

If I understand correctly, you want left joins and coalesce():

select t.*, coalesce(p.passportid, dp.passportid) as passportid
from transactions t left join
     primaries p
     on t.borroweruuid = p.uuid left join
     dependents d
     on t.borroweruuid = d.uuid left join
     primaries pd
     on pd.id = d.primaryid;

The first left join matches directly to the primaries table. The second matches through the dependents.

What is the difference between UNION and UNION ALL?

UNION removes duplicate records (where all columns in the results are the same), UNION ALL does not.

There is a performance hit when using UNION instead of UNION ALL, since the database server must do additional work to remove the duplicate rows, but usually you do not want the duplicates (especially when developing reports).

To identify duplicates, records must be comparable types as well as compatible types. This will depend on the SQL system. For example the system may truncate all long text fields to make short text fields for comparison (MS Jet), or may refuse to compare binary fields (ORACLE)

UNION Example:

SELECT 'foo' AS bar UNION SELECT 'foo' AS bar

Result:

+-----+
| bar |
+-----+
| foo |
+-----+
1 row in set (0.00 sec)

UNION ALL example:

SELECT 'foo' AS bar UNION ALL SELECT 'foo' AS bar

Result:

+-----+
| bar |
+-----+
| foo |
| foo |
+-----+
2 rows in set (0.00 sec)

SQL Server - Using SQL JOIN and UNION

In the SELECT and JOIN you are using the income.id instead rev.id.

Can you try the query below:

SELECT id, result
FROM (
    SELECT rev.id, rev.total_rev - vendor_4.total_spend AS result
    FROM (
        SELECT id, SUM(rev) AS total_rev
        FROM income
        GROUP BY id
        ) AS rev
    JOIN (
        SELECT id, SUM(spend) AS total_spend
        FROM (
            SELECT id, spend
            FROM vendor_1

            UNION ALL

            SELECT id, spend
            FROM vendor_2

            UNION ALL

            SELECT id, spend
            FROM vendor_3
            ) AS SpendTotal
        GROUP BY id
        ) AS vendor_4 ON vendor_4.id = rev.id
    ) R
ORDER BY result DESC

Union v/s Inner join on 3 conditions in SQL

At least it depends on A or B originally having doubles. For example

with A(c) as (
 select 1 union all
 select 1 union all
 select 2 
 ),
B(c)  as (
 select 1 union all
 select 2 union all
 select 3 
 )
select *
from A join B on A.c=B.c
union
select *
from A join B on A.c>B.c

returns 3 rows (distinct).

with A(c) as (
 select 1 union all
 select 1 union all
 select 2 
 ),
B(c)  as (
 select 1 union all
 select 2 union all
 select 3 
 )
select *
from A join B on A.c=B.c or A.c>B.c

returns 4 rows due to A having doubles.

---

Related Topics