set-operations-and-union-reasoning

# Skill: set operations and union reasoning ## 1. Capability Definition & Real Case * **Professional Definition**: The ability to compose SQL queries that utilize set theory and relational algebra to merge (UNION, UNION ALL) or logically contrast (INTERSECT, EXCEPT, NOT IN, NOT EXISTS) the outputs of independent subqueries. This necessitates assuring accurate structural compatibility across branches while enforcing rigid set semantics for tracking comprehensive global sets and mathematical complements. * **Dimension Hierarchy**: Query Reasoning->Relational and Logical Composition->set operations and union reasoning ### Real Case **[Case 1]** * **Initial Environment**: A retail database containing two structurally similar tables logically segregated by source domain: 'web_lineitem' and 'store_lineitem'. Strict set combinations mandate identical categorical projection structures. * **Real Question**: Show all sales records for December 2024 across both the website and physical stores, including duplicates if the same part was sold in both channels. * **Real Trajectory**: Establish primary 'web_lineitem' table dependencies aligning structural temporal boundaries accurately. Duplicate parallel isolation protocols defining exact categorical projections parsing the identical 'store_lineitem' repository independently. Natively embed UNION ALL directives to satisfy raw entity preservation parameters. * **Real Answer**: SELECT partkey, quantity FROM web_lineitem WHERE shipdate BETWEEN '2024-12-01' AND '2024-12-31' UNION ALL SELECT partkey, quantity FROM store_lineitem WHERE shipdate BETWEEN '2024-12-01' AND '2024-12-31'; * **Why this demonstrates the capability**: This exhibits fundamental holistic data tracking bridging independently partitioned structures. Mapping matching relational subsets from discrete tables via identical parallel projections establishes core additive set tracking. --- **[Case 2]** * **Initial Environment**: A library database utilizing independent tracking silos isolating active physical 'checkouts' away from main categorical 'books' databases identifying static publication metrics. * **Real Question**: Find the titles of all books with more than 500 pages that have never been checked out by a user. * **Real Trajectory**: Perform elemental filtering routines isolating 'books' matrices spanning precise page metrics. Initiate secondary structural logic isolating primary key footprints encompassing 'checkouts'. Employ rigorous Boolean negation (NOT IN) subtracting secondary circulation subsets actively excising tracked overlap records. * **Real Answer**: SELECT title FROM books WHERE page_count > 500 AND book_id NOT IN (SELECT book_id FROM checkouts); * **Why this demonstrates the capability**: This enforces rigid mathematical set exclusion (difference). Bridging disparate functional domains successfully mapping positive metadata values explicitly against absolute relational absence constraints demonstrates complex domain negation. --- **[Case 3]** * **Initial Environment**: A scientific metadata research database defining directed citation relationships relying exclusively upon unified categorical tables 'articles' intersecting specifically through 'citing_cited' crosswalks mapping interconnected IDs. * **Real Question**: How many articles are cited by other articles but do not cite any other articles themselves? * **Real Trajectory**: Isolate comprehensive base structures aggregating native research arrays. Identify 'cited' vectors systematically invoking structural inclusion (IN). Sequentially calculate 'citing' vectors utilizing discrete set negation (NOT IN) subtracting independent parameters tracking complementary relationship directions. * **Real Answer**: SELECT COUNT(*) FROM articles WHERE article_id NOT IN (SELECT article_id_citing FROM citing_cited) AND article_id IN (SELECT article_id_cited FROM citing_cited); * **Why this demonstrates the capability**: This characterizes multi-layered set modeling evaluating intersecting entity nodes explicitly bridging dual-directional metadata relationships natively, validating profound absence parameters overriding intersection arrays. ## Pipeline Execution Instructions To synthesize data for this capability, you must strictly follow a 3-phase pipeline. **Do not hallucinate steps.** Read the corresponding reference file for each phase sequentially: 1. **Phase 1: Environment Exploration** Read the exploration guidelines to discover raw knowledge seeds: `references/EXPLORATION.md` 2. **Phase 2: Trajectory Selection** Once Phase 1 is complete, read the selection criteria to evaluate the trajectory: `references/SELECTION.md` 3. **Phase 3: Data Synthesis** Once a trajectory passes Phase 2, read the synthesis instructions to generate the final data: `references/SYNTHESIS.md`