Mysql – Inner Join versus Union All

inner-joinMySQLsqlunion

Which version of the query is faster / best practice? (Curiousity thing.)

~~More importantly, are they equivalent?~~

Do these queries accomplish the same thing in this example?

1) INNER JOIN with two OR conditions:

SELECT
  DISTINCT (cat.id) as 'Cat ID:'
FROM
  cat
INNER JOIN cuteness_showdown ON
  (cat.id = cuteness_showdown.cat_1 OR cat.id = cuteness_showdown.cat_2);

2) Query each column separately and UNION ALL:

SELECT
  DISTINCT (table_1.id) as 'Cat ID:'
FROM
  (SELECT
    cuteness_showdown.cat_1 AS id
  FROM
    cuteness_showdown
  UNION ALL
  SELECT
    cuteness_showdown.cat_2 AS id
    FROM
    cuteness_showdown) AS table_1;

Now, which version is faster / best practice if I need a column from another table?

1) INNER JOIN with two OR conditions (no change):

SELECT
  DISTINCT (cat.id) as 'Cat ID:',
  cat.name as 'Cat Name:'
FROM
  cat
INNER JOIN cuteness_showdown ON
  (cat.id = cuteness_showdown.cat_1 OR cat.id = cuteness_showdown.cat_2);

2) Query each column separately and UNION ALL (needed to INNER JOIN cat table):

SELECT
  DISTINCT (table_1.id) as 'Cat ID:'
  cat.name as 'Cat Name:'
FROM
  (SELECT
    cuteness_showdown.cat_1 AS id
  FROM
    cuteness_showdown
  UNION ALL
  SELECT
    cuteness_showdown.cat_2 AS id
  FROM
    cuteness_showdown) AS table_1
INNER JOIN cat on
  (table_1.id = cat.id);

Best Answer

To find out which is faster, break out a terminal, write a script that runs each 1000 times and compare the results :)

As for whether they are equivalent, the query optimiser will very often come up with the exact same execution plan for several SQL queries that do the same thing, so they may well be. I can't tell you whether these ones will get that treatment, but you can use EXPLAIN to see the execution plans for yourself and compare them, assuming you have some data.

If the execution plans are indeed the same, best practice is about choosing the more readable statement so that anyone else who comes along to maintain the code can do so easily. Alternatively, if they are not the same, then you have to decide whether a harder-to read statement is worth the extra performance gain, which depends on how big a deal performance is in your project. I'd argue that if you have a relatively small DB which is unlikely to scale much and sub 10ms response times, then performance isn't an issue so just make it easy to maintain.

Related Solutions

Sql – the difference between “INNER JOIN” and “OUTER JOIN”

Assuming you're joining on columns with no duplicates, which is a very common case:

An inner join of A and B gives the result of A intersect B, i.e. the inner part of a Venn diagram intersection.
An outer join of A and B gives the results of A union B, i.e. the outer parts of a Venn diagram union.

Examples

Suppose you have two tables, with a single column each, and data as follows:

Note that (1,2) are unique to A, (3,4) are common, and (5,6) are unique to B.

Inner join

An inner join using either of the equivalent queries gives the intersection of the two tables, i.e. the two rows they have in common.

select * from a INNER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a = b.b;

a | b
--+--
3 | 3
4 | 4

Left outer join

A left outer join will give all rows in A, plus any common rows in B.

select * from a LEFT OUTER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a = b.b(+);

a |  b
--+-----
1 | null
2 | null
3 |    3
4 |    4

Right outer join

A right outer join will give all rows in B, plus any common rows in A.

select * from a RIGHT OUTER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a(+) = b.b;

a    |  b
-----+----
3    |  3
4    |  4
null |  5
null |  6

Full outer join

A full outer join will give you the union of A and B, i.e. all the rows in A and all the rows in B. If something in A doesn't have a corresponding datum in B, then the B portion is null, and vice versa.

select * from a FULL OUTER JOIN b on a.a = b.b;

 a   |  b
-----+-----
   1 | null
   2 | null
   3 |    3
   4 |    4
null |    6
null |    5

Sql – 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