Is there a limit on the maximum number of SQL join tables per query or are sub-queries counted separately?

Question

I read about the 61 table join restriction in MySQL 5, but I'm not sure how this applies to the following:

SELECT * FROM ( SELECT * FROM tableA JOIN // Lots of other joins here... UNION SELECT * FROM tableB JOIN // Lots of other joins here... UNION SELECT * FROM tableC JOIN // Lots of other joins here... // etc... ) 

Would I overcome the limit of just 61 tables in all subqueries, or would it be 61 for a UNIONed subquery?

It depends on different databases, for example. PostgreSQL, MSSQL, Oracle?

Answer 1

There seems to be 61 in each subquery. The violin is shown here.

http://sqlfiddle.com/#!2/2b219/5

I have a simple table with one row:

 id | value 1 | testvalue 

The first query is just a table.

The second query joins the table itself 61 times. It works great.

The third query has a subquery containing 61 joins that are already joined to the table again. It works great.

The fourth query joins the table 62 times. He is failing.

Answer 2

Yes, this applies to the entire query. I came across this. In SQL 2005, the limit was 256. We just upgraded to 2008, and I'm not sure if there is a limit. Check out the official restrictions here: http://msdn.microsoft.com/en-us/library/ms143432.aspx However, the best way to do this is to either split the request into two or change the way it is written.