How To Dev

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...

Introduction Core Concepts Optimizer This is the general name for the entire component responsible for receiving SQL statements, analyzing them and deciding how the system will retrieve the data. Its task is to transform a declarative SQL statement (such as what data to get) into a specific physical execution plan (how to get that data step by step). This is the most general concept that most current databases like SQL or NoSQL use to automatically calculate the most efficient way to execute a query. Cost-based Optimizer This is a specific type of Optimizer that operates based on the principle of Cost calculation. It helps the Optimizer choose the most efficient execution path. To see the difference clearly, there are two main schools of Optimizer in computer science history: Rule-based Optimizer (RBO): Chooses the path based on fixed, rigid rules (For example: if an Index is available, it must be used, regardless of table size), which lacks flexibility and is now obsolete. Cost-based ...