SQL tips: get exclusive data set

Posted on July 10, 2022 at 11:20 AM


In data science & data engineering, it is often required to extracting exclusive data set from a large-scale table in a business date comparing with a history data, e.g. daily new product incremental, weekly new users, ..... Here two ways are discussed using an example of how to calculate daily new products.

Method 1: use LEFT JOIN operator, e.g.

-- daily_biz_tb is table to store daily product with schema like 
-- CREATE TABLE IF NOT EXISTS daily_biz_tb (product_id STRING, title STRING) PARTITION BY (ds STRING)

DROP TABLE IF EXISTS daily_inc_tb;
CREATE TABLE daily_inc_tb LIFECYCLE 7 AS 
SELECT t1.* FROM (
  SELECT product_id, title 
  FROM daily_biz_tb 
  WHERE ds='${bizdate}'
)t1
LEFT JOIN (
  SELECT product_id, title 
  FROM daily_biz_tb 
  WHERE ds='${yesterday}'
)t2
ON t1.product_id=t2_product_id
WHERE t2.product_id IS NULL
;

The logic is that if product_id in today but not in yesterday, after left join, the column t2.product_id will be NULL in today.

Method 2: left anti join. The LEFT ANTI JOIN will be much elegant. It will exclude all item based on key product_id. 

-- daily_biz_tb is table to store daily product with schema like 
-- CREATE TABLE IF NOT EXISTS daily_biz_tb (product_id STRING, title STRING) PARTITION BY (ds STRING)

DROP TABLE IF EXISTS daily_inc_tb;
CREATE TABLE daily_inc_tb LIFECYCLE 7 AS 
SELECT t1.* FROM (
  SELECT product_id, title 
  FROM daily_biz_tb 
  WHERE ds='${bizdate}'
)t1
LEFT ANTI JOIN (
  SELECT product_id, title 
  FROM daily_biz_tb 
  WHERE ds='${yesterday}'
)t2
ON t1.product_id=t2_product_id
;

Which one is recommended? Of course, left anti join. It is not only elegant but also performance better when from a big table excluding data in a small table. e.g. 1T table excluding 1K data in small table. In practice, method 1 sometimes has performance issue while method 2 fast.


FEATURED TAGS
computer program javascript nvm node.js Pipenv Python 美食 AI artifical intelligence Machine learning data science digital optimiser user profile Cooking cycling green railway feature spot 景点 work technology F1 中秋节 dog setting sun sql photograph Alexandra canal flowers bee greenway corridors programming C++ passion fruit sentosa Marina bay sands pigeon squirrel Pandan reservoir rain otter Christmas orchard road PostgreSQL fintech sunset thean hou temple in sungai lembing 海上日出 SQL optimization pieces of memory 回忆 garden festival ta-lib backtrader chatGPT generative AI stable diffusion webui draw.io streamlit LLM speech recognition AI goverance prompt engineering fastapi stock trading artificial-intelligence Tariffs AI coding AI agent FastAPI 人工智能 Tesla AI5 AI6 FSD AI Safety AI governance LLM risk management Vertical AI Insight by LLM LLM evaluation AI safety enterprise AI security AI Governance Privacy & Data Protection Compliance Microsoft Scale AI Claude Anthropic 新加坡传统早餐 咖啡 Coffee Singapore traditional coffee breakfast Quantitative Assessment Oracle OpenAI Market Analysis Dot-Com Era AI Era Rise and fall of U.S. High-Tech Companies Technology innovation Sun Microsystems Bell Lab Agentic AI McKinsey report Dot.com era AI era Speech recognition Natural language processing ChatGPT Meta Privacy Google PayPal Edge AI Enterprise AI Nvdia AI cluster COE Singapore Shadow AI AI Goverance & risk Tiny Hopping Robot Robot Materials SCIGEN RL environments Reinforcement learning Continuous learning Google play store AI strategy Model Minimalism Fine-tuning smaller models LLM inference Closed models Open models Privacy trade-off MIT Innovations Federal Reserve Rate Cut Mortgage Interest Rates Credit Card Debt Management Nvidia SOC automation Investor Sentiment Enterprise AI adoption AI Innovation AI Agents AI Infrastructure Humanoid robots AI benchmarks AI productivity Generative AI Workslop Federal Reserve AI automation Multimodal AI AI agents AI integration Market Volatility Government Shutdown Rate-cut odds AI Fine-Tuning LLMOps Frontier Models Hugging Face Multimodal Models Energy Efficiency AI coding assistants AI infrastructure Semiconductors Gold & index inclusion Multimodal Chinese open-source AI AI hardware Semiconductor supply chain Open-Source AI prompt injection LLM security AI spending AI Bubble Quantum Computing Open-source AI AI shopping Multi-agent systems AI research breakthroughs AI in finance Financial regulation Custom AI Chips Solo Founder Success Newsletter Business Models Indie Entrepreneur Growth Apple AI chips robotaxi AI security embodied AI AI tools IPO artificial intelligence venture capital multimodal AI AI chatbot AI browser space funding quantum computing DeepSeek enterprise AI AI investing AI investment prompt injection attacks AI red teaming agentic browsing agentic AI cybersecurity AI search model quantization AI therapy AI bubble