Use A Union Or A Join – What Is Faster [closed]

by

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

UNION ALL Execution Plan

JOIN Execution Plan

JOIN Execution Plan

Practical Results

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

Benchmark Results

More Related Contents:

  1. MySQL: Alternatives to ORDER BY RAND()
  2. Simple way to calculate median with MySQL
  3. MySQL LIKE IN()?
  4. Auto Increment after delete in MySQL
  5. Unioning two tables with different number of columns
  6. In MySQL queries, why use join instead of where?
  7. MySQL “NOT IN” query
  8. Emulate MySQL LIMIT clause in Microsoft SQL Server 2000
  9. Importance of varchar length in MySQL table
  10. Stored Procedure with optional “WHERE” parameters
  11. mySQL :: insert into table, data from another table?
  12. Return 0 if field is null in MySQL
  13. MySQL “between” clause not inclusive?
  14. SQLite – ORDER BY RAND()
  15. How to search multiple columns in MySQL?
  16. MySQL – Selecting data from multiple tables all with same structure but different data
  17. Can a stored procedure/function return a table?
  18. Adding a new SQL column with a default value
  19. Sorting string column containing numbers in SQL?
  20. How to Optimize Queries in a Database – The Basics
  21. Multiple Ranks in one table
  22. Any way to achieve fulltext-like search on InnoDB
  23. The difference between ‘AND’ and ‘&&’ in SQL
  24. Can I use a MySQL database with an App Engine application
  25. How can I make an average of dates in MySQL?
  26. What is the best way to generate ranks in MYSQL?
  27. How to find all IDs of children recursively?
  28. How to implement a Keyword Search in MySQL?
  29. In SQL how do I get the maximum value for an integer?
  30. Get the first and last date of next month in MySQL

Leave a Comment