Repository: sqlfluff/sqlfluff

Issue:
Rule: No nested selects within join clauses.
This is a pretty general, high-level quality check. I often see this issue with SQL written by new data scientists or engineers -- they'll deliver a working query, but it's monolithic and 50+ lines long. Even with proper indentation, it's hard to grasp what going on.

When this occurs, SQLFluff should suggest breaking the query into two or more logical chunks, with some of these chunks written as (descriptively named) CTEs.

Discussion:

Patch:
diff --git a/src/sqlfluff/core/default_config.cfg b/src/sqlfluff/core/default_config.cfg
index a39bc0f82..04d3bef42 100644
--- a/src/sqlfluff/core/default_config.cfg
+++ b/src/sqlfluff/core/default_config.cfg
@@ -59,3 +59,7 @@ select_clause_trailing_comma = forbid
 
 [sqlfluff:rules:L040]  # Null & Boolean Literals
 capitalisation_policy = consistent
+
+[sqlfluff:rules:L042]
+# By default, allow subqueries in from clauses, but not join clauses.
+forbid_subquery_in = join
diff --git a/src/sqlfluff/core/dialects/dialect_ansi.py b/src/sqlfluff/core/dialects/dialect_ansi.py
index 016383b07..a15793e8c 100644
--- a/src/sqlfluff/core/dialects/dialect_ansi.py
+++ b/src/sqlfluff/core/dialects/dialect_ansi.py
@@ -812,8 +812,8 @@ class TableExpressionSegment(BaseSegment):
     match_grammar = Sequence(
         Ref("PreTableFunctionKeywordsGrammar", optional=True),
         OneOf(
-            Ref("MainTableExpressionGrammar"),
-            Bracketed(Ref("MainTableExpressionGrammar")),
+            Ref("MainTableExpressionSegment"),
+            Bracketed(Ref("MainTableExpressionSegment")),
         ),
         Ref("AliasExpressionSegment", optional=True),
         Ref("PostTableExpressionGrammar", optional=True),
@@ -847,7 +847,7 @@ class TableExpressionSegment(BaseSegment):
 
 
 @ansi_dialect.segment()
-class MainTableExpressionGrammar(BaseSegment):
+class MainTableExpressionSegment(BaseSegment):
     """The main table expression e.g. within a FROM clause."""
 
     type = "main_table_expression"
diff --git a/src/sqlfluff/core/dialects/dialect_exasol.py b/src/sqlfluff/core/dialects/dialect_exasol.py
index a34eb1d9a..71cdc6044 100644
--- a/src/sqlfluff/core/dialects/dialect_exasol.py
+++ b/src/sqlfluff/core/dialects/dialect_exasol.py
@@ -226,7 +226,7 @@ class SelectStatementSegment(BaseSegment):
 
 
 @exasol_dialect.segment(replace=True)
-class MainTableExpressionGrammar(BaseSegment):
+class MainTableExpressionSegment(BaseSegment):
     """The main table expression e.g. within a FROM clause."""
 
     type = "main_table_expression"
diff --git a/src/sqlfluff/core/rules/config_info.py b/src/sqlfluff/core/rules/config_info.py
index 3a797518e..6f856932d 100644
--- a/src/sqlfluff/core/rules/config_info.py
+++ b/src/sqlfluff/core/rules/config_info.py
@@ -71,4 +71,8 @@ STANDARD_CONFIG_INFO_DICT = {
             " be ignored when linting line lengths."
         ),
     },
+    "forbid_subquery_in": {
+        "validation": ["join", "from", "both"],
+        "definition": "Which clauses should be linted for subqueries.",
+    },
 }
