How To Dev

Introduction Subquery : A SELECT statement located inside another SQL statement (can be inside SELECT, FROM, WHERE, HAVING ). It supplies data for the main query. Nested Query : A term used to describe the structure of the query. When a Subquery resides inside a parent statement, this action is called nesting. Thus, Subquery can be viewed as the component (the child), while Nested query is the structural relationship (the parent containing the child). A statement that contains a subquery has its entire structure referred to as a nested query . Classification Non-correlated Subquery : This type of subquery runs completely independently of the parent statement. Postgres executes this subquery exactly once, using its result to apply to the parent statement. Example: SELECT name, salary FROM employees WHERE salary > (SELECT AVG(salary) FROM employees) Here, SELECT AVG(salary) FROM employees is an independent Subquery that only needs to run once to provide the value for the outer ...

Introduction A Common Table Expression (CTE) is considered a temporary table variable that only exists within the scope of a single query statement ( SELECT, INSERT, UPDATE or DELETE ). It helps you encapsulate a complex query logic cluster into a variable for reuse immediately below. In Postgres, the WITH clause is used as the syntax keyword to define a CTE . Features The scope of existence is only within a single query statement. Stored data is typically processed in RAM (in-memory) or temporarily saved as a file if the data is too large. No need to clean up ( drop ) after execution, it will automatically disappear as soon as the statement finishes executing. A special feature is the support for recursion ( WITH RECURSIVE ), which is highly powerful when processing tree or hierarchical data structures. Use cases The following are suitable use cases for using a CTE including You want to write clean and readable code, breaking down complex JOIN statements into individual steps. Sm...