Does the limit on the maximum number of SQL join tables apply to the whole query, or are subqueries counted separately?

user_693349 0
Viewed 5592

I have read about the 61 table join limit in MySQL 5, but I'm not sure how it would apply to the folllowing:

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 hit the limit with 61 tables in total across all the subqueries, or would it be 61 per UNIONed subquery?

Does this vary across DBs e.g. PostgreSQL, MSSQL, Oracle?

join mysql sql
2 Answers

It seems to be 61 per subquery. Here's a fiddle demonstrating this.

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

I have a simple table with one row:

id    | value
1     | testvalue

The first query is just the table.

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

The third query has a subquery containing 61 joins, which is itself joined with the table one more time. It works fine.

The fourth query joins the table 62 times. It fails.

user_28436900
Related