Can all SQL queries with joins be split into multiple SQL queries for execution?

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 所有带join的sql可否拆成若干sql执行

| username: grepti

Is it theoretically possible to split a join SQL involving several tables across two databases into multiple SQL executions?

| username: Kongdom | Original post link

I think it still depends on the specific business for analysis. Separating the left join might be okay, but keeping the inner join together should make the query a bit faster.

| username: waeng | Original post link

Theoretically, it is possible.

| username: tidb菜鸟一只 | Original post link

You mean, there are 3 tables in database A and 2 tables in database B, and you want to inner join these 5 tables by first inner joining the three tables in database A, then inner joining the two tables in database B, and finally inner joining these two result sets? Where are you joining these two result sets?

| username: xfworld | Original post link

In some scenarios, appropriate redundancy and widening can be more effective.

| username: tony5413 | Original post link

Theoretically, it is possible. It depends on the specific situation. If there are too many table joins, you still need to consider it from the table design perspective.

| username: grepti | Original post link

Join these two results in the business system.

| username: ouyangqing | Original post link

It is definitely possible to split it, but this requires the application to handle the association of multiple result sets, which likely means the application needs to be modified. Can the application agree to do this?

| username: BraveChen | Original post link

Different databases belong to different clusters, and then use application servers to perform calculations to solve cross-database issues?

| username: liuis | Original post link

I think we should let the code handle as much as possible since server resources are cheap.

| username: grepti | Original post link

Yes, what I don’t understand is whether the join SQL can be split into several sub-SQLs to be executed separately in two databases and then merged on the application side.

| username: xingzhenxiang | Original post link

The logic is clear, everything can be split.

| username: grepti | Original post link

The image you provided is not accessible. Please provide the text you need translated.

| username: xingzhenxiang | Original post link

In theory, these join operations can be split into multiple SQL queries for execution. However, the following factors need to be considered during the splitting process:

  1. Data Consistency: Multiple queries after splitting may lead to data inconsistency. It is necessary to ensure data integrity and correctness.
  2. Performance: Executing multiple queries may affect overall performance. Queries need to be optimized to avoid repeated data scans and other operations.
  3. Maintainability: The code after splitting may be more complex, requiring clear comments, readability, and ease of understanding for future maintenance.
  4. Transaction Management: If transaction consistency needs to be maintained between multiple queries, explicit transaction management is required in the program to ensure all operations succeed or roll back.

In summary, although join operations can be split into multiple SQL queries for execution, the above factors need to be comprehensively considered and evaluated to determine whether this approach is worthwhile.

| username: tidb菜鸟一只 | Original post link

If it’s an inner join, it will definitely work, but can the processing efficiency at the application layer meet the business requirements?

| username: Jellybean | Original post link

Introducing temporary intermediate tables, memory tables, or physical tables can theoretically split all types of join SQL.

However, in actual operations, to ensure data accuracy and resource usage balance, a considerable amount of analysis and verification work may be required, and a comprehensive evaluation based on the business context is necessary.

| username: 海石花47 | Original post link

I asked a similar question about splitting join SQL yesterday, and I’m here to learn.

| username: BraveChen | Original post link

Actually, it is not recommended to do so. You should know that performing some join calculations in the database will still have some optimizations.