diff --git a/src/sqlfluff/core/rules/std/L042.py b/src/sqlfluff/core/rules/std/L042.py
new file mode 100644
index 000000000..c2b231707
--- /dev/null
+++ b/src/sqlfluff/core/rules/std/L042.py
@@ -0,0 +1,81 @@
+"""Implementation of Rule L042."""
+
+from sqlfluff.core.rules.base import BaseCrawler, LintResult
+
+
+class Rule_L042(BaseCrawler):
+    """Join/From clauses should not contain subqueries. Use CTEs instead.
+
+    By default this rule is configured to allow subqueries within `FROM`
+    clauses but not within `JOIN` clauses. If you prefer a stricter lint
+    then this is configurable.
+
+    NB: Some dialects don't allow CTEs, and for those dialects
+    this rule makes no sense and should be disabled.
+
+    | **Anti-pattern**
+
+    .. code-block:: sql
+
+        select
+            a.x, a.y, b.z
+        from a
+        join (
+            select x, z from b
+        ) using(x)
+
+
+    | **Best practice**
+
+    .. code-block:: sql
+
+        with c as (
+            select x, z from b
+        )
+        select
+            a.x, a.y, c.z
+        from a
+        join c using(x)
+
+    """
+
+    config_keywords = ["forbid_subquery_in"]
+
+    _config_mapping = {
+        "join": ["join_clause"],
+        "from": ["from_clause"],
+        "both": ["join_clause", "from_clause"],
+    }
+
+    def _eval(self, segment, **kwargs):
+        """Join/From clauses should not contain subqueries. Use CTEs instead.
+
+        NB: No fix for this routine because it would be very complex to
+        implement reliably.
+        """
+        parent_types = self._config_mapping[self.forbid_subquery_in]
+        for parent_type in parent_types:
+            if segment.is_type(parent_type):
+                # Get the referenced table segment
+                table_expression = segment.get_child("table_expression")
+                if not table_expression:
+                    return None  # There isn't one. We're done.
+                # Get the main bit
+                table_expression = table_expression.get_child("main_table_expression")
+                if not table_expression:
+                    return None  # There isn't one. We're done.
+
+                # If any of the following are found, raise an issue.
+                # If not, we're fine.
+                problem_children = [
+                    "with_compound_statement",
+                    "set_expression",
+                    "select_statement",
+                ]
+                for seg_type in problem_children:
+                    seg = table_expression.get_child(seg_type)
+                    if seg:
+                        return LintResult(
+                            anchor=seg,
+                            description=f"{parent_type} clauses should not contain subqueries. Use CTEs instead",
+                        )


Test patch:
diff --git a/test/core/rules/test_cases/L042.yml b/test/core/rules/test_cases/L042.yml
new file mode 100644
index 000000000..7257d92ad
--- /dev/null
+++ b/test/core/rules/test_cases/L042.yml
@@ -0,0 +1,89 @@
+rule: L042
+
+select_fail:
+  fail_str: |
+    select
+        a.x, a.y, b.z
+    from a
+    join (
+        select x, z from b
+    ) on (a.x = b.x)
+
+select_multijoin_fail:
+  fail_str: |
+    select
+        a.x, d.x as foo, a.y, b.z
+    from a
+    join d using(x)
+    join (
+        select x, z from b
+    ) using (x)
+  
+with_fail:
+  fail_str: |
+    select
+        a.x, a.y, b.z
+    from a
+    join (
+        with d as (
+            select x, z from b
+        )
+        select * from d
+    ) using (x)
+  
+set_fail:
+  fail_str: |
+    select
+        a.x, a.y, b.z
+    from a
+    join (
+        select x, z from b
+        union
+        select x, z from d
+    ) using (x)
+
+simple_pass:
+  pass_str: |
+    with c as (
+        select x, z from b
+    )
+    select
+        a.x, a.y, c.z
+    from a
+    join c on (a.x = c.x)
+
+from_clause_pass:
+  pass_str: |
+    select
+        a.x, a.y
+    from (
+        select * from b
+    ) as a
+  configs:
+    rules:
+      L042:
+        forbid_subquery_in: join
+
+from_clause_fail:
+  fail_str: |
+    select
+        a.x, a.y
+    from (
+        select * from b
+    ) as a
+  configs:
+    rules:
+      L042:
+        forbid_subquery_in: from
+
+both_clause_fail:
+  fail_str: |
+    select
+        a.x, a.y
+    from (
+        select * from b
+    ) as a
+  configs:
+    rules:
+      L042:
+        forbid_subquery_in: both