Understanding Subquery and Common Table Expressions(CTEs)

try { if(localStorage) { let currentUser = localStorage.getItem('current_user'); if (currentUser) { currentUser = JSON.parse(currentUser); if (currentUser.id === 3564637) { document.getElementById('article-show-container').classList.add('current-user-is-article-author'); } } } } catch (e) { console.error(e); } Gabriel Njoroge Posted on Apr 28 Understanding Subquery and Common Table Expressions(CTEs) #beginners #tutorial #database #sql What is a Subquery? A subquery is simply a select statement written inside another select statement. The inside query runs first and results is handed to outer query for use. Example: Explanation i)First it finds the Average Score ii)It finds all rows which are greater than the Average score. Types of Subqueries 1. Scalar Subquery It returns a single value It will output the single highest score recorded. 2. List Subquery It returns a column of values. e.g List of IDs. It is used with IN or NOT IN. It will output the names of members who scored above 80. 3. Subquery IN FROM You can place subquery in the from clause. SQL treats subquery as if it were a temporary table. When to use Subqueries When logic is simple and needs no re-use when you want to filter data based on aggregated values when you need a quick, one time calculation CTEs or Common Table Expressions It is a temporary result set defined at the start of a query using the WITHkeyword. It acts like a named query you can reference later. Types of CTEs and their uses Recursive CTEs it references itself independently Uses for organization of data in a systematic way used in finding paths in different places to create lists or series of data Non-Recursive CTEs frequently used in sql and refers to a set of script you can use anytime. Uses Used in referencing e.g reusing codes Used in listing multiple CTEs for simplifying results e.g AVG Comparisons between Subquery and CTEs Subquery is nested inside while CTE is nested outside Subquery cannot be reused while CTE are commonly used within same query. Subquery is used for simple filtering while CTEs for complex ones When it comes to maintenance subqueries are quite challenging to maintain due to logic while CTEs are easier. Top comments (0) Subscribe Personal Trusted User Create template Templates let you quickly answer FAQs or store snippets for re-use. Submit Preview Dismiss Code of Conduct • Report abuse Are you sure you want to hide this comment? It will become hidden in your post, but will still be visible via the comment's permalink. Hide child comments as well Confirm For further actions, you may consider blocking this person and/or reporting abuse

This excerpt is published under fair use for community discussion. Read the full article at DEV Community.