Coding
AI Prompt for Complex SQL Joins
Master complex SQL joins with this AI prompt: multi-table queries, subqueries and anti-joins explained with Venn-style logic and verified results.
The prompt
Role: You are a SQL specialist who makes multi-table queries intuitive. Context: Dialect: [DIALECT]. Goal in plain English: [GOAL]. Tables and relationships: [SCHEMA] Task: 1) Write the query using the right technique for the goal (INNER/LEFT JOIN, EXISTS/NOT EXISTS, IN, correlated subquery — choose deliberately and say why). 2) Explain the join logic in plain English: which rows survive each step and why. 3) Show how to verify correctness: a small sample dataset and the expected result rows. 4) Warn about the classic traps for this pattern (NULLs in NOT IN, duplicate multiplication from one-to-many joins, etc.). Format: Query first, then the plain-English logic, then the verification sample, then the traps. Ask the AI to demonstrate the difference between the chosen technique and its closest alternative on your sample data, showing the rows each returns. Seeing the divergence on real rows cements when to use which. Constraints: Prefer the clearest correct technique over the cleverest. Handle NULLs explicitly — state your assumption about nullable join keys.
Variables to fill in
Replace each [variable] in the prompt with your own details:
[DIALECT] [GOAL] [SCHEMA]
Example input
DIALECT=MySQL, GOAL=customers who bought in 2026 but not 2025, plus their lifetime value, TABLES=customers, orders(customer_id, total, created_at, status)
How to use this prompt
Describe the result set in plain words first ('customers who did X but not Y') — that phrasing maps directly to the right SQL pattern. Include the relationship cardinalities.
Tips for the best results
- Ask 'why this technique and not the alternative' to learn the decision framework.
- The verification sample is gold — run it to prove the query right.
- For NOT IN queries, always ask about the NULL trap explicitly.
Frequently asked questions
JOIN vs subquery vs EXISTS — which?
It shows both and explains when each wins — EXISTS for semi-joins, IN for small lists, JOINs for aggregations.
When should I use EXISTS instead of IN?
EXISTS for correlated semi-joins on large tables, IN for small static lists. And never use NOT IN with nullable columns — the prompt warns about that trap.