Repository: sqlfluff/sqlfluff

Issue:
New Rule: In ON clause of JOIN statement specify source table first and join table second
<!--Note: This is for general enhancements to the project. Please use the Bug report template instead to raise parsing/linting/syntax issues for existing supported dialects-->
From GitLab SQL style guide: https://about.gitlab.com/handbook/business-technology/data-team/platform/sql-style-guide/#joins

`Specify the order of a join with the FROM table first and JOIN table second:`
```sql
  -- Good
  FROM source
  LEFT JOIN other_source
    ON source.id = other_source.id
  WHERE ...

  -- Bad
  FROM source
  LEFT JOIN other_source
    ON other_source.id = source.id
  WHERE ...
```

More generally put the SOURCE table first and JOIN table second (since the source isn't always necessarily with a FROM clause):
```sql
  -- Good
  FROM source
  LEFT JOIN other_source
    ON source.id = other_source.id
  LEFT JOIN different_source
    ON other_source.id = different_source.id
  WHERE ...

  -- Bad
  FROM source
  LEFT JOIN other_source
    ON other_source.id = source.id
  LEFT JOIN different_source
    ON different_source.id = other_source.id
  WHERE ...
```

The motivation is that we already know the JOIN table from the JOIN clause so we want to be able to quickly scan for the SOURCE table.

Discussion:
greg-finley: Thanks for the contribution! As a first step, could you look into the issues raised by the CI pipeline?
fmms: @thibonacci very nice. I like the idea. Another thing I have bbeen wishing for is to require ON clauses to be in brackets.

Regarding the current implmentation, I think one has to consider how this behaves on `ÌNNER JOIN` and `RIGHT JOIN`. To my understanding this will even be a challenge for the naming as `right` and `left` get hard to understand? Maybe we have a `basetable` and a `jointable` table?
thibonacci: > Thanks for the contribution! As a first step, could you look into the issues raised by the CI pipeline?

Thanks @greg-finley , I believe I've fixed all of the issues except for [this one](https://github.com/sqlfluff/sqlfluff/actions/runs/5573581086/job/15103595190) but it seems to only be affecting Python 3.7, which has reached end of life, so perhaps it's okay? If you could help rerun the CI pipeline and we can check if there are still any errors? Thanks!
thibonacci: > @thibonacci very nice. I like the idea. Another thing I have bbeen wishing for is to require ON clauses to be in brackets.
> 
> Regarding the current implmentation, I think one has to consider how this behaves on `ÌNNER JOIN` and `RIGHT JOIN`. To my understanding this will even be a challenge for the naming as `right` and `left` get hard to understand? Maybe we have a `basetable` and a `jointable` table?

Yes that's not a bad point, I'm just a bit disappointed not be to able to find official documentation using terminology that we are looking for here. Will continue to look...
greg-finley: > > Thanks for the contribution! As a first step, could you look into the issues raised by the CI pipeline?
> 
> Thanks @greg-finley , I believe I've fixed all of the issues except for [this one](https://github.com/sqlfluff/sqlfluff/actions/runs/5573581086/job/15103595190) but it seems to only be affecting Python 3.7, which has reached end of life, so perhaps it's okay? If you could help rerun the CI pipeline and we can check if there are still any errors? Thanks!

For consistency with the rest of the codebase, let's do `from typing import List` and `List[str]` etc.
thibonacci: > > > Thanks for the contribution! As a first step, could you look into the issues raised by the CI pipeline?
> > 
> > 
> > Thanks @greg-finley , I believe I've fixed all of the issues except for [this one](https://github.com/sqlfluff/sqlfluff/actions/runs/5573581086/job/15103595190) but it seems to only be affecting Python 3.7, which has reached end of life, so perhaps it's okay? If you could help rerun the CI pipeline and we can check if there are still any errors? Thanks!
> 
> For consistency with the rest of the codebase, let's do `from typing import List` and `List[str]` etc.

Thanks @greg-finley , I've done that and fixed more CI/CD errors. Hopefully only the py3.7 one is left.
coveralls: 
[![Coverage Status](https://coveralls.io/builds/61682002/badge)](https://coveralls.io/builds/61682002)

coverage: 99.951% (-0.05%) from 100.0% when pulling **edb99dd43fc0b37103245659a57b89b627181ba6 on thibonacci:main** into **1925c59a860a7ff3c90745bbb8c7d827c4a985c0 on sqlfluff:main**.

thibonacci: > [![Coverage Status](https://camo.githubusercontent.com/6bae0a4926054e11766bea69894190bdf8f71dc9f354efafaf8d19f2959491af/68747470733a2f2f636f766572616c6c732e696f2f6275696c64732f36313638323532362f6261646765)](https://coveralls.io/builds/61682526)
> 
> coverage: 99.952% (-0.05%) from 100.0% when pulling **[83f40c7](https://github.com/sqlfluff/sqlfluff/commit/83f40c7558293e282a220f62daddc4be07d980e7) on thibonacci:main** into **[a4563fa](https://github.com/sqlfluff/sqlfluff/commit/a4563fa30b2da435fc1541b717fa9648d4c91b97) on sqlfluff:main**.

@greg-finley I'm working on getting the coverage fixed; however, I'm now getting pre-commit errors about files that I've never worked on (see below screenshot). Do you have any idea as to what I should do in order to solve them? Thanks!
![Screenshot 2023-08-01 at 08 23 33](https://github.com/sqlfluff/sqlfluff/assets/60314995/6a8a8289-a32d-4593-aafb-8cfbaa065bb8)


alanmcruickshank: Thanks for getting this so far @thibonacci!

Having a read through I think this is a good rule for us to add - but we need to cover a couple of logical situations to make sure it's complete and ready to add.

1. I think the point raised by @fmms is important, and we should at least one test case each for the following joins:
   - `LEFT` (which we have)
   - `LEFT OUTER` - which should behave the same as `LEFT`
   - `INNER` - which should also behave the same as `LEFT`
   - `RIGHT` & `RIGHT OUTER` - which _may_ behave differently. I've done a bit of research, and based on the [snowflake docs](https://docs.snowflake.com/en/sql-reference/constructs/join) and the [bigquery docs](https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#right_join) it appears there is disagreement on which should come first - but that it's the _same_ for both `RIGHT` and `LEFT`. That means I think that from a configuration point of view, while we might still offer `left` and `right` - the default should be something more like `earlier` and `later` (I'm not attached to those terms, maybe `source` and `join`?), and the default should be `earlier`/`source` in line with #2395.
   - We should also test with `CROSS` and `FULL OUTER`.
2. We should also address the question of joins which do their filtering in the `WHERE` clause. My sense is that you haven't tried to tackle this as this stage, which is fine, but we should put a note in the docstring to that effect to say it's not covered. e.g. `SELECT * FROM a, b WHERE a.foo = b.foo`.
  - On this front, it could be interesting to also extend this logic to the `WHERE` clause too though, where we say any comparisons in there should _also_ follow the same rules. Up to you whether you apply it to `WHERE` for now, or just call out that it's not currently covered.
alanmcruickshank: Also heads up that we'll probably get 2.2.0 release before this is ready, don't rush this to try and hit that timeline. We can get this into 2.3.0 and I'm happy to do that as soon as this is ready to go 👍 .
thibonacci: > Thanks for getting this so far @thibonacci!
> 
> Having a read through I think this is a good rule for us to add - but we need to cover a couple of logical situations to make sure it's complete and ready to add.
> 
> 1. I think the point raised by @fmms is important, and we should at least one test case each for the following joins:
>    
>    * `LEFT` (which we have)
>    * `LEFT OUTER` - which should behave the same as `LEFT`
>    * `INNER` - which should also behave the same as `LEFT`
>    * `RIGHT` & `RIGHT OUTER` - which _may_ behave differently. I've done a bit of research, and based on the [snowflake docs](https://docs.snowflake.com/en/sql-reference/constructs/join) and the [bigquery docs](https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#right_join) it appears there is disagreement on which should come first - but that it's the _same_ for both `RIGHT` and `LEFT`. That means I think that from a configuration point of view, while we might still offer `left` and `right` - the default should be something more like `earlier` and `later` (I'm not attached to those terms, maybe `source` and `join`?), and the default should be `earlier`/`source` in line with [New Rule: In ON clause of JOIN statement specify source table first and join table second #2395](https://github.com/sqlfluff/sqlfluff/issues/2395).
>    * We should also test with `CROSS` and `FULL OUTER`.
> 2. We should also address the question of joins which do their filtering in the `WHERE` clause. My sense is that you haven't tried to tackle this as this stage, which is fine, but we should put a note in the docstring to that effect to say it's not covered. e.g. `SELECT * FROM a, b WHERE a.foo = b.foo`.
> 
> * On this front, it could be interesting to also extend this logic to the `WHERE` clause too though, where we say any comparisons in there should _also_ follow the same rules. Up to you whether you apply it to `WHERE` for now, or just call out that it's not currently covered.

Thanks for taking a look @alanmcruickshank!

1. Yep will add those tests! Still not quite fully sure about the terminology, Redshift actually does use "left" and "right" in [here](https://docs.aws.amazon.com/redshift/latest/dg/r_FROM_clause30.html#r_FROM_clause30-parameters) the way I've been using them (i.e. irrespective of the type of join) - in quotes though so they're essentially admitting that it's not the greatest terminology. I'm happy to go with `source` and `join`, just wish there were some authority telling us what we should use, but I suppose SQLFluff is becoming such an authority!
2. Yeah when I first started this I had no idea how SQLFluff worked so wanted to keep things as simple as possible but I now don't think it should be too hard to extend the logic I've used to the `WHERE` clause so I can do that as part of this PR.

I have very limited time to work on this unfortunately so am definitely not rushing anything 👍 
thibonacci: @alanmcruickshank In the end I decided not to try to cover `WHERE` clauses as I've just learned about the `(+)` notation (see [Snowflake docs](https://docs.snowflake.com/en/sql-reference/constructs/where#joins-in-the-where-clause)) which I don't believe is supported by SQLFluff yet. Also went with `earlier`/`later` as it felt a little bit easier to explain in the docstring. 
If you could help trigger the CI which I think should be all green now. 🤞 
Thanks! 
thibonacci: > Looking pretty good! One last suggestion on customising the `description` of the rule now that you've got all the configuration sorted (and you'll likely need to update the test suite too once you've done that).
> 
> After that, this should be good to merge.

Yep that's a nice little improvement. Done!

Patch:
diff --git a/src/sqlfluff/cli/commands.py b/src/sqlfluff/cli/commands.py
index fb0b2655a..2ac85eb52 100644
--- a/src/sqlfluff/cli/commands.py
+++ b/src/sqlfluff/cli/commands.py
@@ -13,10 +13,6 @@ import yaml
 
 import click
 
-# For the profiler
-import pstats
-from io import StringIO
-
 # To enable colour cross platform
 import colorama
 from tqdm import tqdm
@@ -1186,9 +1182,6 @@ def quoted_presenter(dumper, data):
         "stdout logging."
     ),
 )
-@click.option(
-    "--profiler", is_flag=True, help="Set this flag to engage the python profiler."
-)
 @click.option(
     "--parse-statistics",
     is_flag=True,
@@ -1211,7 +1204,6 @@ def parse(
     include_meta: bool,
     format: str,
     write_output: Optional[str],
-    profiler: bool,
     bench: bool,
     nofail: bool,
     logger: Optional[logging.Logger] = None,
@@ -1250,18 +1242,6 @@ def parse(
         stderr_output=non_human_output,
     )
 
-    # TODO: do this better
-
-    if profiler:
-        # Set up the profiler if required
-        try:
-            import cProfile
-        except ImportError:  # pragma: no cover
-            click.echo("The cProfiler is not available on your platform.")
-            sys.exit(EXIT_ERROR)
-        pr = cProfile.Profile()
-        pr.enable()
-
     t0 = time.monotonic()
 
     # handle stdin if specified via lone '-'
@@ -1319,14 +1299,6 @@ def parse(
 
         # Dump the output to stdout or to file as appropriate.
         dump_file_payload(write_output, file_output)
-    if profiler:
-        pr.disable()
-        profiler_buffer = StringIO()
-        ps = pstats.Stats(pr, stream=profiler_buffer).sort_stats("cumulative")
-        ps.print_stats()
-        click.echo("==== profiler stats ====")
-        # Only print the first 50 lines of it
-        click.echo("\n".join(profiler_buffer.getvalue().split("\n")[:50]))
 
     if violations_count > 0 and not nofail:
         sys.exit(EXIT_FAIL)  # pragma: no cover
diff --git a/src/sqlfluff/core/default_config.cfg b/src/sqlfluff/core/default_config.cfg
index cfd85cf4d..b3f385c9b 100644
--- a/src/sqlfluff/core/default_config.cfg
+++ b/src/sqlfluff/core/default_config.cfg
@@ -384,3 +384,6 @@ wildcard_policy = single
 [sqlfluff:rules:structure.subquery]
 # By default, allow subqueries in from clauses, but not join clauses
 forbid_subquery_in = join
+
+[sqlfluff:rules:structure.first_table]
+preferred_first_table_in_join_clause = earlier
diff --git a/src/sqlfluff/core/rules/config_info.py b/src/sqlfluff/core/rules/config_info.py
index 28e067bb3..62e723550 100644
--- a/src/sqlfluff/core/rules/config_info.py
+++ b/src/sqlfluff/core/rules/config_info.py
@@ -213,6 +213,13 @@ STANDARD_CONFIG_INFO_DICT: Dict[str, Dict[str, Any]] = {
         "validation": ["consistent", "shorthand", "convert", "cast"],
         "definition": ("The expectation for using sql type casting"),
     },
+    "preferred_first_table_in_join_clause": {
+        "validation": ["earlier", "later"],
+        "definition": (
+            "Which table to list first when joining two tables. "
+            "Defaults to ``earlier``."
+        ),
+    },
 }
 
 
diff --git a/src/sqlfluff/rules/structure/ST09.py b/src/sqlfluff/rules/structure/ST09.py
new file mode 100644
index 000000000..b0732d77e
--- /dev/null
+++ b/src/sqlfluff/rules/structure/ST09.py
@@ -0,0 +1,291 @@
+"""Implementation of Rule ST09."""
+from typing import Optional, Tuple, List, cast
+from sqlfluff.core.parser.segments.raw import BaseSegment, SymbolSegment
+from sqlfluff.core.rules import BaseRule, LintFix, LintResult, RuleContext
+from sqlfluff.core.rules.crawlers import SegmentSeekerCrawler
+from sqlfluff.utils.functional import Segments, FunctionalContext
+from sqlfluff.dialects.dialect_ansi import (
+    FromExpressionElementSegment,
+    JoinClauseSegment,
+)
+
+
+class Rule_ST09(BaseRule):
+    """Joins should list the table referenced earlier/later first.
+
+    This rule will break conditions from join clauses down into subconditions
+    using the "and" and "or" binary operators.
+
+    Subconditions that are made up of a qualified column reference,
+    a comparison operator and another qualified column reference
+    are then evaluated to check whether they list the table that was referenced
+    earlier - or later, depending on the ``preferred_first_table_in_join_clause``
+    configuration.
+
+    Subconditions that do not follow that pattern are ignored by this rule.
+
+    .. note::
+       Joins in ``WHERE`` clauses are currently not supported by this rule.
+
+    **Anti-pattern**
+
+    In this example, the tables that were referenced later are listed first
+    and the ``preferred_first_table_in_join_clause`` configuration
+    is set to ``earlier``.
+
+    .. code-block:: sql
+
+        select
+            foo.a,
+            foo.b,
+            bar.c
+        from foo
+        left join bar
+            -- This subcondition does not list
+            -- the table referenced earlier first:
+            on bar.a = foo.a
+            -- Neither does this subcondition:
+            and bar.b = foo.b
+
+    **Best practice**
+
+    List the tables that were referenced earlier first.
+
+    .. code-block:: sql
+
+        select
+            foo.a,
+            foo.b,
+            bar.c
+        from foo
+        left join bar
+            on foo.a = bar.a
+            and foo.b = bar.b
+    """
+
+    name = "structure.first_table"
+    aliases = ()
+    groups: Tuple[str, ...] = ("all", "structure")
+    config_keywords = ["preferred_first_table_in_join_clause"]
+    crawl_behaviour = SegmentSeekerCrawler({"from_expression"})
+    is_fix_compatible = True
+
+    def _eval(self, context: RuleContext) -> Optional[LintResult]:
+        """Find rule violations and provide fixes.
+
+        0. Grab all table aliases into a table_aliases list.
+        1. Grab all conditions from the different join_on_condition segments.
+        2. Break conditions down into subconditions using the "and" and "or"
+        binary operators.
+        3. Keep subconditions that are made up of a qualified column_reference,
+        a comparison_operator and another qualified column_reference segments.
+        4. Check whether the table associated with the first column_reference segment
+        has a greater index in table_aliases than the second column_reference segment.
+        If so, populate the fixes list (lower index instead of greater index
+        if preferred_first_table_in_join_clause == "later").
+        5.a. If fixes is empty the rule passes.
+        5.b. If fixes isn't empty we return a LintResult object with fixable violations.
+        """
+        self.preferred_first_table_in_join_clause: str
+
+        assert context.segment.is_type("from_expression")
+
+        # STEP 0.
+        table_aliases: List[str] = []
+
+        children = FunctionalContext(context).segment.children()
+
+        # we use recursive_crawl to deal with brackets
+        join_clauses = children.recursive_crawl("join_clause")
+
+        join_on_conditions = join_clauses.children().recursive_crawl(
+            "join_on_condition"
+        )
+
+        # we only care about join_on_condition segments
+        if len(join_on_conditions) == 0:
+            return None
+
+        # the first alias comes from the from clause
+        from_expression_alias: str = (
+            cast(
+                FromExpressionElementSegment,
+                children.recursive_crawl("from_expression_element")[0],
+            )
+            .get_eventual_alias()
+            .ref_str
+        )
+
+        table_aliases.append(from_expression_alias)
+
+        # the rest of the aliases come from the different join clauses
+        join_clause_aliases: List[str] = [
+            cast(JoinClauseSegment, join_clause).get_eventual_aliases()[0][1].ref_str
+            for join_clause in [clause for clause in join_clauses]
+        ]
+
+        table_aliases = table_aliases + join_clause_aliases
+
+        table_aliases = [alias.upper() for alias in table_aliases]
+
+        # STEP 1.
+        conditions: List[List[BaseSegment]] = []
+
+        join_on_condition__expressions = join_on_conditions.children().recursive_crawl(
+            "expression"
+        )
+
+        for expression in join_on_condition__expressions:
+            expression_group = []
+            for element in Segments(expression).children():
+                if element.type not in ("whitespace", "newline"):
+                    expression_group.append(element)
+            conditions.append(expression_group)
+
+        # STEP 2.
+        subconditions: List[List[List[BaseSegment]]] = []
+
+        for expression_group in conditions:
+            subconditions.append(
+                self._split_list_by_segment_type(
+                    segment_list=expression_group,
+                    delimiter_type="binary_operator",
+                    delimiters=["and", "or"],
+                )
+            )
+
+        subconditions_flattened: List[List[BaseSegment]] = [
+            item for sublist in subconditions for item in sublist
+        ]
+
+        # STEP 3.
+        column_operator_column_subconditions: List[List[BaseSegment]] = [
+            subcondition
+            for subcondition in subconditions_flattened
+            if self._is_qualified_column_operator_qualified_column_sequence(
+                subcondition
+            )
+        ]
+
+        # STEP 4.
+        fixes: List[LintFix] = []
+
+        for subcondition in column_operator_column_subconditions:
+            comparison_operator = subcondition[1]
+            first_column_reference = subcondition[0]
+            second_column_reference = subcondition[2]
+            raw_comparison_operators = comparison_operator.get_children(
+                "raw_comparison_operator"
+            )
+
+            first_table = first_column_reference.get_child("naked_identifier").raw_upper
+            second_table = second_column_reference.get_child(
+                "naked_identifier"
+            ).raw_upper
+
+            # if we swap the two column references around the comparison operator
+            # we might have to replace the comparison operator with a different one
+            raw_comparison_operator_opposites = {"<": ">", ">": "<"}
+
+            # there seem to be edge cases where either the first table or the second
+            # table is not in table_aliases, in which case we cannot provide any fix
+            if first_table not in table_aliases or second_table not in table_aliases:
+                continue
+
+            if (
+                table_aliases.index(first_table) > table_aliases.index(second_table)
+                and self.preferred_first_table_in_join_clause == "earlier"
+            ) or (
+                table_aliases.index(first_table) < table_aliases.index(second_table)
+                and self.preferred_first_table_in_join_clause == "later"
+            ):
+                fixes = (
+                    fixes
+                    + [
+                        LintFix.replace(
+                            first_column_reference,
+                            [second_column_reference],
+                        )
+                    ]
+                    + [
+                        LintFix.replace(
+                            second_column_reference,
+                            [first_column_reference],
+                        )
+                    ]
+                    + (
+                        [
+                            LintFix.replace(
+                                raw_comparison_operators[0],
+                                [
+                                    SymbolSegment(
+                                        raw=raw_comparison_operator_opposites[
+                                            raw_comparison_operators[0].raw
+                                        ],
+                                        type="raw_comparison_operator",
+                                    )
+                                ],
+                            )
+                        ]
+                        if raw_comparison_operators[0].raw
+                        in raw_comparison_operator_opposites
+                        and [r.raw for r in raw_comparison_operators] != ["<", ">"]
+                        else []
+                    )
+                )
+
+        # STEP 5.a.
+        if fixes == []:
+            return None
+
+        # STEP 5.b.
+        else:
+            return LintResult(
+                anchor=context.segment,
+                fixes=fixes,
+                description=(
+                    "Joins should list the table referenced "
+                    f"{self.preferred_first_table_in_join_clause} first."
+                ),
+            )
+
+    @staticmethod
+    def _split_list_by_segment_type(
+        segment_list: List[BaseSegment], delimiter_type: str, delimiters: List[str]
+    ) -> List:
+        # Break down a list into multiple sub-lists using a set of delimiters
+        delimiters = [delimiter.upper() for delimiter in delimiters]
+        new_list = []
+        sub_list = []
+        for i in range(len(segment_list)):
+            if i == len(segment_list) - 1:
+                sub_list.append(segment_list[i])
+                new_list.append(sub_list)
+            elif (
+                segment_list[i].type == delimiter_type
+                and segment_list[i].raw_upper in delimiters
+            ):
+                new_list.append(sub_list)
+                sub_list = []
+            else:
+                sub_list.append(segment_list[i])
+
+        return new_list
+
+    @staticmethod
+    def _is_qualified_column_operator_qualified_column_sequence(
+        segment_list: List[BaseSegment],
+    ) -> bool:
+        # Check if list is made up of a qualified column_reference segment,
+        # a comparison_operator segment and another qualified column_reference segment
+        if len(segment_list) != 3:
+            return False
+        if (
+            segment_list[0].type == "column_reference"
+            and "dot" in segment_list[0].direct_descendant_type_set
+            and segment_list[1].type == "comparison_operator"
+            and segment_list[2].type == "column_reference"
+            and "dot" in segment_list[2].direct_descendant_type_set
+        ):
+            return True
+        return False
diff --git a/src/sqlfluff/rules/structure/__init__.py b/src/sqlfluff/rules/structure/__init__.py
index fdb48e021..5ece4f71d 100644
--- a/src/sqlfluff/rules/structure/__init__.py
+++ b/src/sqlfluff/rules/structure/__init__.py
@@ -20,6 +20,7 @@ def get_rules() -> List[Type[BaseRule]]:
     from sqlfluff.rules.structure.ST06 import Rule_ST06
     from sqlfluff.rules.structure.ST07 import Rule_ST07
     from sqlfluff.rules.structure.ST08 import Rule_ST08
+    from sqlfluff.rules.structure.ST09 import Rule_ST09
 
     return [
         Rule_ST01,
@@ -30,4 +31,5 @@ def get_rules() -> List[Type[BaseRule]]:
         Rule_ST06,
         Rule_ST07,
         Rule_ST08,
+        Rule_ST09,
     ]


Test patch:
diff --git a/test/cli/commands_test.py b/test/cli/commands_test.py
index 292a5ac51..943bbec54 100644
--- a/test/cli/commands_test.py
+++ b/test/cli/commands_test.py
@@ -346,8 +346,7 @@ def test__cli__command_render_stdin():
         (parse, ["-n", "test/fixtures/cli/passing_b.sql", "--format", "yaml"]),
         # Check parsing with no output (used mostly for testing)
         (parse, ["-n", "test/fixtures/cli/passing_b.sql", "--format", "none"]),
-        # Check the profiler and benching commands
-        (parse, ["-n", "test/fixtures/cli/passing_b.sql", "--profiler"]),
+        # Check the benching commands
         (parse, ["-n", "test/fixtures/cli/passing_b.sql", "--bench"]),
         (
             lint,
@@ -1432,7 +1431,7 @@ def test__cli__command_lint_serialize_multiple_files(serialize, write_file, tmp_
     print("Result length:", payload_length)
 
     if serialize == "human":
-        assert payload_length == 23 if write_file else 32
+        assert payload_length == 25 if write_file else 34
     elif serialize == "none":
         assert payload_length == 1  # There will be a single newline.
     elif serialize == "json":
@@ -1450,7 +1449,7 @@ def test__cli__command_lint_serialize_multiple_files(serialize, write_file, tmp_
         # SQLFluff produces trailing newline
         if result[-1] == "":
             del result[-1]
-        assert len(result) == 11
+        assert len(result) == 12
     else:
         raise Exception
 
@@ -1960,8 +1959,10 @@ class TestProgressBars:
 multiple_expected_output = """==== finding fixable violations ====
 == [test/fixtures/linter/multiple_sql_errors.sql] FAIL
 L:  12 | P:   1 | LT02 | Expected indent of 4 spaces. [layout.indent]
+L:  40 | P:  10 | ST09 | Joins should list the table referenced earlier first.
+                       | [structure.first_table]
 ==== fixing violations ====
-1 fixable linting violations found
+2 fixable linting violations found
 Are you sure you wish to attempt to fix these? [Y/n] ...
 Invalid input, please enter 'Y' or 'N'
 Aborting...
@@ -2008,7 +2009,7 @@ def test__cli__fix_multiple_errors_quiet_force():
     normalised_output = result.output.replace("\\", "/")
     assert normalised_output.startswith(
         """== [test/fixtures/linter/multiple_sql_errors.sql] FIXED
-1 fixable linting violations found"""
+2 fixable linting violations found"""
     )
 
 
@@ -2031,7 +2032,7 @@ def test__cli__fix_multiple_errors_quiet_no_force():
     )
     normalised_output = result.output.replace("\\", "/")
     assert normalised_output.startswith(
-        """1 fixable linting violations found
+        """2 fixable linting violations found
 Are you sure you wish to attempt to fix these? [Y/n] ...
 == [test/fixtures/linter/multiple_sql_errors.sql] FIXED
 All Finished"""
diff --git a/test/fixtures/rules/std_rule_cases/ST09.yml b/test/fixtures/rules/std_rule_cases/ST09.yml
new file mode 100644
index 000000000..58486670d
--- /dev/null
+++ b/test/fixtures/rules/std_rule_cases/ST09.yml
@@ -0,0 +1,391 @@
+rule: ST09
+
+test_pass_no_join_clauses:
+  pass_str: select * from foo
+
+test_pass_no_join_on_conditions:
+  pass_str:
+    select
+        foo.a,
+        bar.b
+    from foo
+    left join bar
+        using (a)
+
+test_pass_ignored_subconditions:
+  pass_str:
+    select
+        foo.a,
+        bar.b
+    from foo
+    left join bar
+        on bar.a between foo.a and foo.b
+
+test_pass_unqualified_column_reference:
+  pass_str:
+    select
+        foo.a,
+        bar.b
+    from foo
+    left join bar
+        on bar.b = a
+
+test_pass_earlier_table_first:
+  pass_str: |
+    select
+        foo.a,
+        bar.b
+    from foo
+    left join bar
+        on foo.a = bar.a
+
+test_pass_later_table_first:
+  pass_str: |
+    select
+        foo.a,
+        bar.b
+    from foo
+    left join bar
+        on bar.a = foo.a
+
+  configs:
+    rules:
+      structure.first_table:
+        preferred_first_table_in_join_clause: later
+
+test_fail_earlier_table_first:
+  fail_str: |
+    select
+        foo.a,
+        bar.b
+    from foo
+    left join bar
+        on foo.a = bar.a
+
+  fix_str: |
+    select
+        foo.a,
+        bar.b
+    from foo
+    left join bar
+        on bar.a = foo.a
+
+  configs:
+    rules:
+      structure.first_table:
+        preferred_first_table_in_join_clause: later
+
+test_fail_later_table_first:
+  fail_str: |
+    select
+        foo.a,
+        bar.b
+    from foo
+    left join bar
+        on bar.a = foo.a
+
+  fix_str: |
+    select
+        foo.a,
+        bar.b
+    from foo
+    left join bar
+        on foo.a = bar.a
+
+test_fail_later_table_first_left_outer:
+  fail_str: |
+    select
+        foo.a,
+        bar.b
+    from foo
+    left outer join bar
+        on bar.a = foo.a
+
+  fix_str: |
+    select
+        foo.a,
+        bar.b
+    from foo
+    left outer join bar
+        on foo.a = bar.a
+
+test_fail_later_table_first_inner:
+  fail_str: |
+    select
+        foo.a,
+        bar.b
+    from foo
+    inner join bar
+        on bar.a = foo.a
+
+  fix_str: |
+    select
+        foo.a,
+        bar.b
+    from foo
+    inner join bar
+        on foo.a = bar.a
+
+test_fail_later_table_first_right:
+  fail_str: |
+    select
+        foo.a,
+        bar.b
+    from foo
+    right join bar
+        on bar.a = foo.a
+
+  fix_str: |
+    select
+        foo.a,
+        bar.b
+    from foo
+    right join bar
+        on foo.a = bar.a
+
+test_fail_later_table_first_right_outer:
+  fail_str: |
+    select
+        foo.a,
+        bar.b
+    from foo
+    right outer join bar
+        on bar.a = foo.a
+
+  fix_str: |
+    select
+        foo.a,
+        bar.b
+    from foo
+    right outer join bar
+        on foo.a = bar.a
+
+test_fail_later_table_first_full_outer:
+  fail_str: |
+    select
+        foo.a,
+        bar.b
+    from foo
+    full outer join bar
+        on bar.a = foo.a
+
+  fix_str: |
+    select
+        foo.a,
+        bar.b
+    from foo
+    full outer join bar
+        on foo.a = bar.a
+
+test_pass_later_table_first_cross:
+  pass_str: |
+    select
+        foo.a,
+        bar.b
+    from foo
+    cross join bar
+    where bar.a = foo.a
+
+test_fail_later_table_first_multiple_subconditions:
+  fail_str: |
+    select
+        foo.a,
+        foo.b,
+        bar.c
+    from foo
+    left join bar
+        on bar.a = foo.a
+        and bar.b = foo.b
+
+  fix_str: |
+    select
+        foo.a,
+        foo.b,
+        bar.c
+    from foo
+    left join bar
+        on foo.a = bar.a
+        and foo.b = bar.b
+
+test_fail_later_table_first_multiple_comparison_operators:
+  fail_str: |
+    select
+        foo.a,
+        bar.b,
+        baz.c
+    from foo
+    left join bar
+        on bar.a != foo.a
+        and bar.b > foo.b
+        and bar.c <= foo.c
+    left join baz
+        on baz.a <> foo.a
+        and baz.b >= foo.b
+        and baz.c < foo.c
+
+  fix_str: |
+    select
+        foo.a,
+        bar.b,
+        baz.c
+    from foo
+    left join bar
+        on foo.a != bar.a
+        and foo.b < bar.b
+        and foo.c >= bar.c
+    left join baz
+        on foo.a <> baz.a
+        and foo.b <= baz.b
+        and foo.c > baz.c
+
+test_fail_later_table_first_subquery:
+  fail_str: |
+    select
+        foo.a,
+        bar.b
+    from (
+        select
+            baz.a,
+            qux.b
+        from baz
+        left join qux
+            on qux.a = baz.a
+    ) foo
+    left join bar
+        on bar.a = foo.a
+
+  fix_str: |
+    select
+        foo.a,
+        bar.b
+    from (
+        select
+            baz.a,
+            qux.b
+        from baz
+        left join qux
+            on baz.a = qux.a
+    ) foo
+    left join bar
+        on foo.a = bar.a
+
+test_fail_later_table_first_cte:
+  fail_str: |
+    with foo as (
+        select
+            baz.a,
+            qux.b
+        from baz
+        left join qux
+            on qux.a = baz.a
+    )
+    select
+        foo.a,
+        bar.b
+    from foo
+    left join bar
+        on bar.a = foo.a
+
+  fix_str: |
+    with foo as (
+        select
+            baz.a,
+            qux.b
+        from baz
+        left join qux
+            on baz.a = qux.a
+    )
+    select
+        foo.a,
+        bar.b
+    from foo
+    left join bar
+        on foo.a = bar.a
+
+test_fail_later_table_no_join_clause_in_cte:
+  fail_str: |
+    with foo as (
+        select * from bar
+    )
+    select
+        foo.a,
+        baz.b
+    from foo
+    left join baz
+        on baz.a = foo.a
+
+  fix_str: |
+    with foo as (
+        select * from bar
+    )
+    select
+        foo.a,
+        baz.b
+    from foo
+    left join baz
+        on foo.a = baz.a
+
+test_fail_later_table_no_join_clause_in_main_query:
+  fail_str: |
+    with foo as (
+        select
+            bar.b
+        from bar
+        left join baz
+            on baz.a = bar.a
+    )
+    select
+        b
+    from foo
+
+  fix_str: |
+    with foo as (
+        select
+            bar.b
+        from bar
+        left join baz
+            on bar.a = baz.a
+    )
+    select
+        b
+    from foo
+
+test_fail_later_table_first_brackets_after_on:
+  fail_str: |
+    select
+        foo.a,
+        bar.b
+    from foo
+    left join bar
+        on (bar.a = foo.a)
+
+  fix_str: |
+    select
+        foo.a,
+        bar.b
+    from foo
+    left join bar
+        on (foo.a = bar.a)
+
+test_fail_later_table_first_brackets_after_from:
+  fail_str: |
+    select
+        foo.a,
+        bar.b
+    from
+        (
+            foo
+            left join bar
+                on bar.a = foo.a
+        )
+
+  fix_str: |
+    select
+        foo.a,
+        bar.b
+    from
+        (
+            foo
+            left join bar
+                on foo.a = bar.a
+        )