Fix/Test Joins

2 files changed, 34 insertions, 2 deletions

diff --git a/sqlparse/filters.py b/sqlparse/filters.py
index 2f0e3b9..2ce17e9 100644
--- a/sqlparse/filters.py
+++ b/sqlparse/filters.py
@@ -336,9 +336,10 @@ class ReindentFilter(object):
 
 
 class AlignedIndentFilter:
+    join_words = r'((LEFT\s+|RIGHT\s+|FULL\s+)?(INNER\s+|OUTER\s+|STRAIGHT\s+)?|(CROSS\s+|NATURAL\s+)?)?JOIN\b'
     split_words = (
         'FROM',
-        'JOIN', 'ON',
+        join_words, 'ON',
         'WHERE', 'AND', 'OR',
         'GROUP', 'HAVING', 'LIMIT',
         'ORDER', 'UNION', 'VALUES',
@@ -439,7 +440,12 @@ class AlignedIndentFilter:
         idx = 0
         token = _next_token(idx)
         while token:
-            tlist.insert_before(token, self.whitespace(self._max_kwd_len - len(str(token)) + base_indent, newline_before=True))
+            if token.match(T.Keyword, self.join_words, regex=True):
+                # joins are a special case. we only consider the first word of the join as the aligner
+                token_indent = len(token.value.split()[0])
+            else:
+                token_indent = len(str(token))
+            tlist.insert_before(token, self.whitespace(self._max_kwd_len - token_indent + base_indent, newline_before=True))
             next_idx = tlist.token_index(token) + 1
             token = _next_token(next_idx)
 
diff --git a/tests/test_format.py b/tests/test_format.py
index 9548dc3..dccc3ec 100644
--- a/tests/test_format.py
+++ b/tests/test_format.py
@@ -139,6 +139,32 @@ class TestFormatReindentAligned(TestCaseBase):
                 ' limit 10',
             ]))
 
+    def test_joins(self):
+        sql = """
+            select * from a
+            join b on a.one = b.one
+            left join c on c.two = a.two and c.three = a.three
+            full outer join d on d.three = a.three
+            cross join e on e.four = a.four
+            join f using (one, two, three)
+            """
+        self.ndiffAssertEqual(
+            self.formatter(sql),
+            '\n'.join([
+                'select *',
+                '  from a',
+                '  join b',
+                '    on a.one = b.one',
+                '  left join c',
+                '    on c.two = a.two',
+                '   and c.three = a.three',
+                '  full outer join d',
+                '    on d.three = a.three',
+                ' cross join e',
+                '    on e.four = a.four',
+                '  join f using (one, two, three)',
+            ]))
+
     def test_case_statement(self):
         sql = """
             select a,