https://outerbase.com/_next/static/chunks/pages/learn/sql-outer-join-251196bb6a9cccfe.js

1(self.webpackChunk_N_E=self.webpackChunk_N_E||[]).push([[4186],{8430:(e,t,s)=>{(window.__NEXT_P=window.__NEXT_P||[]).push(["/learn/sql-outer-join",function(){return s(6508)}])},6508:(e,t,s)=>{"use strict";s.r(t),s.d(t,{default:()=>u});var n=s(5105),r=s(9358),o=s(976),a=s(3588),i=s(1189),d=s(2967),l=s(8101);let c=[{title:"Understanding OUTER JOINs",instruction:"\nOUTER JOINs allow you to retrieve all records from one or both tables in a join operation, even when there are no matches.\n\nTypes of OUTER JOINs:\n• LEFT JOIN - Keep all records from left table\n• RIGHT JOIN - Keep all records from right table\n• FULL OUTER JOIN - Keep all records from both tables\n\nBasic Syntax:\n```sql\nSELECT columns\nFROM table1\nLEFT JOIN table2\nON table1.column = table2.column;\n```\n\nKey Differences from INNER JOIN:\n• Returns non-matching rows\n• NULL values for non-matches\n• More comprehensive results\n• Better for data analysis\n",hasEditor:!1},{title:"Basic LEFT JOIN",instruction:"\nLet's start with a LEFT JOIN to find all customers and their orders (if any).\n\nWrite a query that gets:\n• Customer name and email\n• Order total (may be NULL)\n• From customers LEFT JOIN orders\n• Order by customer name\n\nTips:\n• LEFT table (customers) keeps all records\n• RIGHT table (orders) provides matches\n• NULL indicates no matching order\n• Consider using COALESCE for NULLs\n",hasEditor:!0,initialQuery:"-- Get all customers and their orders\n",solution:"SELECT c.name, c.email, o.total FROM customers c LEFT JOIN orders o ON c.id = o.customer_id ORDER BY c.name",hint:"Use LEFT JOIN to keep all customers, even those without orders.",tables:["customers","orders"]},{title:"Finding Missing Data",instruction:"\nOUTER JOINs are perfect for finding missing relationships.\n\nWrite a query that finds:\n• All customers who have never placed an order\n• Show their name and email\n• Using LEFT JOIN and WHERE\n\nUse Cases:\n• Finding inactive customers\n• Identifying orphaned records\n• Data quality checks\n• Marketing analysis\n\nNote: WHERE clause with NULL check finds missing relationships.\n",hasEditor:!0,initialQuery:"-- Find customers with no orders\n",solution:"SELECT c.name, c.email FROM customers c LEFT JOIN orders o ON c.id = o.customer_id WHERE o.id IS NULL",hint:"Use LEFT JOIN and check for NULL in the orders table.",tables:["customers","orders"]},{title:"Multiple OUTER JOINs",instruction:"\nWe can chain multiple OUTER JOINs together.\n\nWrite a query that gets:\n• Product name\n• Category name (may be NULL)\n• Count of orders (may be 0)\n• Using appropriate LEFT JOINs\n\nAdvanced Concepts:\n• Chaining multiple joins\n• Handling multiple NULL possibilities\n• Aggregating with outer joins\n• Proper join order\n\nRemember: Join order matters with OUTER JOINs.\n",hasEditor:!0,initialQuery:"-- Analyze products, categories, and orders\n",solution:"SELECT p.name, c.name as category, COUNT(o.id) as order_count FROM products p LEFT JOIN categories c ON p.category_id = c.id LEFT JOIN orders o ON p.id = o.product_id GROUP BY p.name, c.name",hint:"Use two LEFT JOINs and COUNT with GROUP BY.",tables:["products","categories","orders"]},{title:"Comparing JOIN Types",instruction:"\nLet's see the difference between INNER and LEFT JOIN.\n\nWrite two queries:\n1. INNER JOIN products and orders\n2. LEFT JOIN products and orders\nCompare the counts using UNION ALL\n\nLearning Points:\n• INNER JOIN shows only matches\n• LEFT JOIN shows all left records\n• Difference reveals missing data\n• Important for data analysis\n\nThis helps understand when to use each type.\n",hasEditor:!0,initialQuery:"-- Compare INNER vs LEFT JOIN results\n",solution:"SELECT 'INNER' as join_type, COUNT(*) as count FROM products p INNER JOIN orders o ON p.id = o.product_id UNION ALL SELECT 'LEFT' as join_type, COUNT(*) as count FROM products p LEFT JOIN orders o ON p.id = o.product_id",hint:"Use UNION ALL to show both result counts.",tables:["products","orders"]},{title:"Data Analysis with OUTER JOINs",instruction:"\nUse OUTER JOINs for c

1omprehensive data analysis.\n\nWrite a query that shows:\n• Category name\n• Total products in category\n• Products with orders\n• Products without orders\n• Using LEFT JOINs\n\nAnalysis Goals:\n• Category performance\n• Product engagement\n• Missing relationships\n• Data completeness\n\nThis provides valuable business insights.\n",hasEditor:!0,initialQuery:"-- Analyze category and product performance\n",solution:"SELECT c.name, COUNT(p.id) as total_products, COUNT(DISTINCT o.product_id) as products_ordered, COUNT(p.id) - COUNT(DISTINCT o.product_id) as products_not_ordered FROM categories c LEFT JOIN products p ON c.id = p.category_id LEFT JOIN orders o ON p.id = o.product_id GROUP BY c.name",hint:"Use LEFT JOINs and COUNT with DISTINCT.",tables:["categories","products","orders"]},{title:"Advanced OUTER JOIN Patterns",instruction:"\nCombine OUTER JOINs with other SQL features.\n\nWrite a query that:\n• Shows customer order summary\n• Includes total orders and total spent\n• Shows last order date\n• Includes customers with no orders\n• Orders by total spent (highest first)\n\nThis combines:\n• LEFT JOIN\n• Aggregation\n• Date functions\n• Sorting\n• NULL handling\n\nA real-world reporting query.\n",hasEditor:!0,initialQuery:"-- Create customer order summary\n",solution:"SELECT c.name, COUNT(o.id) as order_count, COALESCE(SUM(o.total), 0) as total_spent, MAX(o.created_at) as last_order FROM customers c LEFT JOIN orders o ON c.id = o.customer_id GROUP BY c.name ORDER BY total_spent DESC",hint:"Use LEFT JOIN with aggregates and COALESCE.",tables:["customers","orders"]}],u=()=>{let e=(0,r.useRouter)(),[t,s]=(0,l.useState)(new Set),u=e=>{s(t=>new Set(Array.from(t).concat(e)))},h=e=>{},N=t.size===c.filter(e=>e.hasEditor).length;return(0,n.jsx)(o.A,{metaTagsProps:{title:"SQL OUTER JOIN Tutorial: Master Data Relationships | Outerbase",description:"Learn how to retrieve all records with SQL OUTER JOIN. Master LEFT JOIN, RIGHT JOIN, and complex outer join patterns with practical examples."},children:(0,n.jsx)(a.A,{lessonCompleted:N,children:(0,n.jsxs)("div",{className:"space-y-12",children:[(0,n.jsxs)("header",{className:"prose prose-neutral prose-invert max-w-none space-y-4",children:[(0,n.jsx)("h1",{children:"SQL OUTER JOIN"}),(0,n.jsx)("p",{className:"lead",children:"Master SQL OUTER JOIN operations to retrieve complete datasets. Learn about LEFT JOIN, RIGHT JOIN, and complex outer join patterns through hands-on examples."})]}),(0,n.jsxs)("nav",{className:"rounded-lg border border-white/10 bg-white/5 p-4",children:[(0,n.jsx)("h2",{className:"text-sm font-medium text-white/60",children:"Quick Navigation"}),(0,n.jsx)("ul",{className:"mt-2 space-y-1",children:c.map((e,t)=>(0,n.jsx)("li",{children:(0,n.jsx)("a",{href:"#".concat(e.title.toLowerCase().replace(/\s+/g,"-")),className:"text-sm text-white/80 hover:text-white",children:e.title})},t))})]}),(0,n.jsx)("div",{className:"space-y-16",children:c.map((e,t)=>(0,n.jsxs)("section",{id:e.title.toLowerCase().replace(/\s+/g,"-"),className:"scroll-mt-8",children:[(0,n.jsxs)("div",{className:"prose prose-neutral prose-invert max-w-none space-y-4",children:[(0,n.jsx)("h2",{children:e.title}),(0,n.jsx)(d.A,{content:e.instruction})]}),e.hasEditor&&(0,n.jsx)("div",{className:"mt-8 space-y-4",children:(0,n.jsx)(i.A,{initialQuery:e.initialQuery||"",tables:e.tables||[],onSuccess:()=>u(t),onError:h,expectedSolution:e.solution,hint:e.hint},t)})]},t))}),(0,n.jsxs)("section",{className:"flex justify-between border-t border-white/10 pt-8",children:[(0,n.jsx)("button",{onClick:()=>{e.push("/learn/sql-join")},className:"rounded-md bg-white/5 px-4 py-2 text-sm font-semibold text-white transition-all hover:bg-white/10",children:"← Previous: SQL Join"}),(0,n.jsx)("button",{onClick:()=>{e.push("/learn/sql-group-by")},className:"rounded-md bg-white/90 px-4 py-2 text-sm font-semibold text-black transition-all hover:bg-white",children:"Next: SQL Group By →"})]})]})})})}}},e=>{var t=t=>e(e.s=t);e.O(0,[3701,4192,5598,3870,801,4775,6752,5088,636,6593,8792],()=>t(8430)),_N_E=e.O()}]);