Skip to content

Add a new api to create join in LogicalPlanBuilder #4306

Closed

Description

@ygf11

Is your feature request related to a problem or challenge? Please describe what you are trying to do.

When I try to add test cases, I find it is not easy to create join logical plan whose keys are expressions, because we have to create many additional projections.
I would like to add a new api to create such join in LogicalPlanBuilder.

Describe the solution you'd like
Add a new api to create join in LogicalPlanBuilder. The api will be like:

 pub fn join_with_expr_keys(
        &self,
        right: &LogicalPlan,
        join_type: JoinType,
        join_keys: (Vec<impl Into<Expr>>, Vec<impl Into<Expr>>),
        filter: Option<Expr>,
    ) -> Result<Self> 

Describe alternatives you've considered

Additional context

Activity

  1. Jefffrey commented on Sep 24, 2025

    @Jefffrey
    Contributor

    I think the join_on() method fits the bill:

    /// Apply a join using the specified expressions.
    ///
    /// Note that DataFusion automatically optimizes joins, including
    /// identifying and optimizing equality predicates.
    ///
    /// # Example
    ///
    /// ```
    /// # use datafusion_expr::{Expr, col, LogicalPlanBuilder,
    /// # logical_plan::builder::LogicalTableSource, logical_plan::JoinType,};
    /// # use std::sync::Arc;
    /// # use arrow::datatypes::{Schema, DataType, Field};
    /// # use datafusion_common::Result;
    /// # fn main() -> Result<()> {
    /// let example_schema = Arc::new(Schema::new(vec![
    /// Field::new("a", DataType::Int32, false),
    /// Field::new("b", DataType::Int32, false),
    /// Field::new("c", DataType::Int32, false),
    /// ]));
    /// let table_source = Arc::new(LogicalTableSource::new(example_schema));
    /// let left_table = table_source.clone();
    /// let right_table = table_source.clone();
    ///
    /// let right_plan = LogicalPlanBuilder::scan("right", right_table, None)?.build()?;
    ///
    /// // Form the expression `(left.a != right.a)` AND `(left.b != right.b)`
    /// let exprs = vec![
    /// col("left.a").eq(col("right.a")),
    /// col("left.b").not_eq(col("right.b"))
    /// ];
    ///
    /// // Perform the equivalent of `left INNER JOIN right ON (a != a2 AND b != b2)`
    /// // finding all pairs of rows from `left` and `right` where
    /// // where `a = a2` and `b != b2`.
    /// let plan = LogicalPlanBuilder::scan("left", left_table, None)?
    /// .join_on(right_plan, JoinType::Inner, exprs)?
    /// .build()?;
    /// # Ok(())
    /// # }
    /// ```
    pub fn join_on(
    self,
    right: LogicalPlan,
    join_type: JoinType,
    on_exprs: impl IntoIterator<Item = Expr>,
    ) -> Result<Self> {
    let filter = on_exprs.into_iter().reduce(Expr::and);
    self.join_detailed(
    right,
    join_type,
    (Vec::<Column>::new(), Vec::<Column>::new()),
    filter,
    NullEquality::NullEqualsNothing,
    )
    }

    Reference PRs:

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    enhancementNew feature or request

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions