Revert move-recursive-ns-query-to-inner-join [RUN ALL RSPEC] [RUN AS-IF-FOSS]

What does this MR do?

This change reverts !56078 (merged) in favor of !56509 (merged)

The INNER JOIN approach also produces faulty queries in some rare cases: #325268 (closed) (can be closed when the revert is merged)

Screenshots (strongly suggested)

Does this MR meet the acceptance criteria?

Conformity

• 📋 Does this MR need a changelog?
  • I have included a changelog entry.
  • [-] I have not included a changelog entry because we revert a feature flag.
• [-] Documentation (if required)
• Code review guidelines
• Merge request performance guidelines
• Style guides
• [-] Database guides
• [-] Separation of EE specific content