One thing that I have noticed confuses a lot of people is "timeseries joins" (I don't know the real term for this) I'm talking about where there is no "one to one" match between keys in the two tables.
I'm a non software type of engineer in my world a lot of tables are structured as timeseries data (such as readings from a device or instrument) which uses timestamp as a key.
Then we have other tables which log event or batch data (such as an alarm start and end time, or Machine start/machine stop etc).
So a lot of queries end up being of the form
Select A.AlarmId, B.Reading, B.Timestamp from Alarms A, Readings B where A.StartTime >= B.Timestamp and A.EndTime < B.Timestamp
A lot of people seem to have problems grasping these kinds of joins.
Cool use case. They're just called "non equi-joins" - because the join condition is an inequality. In general a join produces a row in the output table for each (left, right) pair of rows from the input tables that satisfies the join condition. It's just so common for joins to use a simple equality condition, where one or both sides is a unique id for its table, and people don't as often encounter joins where one input row can inform multiple output rows.
I'm a non software type of engineer in my world a lot of tables are structured as timeseries data (such as readings from a device or instrument) which uses timestamp as a key.
Then we have other tables which log event or batch data (such as an alarm start and end time, or Machine start/machine stop etc).
So a lot of queries end up being of the form
Select A.AlarmId, B.Reading, B.Timestamp from Alarms A, Readings B where A.StartTime >= B.Timestamp and A.EndTime < B.Timestamp
A lot of people seem to have problems grasping these kinds of joins.