SQL连接表的最大数量限制是否适用于整个查询，还是将子查询单独计数? [英] Does the limit on the maximum number of SQL join tables apply to the whole query, or are subqueries counted separately?

问题描述

我已阅读 61表联接限制在MySQL 5中，但我不确定它将如何应用于以下情况:

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...
)

我会在所有子查询中达到61个表的限制，还是每个UNIONed子查询61个?

这在DB之间是否有所不同，例如PostgreSQL，MSSQL，Oracle?

解决方案

每个子查询似乎为61.这是一个小提琴来演示这一点.

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

我有一张简单的表，只有一行:

id    | value
1     | testvalue

第一个查询只是表.

第二个查询将表自身连接61次.效果很好.

第三个查询有一个包含61个联接的子查询，该联接本身又一次与表联接.效果很好.

第四个查询联接表62次.它失败了.

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?

解决方案

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.

这篇关于SQL连接表的最大数量限制是否适用于整个查询，还是将子查询单独计数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！