3 ms·
Once I started thinking about joins in terms of spatial dimensions, things got a lot easier to reason with. I like to think of the inner join like the scene fro
by bob1029 3y ago
Once I started thinking about joins in terms of spatial dimensions, things got a lot easier to reason with. I like to think of the inner join like the scene from Stargate where they were describing how gate addressing works. Assume you have a contrived example with 3 tables describing the position of an off world probe in each dimension - X, Y and Z. Each table looks like:
CREATE TABLE Dim_X (
int EntityId
float Value
)
Establishing a point in space for a given entity (regardless of how you derive that ID) is then a matter of:
SELECT Dim_X.Value AS X, Dim_Y.Value as Y, Dim_Z.Value as Z
FROM Dim_X, Dim_Y, Dim_Z
WHERE Dim_X.EntityId = Dim_Y.EntityId --Join 1
AND Dim_Y.EntityId = Dim_Z.EntityId --Join 2
AND Dim_X.EntityId = @MyEntityId --The entity we want to find the 3D location of
You will note that there are 2 inner joins used in this example. That is the bare minimum needed to construct a 3 dimensional space. Think about taking 3 cards and taping them together with 2 pieces of rigid metal tape. You can make a good corner of a cube, even if it's a bit floppy on one edge. Gravity doesn't apply inside the RDBMS, so this works out.
This same reasoning can be extrapolated to higher, non-spatial dimensions. Think about adding time into that mix. In order to find an entity, you also now need to perform an inner join on that dimension and constrain on a specific time value. If you join but fail to constrain on a specific time, then you get a happy, comprehensive report of all the places an entity was over time.
The other join types are really minor variations on these themes once you have a deep conceptual grasp (i.e. can "rotate" the schema in your mind).
Playing around with some toy examples can do wonders for understanding. I sometimes find myself going back to the cartesian coordinate example when stuck trying to weld together 10+ dimensions in a real-world business situation.
- iamcreasy 3y agoI think you are saying that every join is a variation of cross join. Did I understand you correctly?
- michaelmior 3y agoThis reminds me a lot of HyperDex[0], which hashes values into a multidimensional hyperspace based on their attributes for indexing purposes. [0] https://dbdb.io/db/hyperdex https://dbdb.io/db/hyperdex
- 1970-01-01 3y agoOLAP and MOLAP https://www.ibm.com/topics/olap https://www.ibm.com/topics/olap https://en.wikipedia.org/wiki/Online_analytical_processing#Multidimensional_OLAP_.28MOLAP.29 https://en.wikipedia.org/wiki/Online_analytical_processing#M...
- toyg 3y agoThat Wikipedia article, and it's offshoot pages, are so awfully out of date... But yes, OLAP.
- rjbwork 3y agoThis is like, a hypernormalization of the data though, isn't it? I think in standard BCNF you'd just leave your table as CREATE TABLE EntityPosition ( int EntityId, float X, float y, float z ) It does remind me of data warehouse stuff though, given we're working with aggregates and piecing together bits of various dimensions.
- jagged-chisel 3y ago> …a hypernormalization of the data … Well, yeah - it’s an example to get the point across, not an exercise in finding the right level of normalization.
- farkanoid 3y agoGreat. Now you've made me want to re-watch Stargate!
- quasarj 3y agoI now understand joins less than I did before.
- barrkel 3y agoIt's more a description of how dimensions work in OLAP and it's incomplete without measures.
- cwbrandsma 3y agoI miss MDX (language behind MS Analysis Service).
- spopejoy 3y agoYour example raises a question I've never found a good answer to: Why use `JOIN` (and `INNER JOIN` and friends) at all when you can write the exact join more accurately IMO with WHERE clauses (and using "*=" operators), and then just list all the tables in the FROM clause? My brain always hurts trying to parse complex FROM clauses with various JOINs whereas reading the equivalent equations in WHERE clauses is way more straightforward to me.
- iamcreasy 3y agoI have had this issue, and solved it by learning that join happens from left to right. `A left join B left join C` is equivalent to ((A left join B) left join C).