Using CROSS JOIN LATERAL in Postgres
Introduction In standard SQL, subqueries located in the JOIN clause operate independently, they cannot see or use data from tables located before (to the left of) it. When you add the LATERAL keyword, Postgres allows the subquery on the right to directly access column values of each row in the table on the left. Differences between Join types are as follows Standard CROSS JOIN takes the Cartesian product of 2 tables independent of each other (a multiplication of 2 tables without needing a condition) CROSS JOIN LATERAL operates like a for-each loop in programming, with each row in the left table, Postgres runs the subquery on the right to perform dynamic calculations repeatedly based on values from the left table and joins the results together. INNER JOIN is the intersection between 2 datasets, it only retains rows in 2 tables when both satisfy a specific join condition, the condition in ON is mandatory (unlike CROSS JOIN which does not require passing a condition) Can be combined...