DOC: SQL-style join conditions

[jxu]

Pandas version checks

• I have checked that the issue still exists on the latest versions of the docs on main here

Location of the documentation

https://pandas.pydata.org/docs/getting_started/comparison/comparison_with_sql.html

Documentation problem

Not described how to do more complicated SQL-like joins on conditions, such as

SELECT *
FROM A
LEFT JOIN B
  ON A.key = B.key AND A.a >= B.b 

Suggested fix for documentation

Something from https://stackoverflow.com/questions/23508351/how-to-do-workaround-a-conditional-join-in-python-pandas

This is a common operation and a canonical answer is useful

[samukweku]

@jxu you could contribute a PR to add this to the docs

[Delengowski]

I'm not familiar with SQL mechanics but does it actually only merge on rows that A.a >= B.b evaluates true or does it match on A.key = B.key first and then filter it further with A.a >= B.b (meaning there's some intermediate result)?

If it's the latter, then I suppose an example showing the merge followed by a query could be done but I believe that's inferable from the current examples.

To my knowledge there is no way to do the former with pandas

[jxu]

I'm not actually sure how to do this. My understanding of LEFT JOIN in SQL is logically

1. Cartesian Product
2. Filter rows based on condition
3. Give rows not matched from left table nulls

So I think the pandas merge is join but only on keys... you could do what I wrote step by step, but I think the product step would be expensive in implementation

[samukweku]

Merging on the keys and then filtering is usually what SQL does as well (if there is an equality condition).

[jxu]

That's the logical result, but the SQL engine can run it in a way that's more efficient. But if I write it as two steps in pandas, the whole cross product is calculated before any filtering.

[jxu]

I'm not familiar with SQL mechanics but does it actually only merge on rows that A.a >= B.b evaluates true or does it match on A.key = B.key first and then filter it further with A.a >= B.b (meaning there's some intermediate result)?

If it's the latter, then I suppose an example showing the merge followed by a query could be done but I believe that's inferable from the current examples.

To my knowledge there is no way to do the former with pandas

The SQL join condition is applied once, so there's no logical distinction between the key condition and any other condition.

[samukweku]

@jxu I'm not sure what you mean by cross product. A merge on the equal keys is not a cross product

[jxu]

Cartesian product or cross join. Cross product is something for vector spaces

[samukweku]

@jxu if you are running an equi join with a non equi join in pandas, then there is no Cartesian join. The join is executed first on the equi join, followed by the non equi join(which is a filter on the results of the equi join). SQL does the same but in a transparent way - an EXPLAIN plan will likely show an equi join followed by a non equi join filter

[jxu]

ok, I did not know that

[thwait]

take