Joins in USQL scripts

Question

Can we use "Between" condition in usql scripts when joining multiple tables. Below is the code snippet :

SELECT sf.ScoringFeedCode AS ScoringFeedCode,         
     mc.CalendarCode AS CycleCode,
     mc.CalendarGUID AS CycleCalendarGUID,
     (DateTime.ParseExact(mc.StartDate, @datetimeformat, null)).ToString("yyyy-MM-dd") AS CycleStartDate,
     (DateTime.ParseExact(mc.EndDate, @datetimeformat, null)).ToString("yyyy-MM-dd") AS CycleEndDate,
     (DateTime.ParseExact(mc.CalendarStartDate, @datetimeformat, null)).ToString("yyyy-MM-dd") AS MonthCalStartDate,
     (DateTime.ParseExact(mc.CalendarEndDate, @datetimeformat, null)).ToString("yyyy-MM-dd") AS MonthCalEndDate,
     qc.CalendarCode AS QtrCode,
     qc.CalendarGUID AS QtrCalendarGUID,
     (DateTime.ParseExact(qc.StartDate, @datetimeformat, null)).ToString("yyyy-MM-dd") AS QtrStartDate,
     (DateTime.ParseExact(qc.EndDate, @datetimeformat, null)).ToString("yyyy-MM-dd") AS QtrEndDate,
     (DateTime.ParseExact(qc.CalendarStartDate, @datetimeformat, null)).ToString("yyyy-MM-dd") AS QtrCalStartDate,
     (DateTime.ParseExact(qc.CalendarEndDate, @datetimeformat, null)).ToString("yyyy-MM-dd") AS QtrCalEndDate,
     (DateTime.ParseExact(wc.EndDate, @datetimeformat, null)).ToString("yyyy-MM-dd") AS WeekEndDate,
     (DateTime.ParseExact(sfc.MaxChangedOnDate, @datetimeformat, null)).ToString("yyyy-MM-dd") AS MaxChangedOnDate,
     (DateTime.ParseExact(sf.LockDate, @datetimeformat, null)).ToString("yyyy-MM-dd") AS LockDate
FROM @ScoringFeedControlData AS sfc
      INNER JOIN
     @ScoringFeedData AS sf
      ON sfc.ScoringFeedGUID == sf.ScoringFeedGUID
      INNER JOIN
          @CalendarFileData AS sc
      ON sfc.CalendarGUID == sc.CalendarGUID
      INNER JOIN
          @CalendarFileData AS wc
      ON wc.EndDate BETWEEN sc.StartDate AND sc.EndDate
      INNER JOIN
          @PeriodTypeFileData AS pt
      ON pt.PeriodTypeGUID == wc.PeriodTypeGUID
      INNER JOIN
          @CalendarFileData AS qc
      ON wc.EndDate BETWEEN qc.StartDate AND qc.EndDate
      INNER JOIN
          @PeriodTypeFileData AS qpt
      ON qpt.PeriodTypeGUID == qc.PeriodTypeGUID
      INNER JOIN
          @CalendarFileData AS mc
      ON wc.EndDate BETWEEN mc.StartDate AND mc.EndDate
      INNER JOIN
          @PeriodTypeFileData AS mpt
      ON mpt.PeriodTypeGUID == mc.PeriodTypeGUID.

What conditions are not allowed and allowed in join conditions in usql scripts?

I edited the code section to make the question more readable. — Michael Rys, Dec 06 '18 at 20:24

score 0 · Answer 1 · answered Dec 06 '18 at 20:23

According to the documentation, you can only provide conjunctions of equi-joins. Any other join condition either has to be turned into an equi-join, e.g., by having computed columns that map into a "category" value (only works under certain conditions) or by moving the join condition into the WHERE clause (in case of INNER JOIN) or into the WHERE clause and additional UNIONs (in case of OUTER JOINs). If there is no equi-join condition left, you turn the INNER JOIN into a CROSS JOIN.

The reason for that is that the underlying engine has more optimal distributed execution strategies for equijoins, but other condition are as expensive as the user-rewrites and we decided that we want to make the cost visible to the query author.

In your case, I would expect that turning the INNER JOIN into a CROSS JOIN and moving the BETWEEN predicate into the WHERE clause is the solution. You may also want to make sure that the join order will do the cross join at the end, where you get the least data explosion (the optimizer may do that, but sometimes you need to "help" it).

Thanks so much Michael – Prashanthi Bejugam Dec 07 '18 at 21:48 — Prashanthi Bejugam, Dec 07 '18 at 21:48

Joins in USQL scripts

1 Answers1