-1

For an example:

In table a we have 1000000 rows In table b we have 5 rows

It's more faster if we use

select * from b inner join a on b.id = a.id

than

select * from a inner join b on a.id = b.id

Vebbie
  • 1,669
  • 2
  • 12
  • 18
Yoga Black Crow
  • 1
  • 3
    Depends on your RDBMS. Check your explain plan/execution plan. – Robby Cornelissen Dec 26 '18 at 06:09
  • 2
    The job of a SQL DB engine is to optimise queries. All of the modern SQL DBs will make a plan that will execute those two the same (faster) way. There will be queries that the optimiser won't be able to optimise and a human will; this is not one of them. – Amadan Dec 26 '18 at 06:11
  • 1
    Tip: Tag your question with the brand of SQL database you use. For example, [tag:sql-server], [tag:mysql], [tag:oracle], [tag:postgresql]. Each SQL product implements different optimizations, so you need to be specific about which brand you use to get the best answer. – Bill Karwin Dec 26 '18 at 06:23
  • 2
    That said, I would be very surprised if any of the major SQL implementations couldn't reorder the tables to optimize this query. They should all know that `inner join` is commutative. – Bill Karwin Dec 26 '18 at 06:24
  • Does this answer your question? [Does INNER JOIN performance depends on order of tables?](https://stackoverflow.com/questions/19612208/does-inner-join-performance-depends-on-order-of-tables) – philipxy Apr 13 '20 at 23:50

2 Answers2

0

No, JOIN order doesn't matter, the query engine will reorganize their order based on statistics for indexes and other stuff. JOIN by order is changed during optimization.

You might test it all by yourself, download some test databases like AdventureWorks or Northwind or try it on your database, you might do this:

  • select show actual execution plan and run first query
  • change JOIN order and now run the query again
  • compare execution plans

They should be identical as the query engine will reorganize them according to other factors.

The only caveat is the Option FORCE ORDER which will force joins to happen in the exact order you have them specified.

Roxy'Pro
  • 4,216
  • 9
  • 40
  • 102
0

It is unlikely. There are lots of factors on the speed of joining two tables. That is why database engines have an optimization phase, where they consider different ways of implementing the query.

There are many different options:

  • Nested loops, scanning b first and then a.
  • Nested loops, scanning a first and then b.
  • Sorting both tables and using a merge join.
  • Hashing both tables and using a hash join.
  • Using an index on b.id.
  • Using an index on a.id.

And these are just high level descriptions -- there are multiple ways to implement some of these methods. Tables can also be partitioned adding further complexity.

Join order is just one consideration.

In this case, the result of the query is likely to depend on the size of the data being returned, rather than the actual algorithm used for fetching the data.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786