How To Dev

Introduction First, let us look at the concept of the three basic relationships in a Relational Database defined based on the fundamental nature of Cardinality links between two tables: One-to-One Relationship (1-1) A record in Table A is linked to exactly one record in Table B and vice versa. Implemented by placing a Foreign Key in one of the two tables and assigning it a UNIQUE constraint. Examples: A User has only one UserProfile. A Product has only one ProductDetail. When to use: When splitting an oversized table containing rarely used columns to optimize data read performance. When security is required: Separating sensitive information such as credit cards or passwords into a dedicated table with stricter access controls. One-to-Many Relationship (1-N) A record in Table A can be linked to multiple records in Table B. Conversely, a record in Table B is linked to only one record in Table A. Implemented by placing a Foreign Key in the "Many" side table (Table B) pointing t...

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