inner join VS union all, when the result is not found

Question

I have two tables with the same column name. I want to search in them.I've research and realized that using UNION ALL is better that INNER JOIN (in terms of speed and performance). Now i want to know if the result is not found, UNION ALL is better yet ?

I think the algorithm of JOIN Keyword is: if the result is not found in the first table, operation stops (in the second table, the search will not be). But UNION ALL search all the tables in any case.my thought is right ? If my thought is right then INNER JOIN is better when the result is not found ?

point: col is indexed (phpmyadmin).

Now which one have better performance if the result is not found ?

SELECT col FROM table1 WHERE col='test'
UNION ALL
SELECT col FROM table2 WHERE col='test';

VS

SELECT col
FROM table1
INNER JOIN table2
ON table1.col=table2.col
WHERE col='test';

Please edit your question and add sample data and desired results. What do you want the query to return when the result is not found? — Gordon Linoff, Apr 30 '15 at 19:06
Your INNER JOIN example isn't going to give the same results, it will give an INTERSECTION rather than a UNION. — MatBailie, Apr 30 '15 at 19:12

GolezTrol · Accepted Answer · 2015-04-30T19:16:00.840

UNION ALL is generally faster. It doesn't require any complex lookups. It just concatinates two query results. Whether data is queried from both tables in case of INNER JOIN greatly depends on the database, let alone specific details (indexes used, etc).

That said, your queries are not the same at all. The version with UNION ALL will return two rows if both tables contain such a record and will still return one row if either table contains a match.

The version with INNER JOIN will only return a row if both tables contain a matching record, and will not return a row if only one of them (or none) contains a matching record.

NB, the one with INNER JOIN uses the unaliased column col, which will probably result in an error.

On a general note, don't start optimizing based on theory. Write clear and readable queries that apply as good as possible to the situation (semantically correct). Only optimize when you need to.

Also, be careful that you don't optimize the wrong thing. Even if the INNER JOIN version would be slightly faster when no data is found, the UNION ALL version will still be lightning fast anyway, so it's better to choose the more optimal one for situations where you do have a match.

I just add (since @GolezTrol already answered) that with `UNION ALL` the only way to `ORDER` is **at the end** (after union) contrary to `INNER JOIN` (where you can already order your rows **before being joined**) The best answer will be given by `execution plan`. — MacKentoch, Apr 30 '15 at 19:15

inner join VS union all, when the result is not found

1 Answers1