What would be a simple way to count the number of Joins (both explicit and implict Joins).

[CBQu]

Hi,

I would like to extract the number of joins in a list of query strings.

Explicit joins can be found by findall(exp.Joins).
Implicit Joins can be covered by findall(exp.EQ) from the anwser of another discussion.

But I think there could be overlaps if explicit joins have ON conditions. I am wonder if there is any smiple way to count all the joins. Or there could be any reference code from the sqlglot's Optimizor Module I did not find.

My current idea is like:
set(explicit_joins)` + set(joins_with_eq_condition) - set (explicit_joins_with_on_condition)

I am wondering if this is correct or if there exists any simpler idea.
Thanks.

[barakalon]

By "implicit joins", I'm assuming you mean this:

SELECT
  *
FROM x, y

If you're just trying to count the number of joined tables:

expression = parse_one("SELECT ...")
implicit_joins = set()
for from_ in expression.find_all(exp.From):
    implicit_joins.update(from_.expressions[1:])

[CBQu]

Sorry for my ambiguous description.

My goal is to count the number of joins in each sql, not the numbers of joined tables.
I am considering all kind of joins - inner, outer, left, right.

By implicit joins, I mean sqls, which do not contain the "JOIN" keyword, like

SELECT *
FROM x, y
WHERE x.a = y.a

By explicit joins, I mean sqls with JOIN keywords.

SELECT *
FROM x JOIN y on x.a = y.a

or

SELECT *
FROM x JOIN y

If I only count the number through exp.EQ expressions, I would miss joins without joining column and the ones with JOIN keywords.
If I only count the number of exp.JOIN expressions, I would miss all the implicit joins.

I am not sure how to correctly extract all the joins.

[tobymao]

why not just findall(exp.Join) + findall(exp.From).sum( from: len(from.expressions) - 1)

[CBQu]

This seems right.

To be more specific, is it something like this?

def count_joins(parsed_sql):
  num_of_joins = 0

  for join in parsed_sql.findall(exp.Join):
      num_of_joins += 1
  
  for from in parsed_sql.findall(exp.From):
      num_of_joins += len(from.expressions) - 1

return num_of_joins

[barakalon]

yep

[CBQu]

Got it. Thanks. :)