Loading the chapter…
%23%20%2F%2F%2F%20script%0A%23%20requires-python%20%3D%20%22%3E%3D3.12%2C%3C3.15%22%0A%23%20dependencies%20%3D%20%5B%0A%23%20%20%20%20%20%22marimo%5Bsql%5D%22%2C%0A%23%20%20%20%20%20%22duckdb%22%2C%0A%23%20%5D%0A%23%20%2F%2F%2F%0A%22%22%22SQL%20Handbook%2C%20Chapter%20C%3A%20More%20Than%20One%20Table.%0A%22%22%22%0A%0Aimport%20marimo%0A%0A__generated_with%20%3D%20%220.25.2%22%0Aapp%20%3D%20marimo.App(width%3D%22medium%22%2C%20sql_output%3D%22polars%22)%0A%0A%0A%40app.cell%0Adef%20_()%3A%0A%20%20%20%20import%20duckdb%0A%20%20%20%20import%20marimo%20as%20mo%0A%0A%20%20%20%20return%20duckdb%2C%20mo%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%20More%20Than%20One%20Table%0A%0A%20%20%20%20Chapter%20C%20of%20the%20SQL%20Handbook.%20Seven%20sections%2C%20each%20one%20concept.%0A%0A%20%20%20%20Chapter%20B%20asked%20one%20table%20a%20question.%20A%20database%20splits%20a%20business%20across%20many%20tables%0A%20%20%20%20on%20purpose%20and%20puts%20a%20key%20in%20each%20one%2C%20so%20almost%20every%20real%20question%20needs%20two%20of%20them%0A%20%20%20%20or%20more.%20This%20chapter%20is%20how%20you%20put%20them%20back%20together%2C%20and%20what%20happens%20to%20your%0A%20%20%20%20numbers%20when%20you%20do.%0A%0A%20%20%20%20Sections%2020%20and%2023%20are%20marked%20%60%3E%20**Advanced**%60.%20Nothing%20in%20this%20course%20requires%20them.%0A%0A%20%20%20%20Every%20query%20below%20runs%20against%20the%20course%20warehouse%2C%20Northwind.%20You%20can%20read%20this%0A%20%20%20%20straight%20through%2C%20or%20open%20it%20when%20you%20need%20one%20thing.%20Every%20section%20ends%20with%0A%20%20%20%20questions%20that%20have%20no%20answers%20written%20down%2C%20because%20those%20are%20the%20ones%20asked%20out%0A%20%20%20%20loud%20in%20class.%0A%0A%20%20%20%20To%20run%20the%20code%20yourself%2C%20download%20the%20%60.py%60%20into%20your%20project%20and%20open%20it%20with%0A%20%20%20%20%60uv%20run%20marimo%20edit%60.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20Opening%20the%20warehouse%0A%0A%20%20%20%20The%20cell%20below%20loads%20the%20parquet%20files%20in%20%60data%2Fnorthwind%2F%60%20into%20DuckDB%20as%20tables.%20How%0A%20%20%20%20that%20works%20is%20Part%20D%2C%20sections%2026%20and%2027%2C%20and%20nothing%20in%20this%20chapter%20depends%20on%0A%20%20%20%20reading%20it.%0A%0A%20%20%20%20One%20name%20matters%20from%20the%20first%20query.%20The%20file%20is%20%60order-details.parquet%60%20and%20the%0A%20%20%20%20table%20is%20%60%22Order%20Details%22%60%2C%20with%20a%20space%20in%20it%2C%20so%20every%20mention%20of%20it%20needs%20double%0A%20%20%20%20quotes.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(duckdb%2C%20mo)%3A%0A%20%20%20%20%23%20Look%20for%20a%20data%2Fnorthwind%20folder%20here%20and%20in%20every%20folder%20above%2C%20so%20the%0A%20%20%20%20%23%20chapter%20works%20wherever%20you%20save%20it.%20If%20there%20is%20none%2C%20read%20the%20copy%20on%20the%0A%20%20%20%20%23%20course%20site%20instead.%0A%20%20%20%20_here%20%3D%20mo.notebook_dir()%0A%20%20%20%20_source%20%3D%20next(%0A%20%20%20%20%20%20%20%20(p%20%2F%20%22data%22%20%2F%20%22northwind%22%20for%20p%20in%20%5B_here%2C%20*_here.parents%5D%0A%20%20%20%20%20%20%20%20%20if%20(p%20%2F%20%22data%22%20%2F%20%22northwind%22).is_dir())%2C%0A%20%20%20%20%20%20%20%20%22https%3A%2F%2Foim7510.github.io%2Fdata%2Fnorthwind%22%2C%0A%20%20%20%20)%0A%0A%20%20%20%20con%20%3D%20duckdb.connect()%0A%0A%20%20%20%20_files%20%3D%20%7B%0A%20%20%20%20%20%20%20%20%22categories%22%3A%20%22categories%22%2C%0A%20%20%20%20%20%20%20%20%22customers%22%3A%20%22customers%22%2C%0A%20%20%20%20%20%20%20%20%22employees%22%3A%20%22employees%22%2C%0A%20%20%20%20%20%20%20%20%22employeeterritories%22%3A%20%22employeeterritories%22%2C%0A%20%20%20%20%20%20%20%20%22Order%20Details%22%3A%20%22order-details%22%2C%0A%20%20%20%20%20%20%20%20%22orders%22%3A%20%22orders%22%2C%0A%20%20%20%20%20%20%20%20%22products%22%3A%20%22products%22%2C%0A%20%20%20%20%20%20%20%20%22regions%22%3A%20%22regions%22%2C%0A%20%20%20%20%20%20%20%20%22shippers%22%3A%20%22shippers%22%2C%0A%20%20%20%20%20%20%20%20%22suppliers%22%3A%20%22suppliers%22%2C%0A%20%20%20%20%20%20%20%20%22territories%22%3A%20%22territories%22%2C%0A%20%20%20%20%7D%0A%20%20%20%20for%20_table%2C%20_file%20in%20_files.items()%3A%0A%20%20%20%20%20%20%20%20con.execute(%0A%20%20%20%20%20%20%20%20%20%20%20%20f%22%22%22CREATE%20TABLE%20%22%7B_table%7D%22%20AS%20SELECT%20*%20FROM%20'%7B_source%7D%2F%7B_file%7D.parquet'%22%22%22%0A%20%20%20%20%20%20%20%20)%0A%20%20%20%20return%20(con%2C)%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20---%0A%20%20%20%20%23%23%2017.%20%60INNER%20JOIN%60%0A%0A%20%20%20%20A%20join%20takes%20two%20tables%20and%20one%20condition%20and%20returns%20the%20rows%20where%20the%20condition%20is%0A%20%20%20%20true.%20%60INNER%20JOIN%60%20is%20the%20plain%20version.%20A%20row%20comes%20out%20only%20when%20it%20found%20a%20partner%0A%20%20%20%20on%20the%20other%20side.%0A%0A%20%20%20%20%60%22Order%20Details%22%60%20holds%20a%20%60ProductID%60%20and%20no%20product%20name.%20%60products%60%20holds%20the%20name%0A%20%20%20%20and%20knows%20nothing%20about%20orders.%20The%20question%20%22what%20was%20on%20order%2010248%22%20needs%20both%0A%20%20%20%20tables.%0A%0A%20%20%20%20%F0%9F%93%96%20**Used%20in%3A**%20Combining%20tables%20with%20%60JOIN%60%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20Joining%20on%20a%20condition%0A%0A%20%20%20%20Order%2010248%20has%20three%20lines.%20Each%20line%20names%20a%20%60ProductID%60%2C%20and%20the%20name%20behind%20that%0A%20%20%20%20id%20lives%20in%20%60products%60.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20d.%22OrderID%22%2C%20p.%22ProductName%22%2C%20d.%22Quantity%22%2C%20d.%22UnitPrice%22%0A%20%20%20%20%20%20%20%20FROM%20%22Order%20Details%22%20AS%20d%0A%20%20%20%20%20%20%20%20INNER%20JOIN%20products%20AS%20p%20ON%20d.%22ProductID%22%20%3D%20p.%22ProductID%22%0A%20%20%20%20%20%20%20%20WHERE%20d.%22OrderID%22%20%3D%2010248%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%60FROM%60%20names%20the%20left%20table%2C%20%60INNER%20JOIN%60%20names%20the%20right%20one%2C%20and%20%60ON%60%20gives%20the%0A%20%20%20%20condition.%20Three%20lines%20go%20in%20and%20three%20rows%20come%20out%2C%20each%20one%20carrying%20columns%20from%0A%20%20%20%20both%20tables.%0A%0A%20%20%20%20The%20word%20%60INNER%60%20is%20optional%20in%20every%20engine%20this%20course%20touches.%20%60JOIN%60%20on%20its%20own%0A%20%20%20%20means%20the%20same%20thing%2C%20and%20you%20will%20read%20far%20more%20code%20that%20writes%20it%20that%20way.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20run%20the%20same%20query%20for%20order%2010249%20and%20count%20the%20rows.%20Then%20swap%20the%0A%20%20%20%20two%20tables%2C%20putting%20%60products%60%20after%20%60FROM%60%20and%20%60%22Order%20Details%22%60%20after%20%60JOIN%60%2C%20and%0A%20%20%20%20confirm%20you%20get%20the%20same%20rows.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20Every%20column%20name%20is%20qualified%0A%0A%20%20%20%20%60d%60%20and%20%60p%60%20are%20aliases%2C%20and%20once%20a%20table%20has%20one%2C%20every%20column%20of%20that%20table%20is%0A%20%20%20%20written%20%60alias.%22Column%22%60.%20This%20is%20not%20decoration.%20Both%20tables%20in%20the%20query%20below%20have%0A%20%20%20%20a%20column%20called%20%60OrderID%60%2C%20and%20asking%20for%20it%20without%20specifying%20which%20one%20is%20an%20error.%0A%0A%20%20%20%20%60%60%60sql%0A%20%20%20%20SELECT%20%22OrderID%22%0A%20%20%20%20FROM%20orders%20AS%20o%0A%20%20%20%20JOIN%20%22Order%20Details%22%20AS%20d%20ON%20o.%22OrderID%22%20%3D%20d.%22OrderID%22%0A%20%20%20%20%60%60%60%0A%0A%20%20%20%20%60%60%60%0A%20%20%20%20Binder%20Error%3A%20Ambiguous%20reference%20to%20column%20name%20%22OrderID%22%0A%20%20%20%20(use%3A%20%22o.OrderID%22%20or%20%22d.OrderID%22)%0A%20%20%20%20%60%60%60%0A%0A%20%20%20%20The%20database%20refuses%20because%20two%20answers%20are%20available%20and%20it%20will%20not%20choose%20for%20you.%0A%0A%20%20%20%20**The%20trap.**%20A%20column%20that%20exists%20in%20only%20one%20of%20the%20two%20tables%20runs%20fine%20with%20no%0A%20%20%20%20prefix.%20On%20the%20day%20somebody%20adds%20a%20column%20of%20that%20name%20to%20the%20other%20table%2C%20a%20query%0A%20%20%20%20that%20worked%20for%20a%20year%20starts%20failing%2C%20and%20the%20change%20is%20nowhere%20near%20it.%20Prefix%20every%0A%20%20%20%20column%20in%20a%20join%2C%20including%20the%20ones%20that%20do%20not%20need%20it%20yet.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20run%20the%20ambiguous%20query%20above%20and%20read%20the%20message.%20Fix%20it%20with%0A%20%20%20%20%60o.%22OrderID%22%60%2C%20then%20with%20%60d.%22OrderID%22%60%2C%20and%20confirm%20the%20two%20give%20the%20same%20rows.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20The%20condition%20and%20the%20output%20are%20separate%0A%0A%20%20%20%20The%20columns%20you%20join%20on%20and%20the%20columns%20you%20select%20are%20two%20different%20decisions.%0A%20%20%20%20%60CustomerID%60%20appears%20twice%20in%20the%20%60ON%60%20line%20below%20and%20nowhere%20in%20the%20result.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20c.%22CompanyName%22%2C%20o.%22OrderID%22%2C%20round(o.%22Freight%22%2C%202)%20AS%20freight%0A%20%20%20%20%20%20%20%20FROM%20orders%20AS%20o%0A%20%20%20%20%20%20%20%20JOIN%20customers%20AS%20c%20ON%20o.%22CustomerID%22%20%3D%20c.%22CustomerID%22%0A%20%20%20%20%20%20%20%20WHERE%20o.%22OrderID%22%20BETWEEN%2010248%20AND%2010252%0A%20%20%20%20%20%20%20%20ORDER%20BY%20o.%22OrderID%22%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20There%20are%20five%20orders%2C%20five%20company%20names%2C%20and%20no%20sign%20of%20the%20key%20that%20found%20them.%0A%20%20%20%20%60VINET%60%20is%20how%20order%2010248%20reached%20Vins%20et%20alcools%20Chevalier%2C%20and%20nobody%20reading%20the%0A%20%20%20%20output%20has%20to%20know%20that.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20add%20%60o.%22CustomerID%22%60%20to%20the%20select%20list%20and%20confirm%20the%20row%20count%20does%0A%20%20%20%20not%20change.%20Then%20delete%20the%20%60ORDER%20BY%60%20and%20run%20it%20twice.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20A%20row%20with%20no%20partner%20does%20not%20come%20out%0A%0A%20%20%20%20%60territories%60%20has%2053%20rows.%20%60employeeterritories%60%20records%20which%20employee%20covers%20which%0A%20%20%20%20territory%2C%20and%20it%20has%2049%20rows%2C%20at%20most%20one%20per%20territory.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20%20%20%20%20(SELECT%20count(*)%20FROM%20territories)%20AS%20territories%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20(SELECT%20count(*)%20FROM%20employeeterritories)%20AS%20assignments%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20(SELECT%20count(*)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20FROM%20territories%20AS%20t%0A%20%20%20%20%20%20%20%20%20%20%20%20%20JOIN%20employeeterritories%20AS%20et%20ON%20t.%22TerritoryID%22%20%3D%20et.%22TerritoryID%22%0A%20%20%20%20%20%20%20%20%20%20%20%20)%20AS%20rows_from_the_join%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20There%20are%2053%20territories%20going%20in%20and%2049%20rows%20coming%20out.%20Four%20territories%20have%20nobody%0A%20%20%20%20assigned%20to%20them%2C%20an%20inner%20join%20has%20nothing%20to%20pair%20them%20with%2C%20and%20they%20leave%20the%0A%20%20%20%20result%20without%20a%20word.%0A%0A%20%20%20%20**The%20trap.**%20An%20inner%20join%20answers%20a%20question%20about%20the%20rows%20that%20matched%2C%20and%20the%0A%20%20%20%20report%20it%20feeds%20will%20look%20complete.%20Count%20the%20input%20and%20count%20the%20output%20every%20time%2C%0A%20%20%20%20and%20when%20the%20two%20differ%2C%20find%20out%20what%20fell%20out%20before%20you%20send%20the%20number%20anywhere.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20run%20each%20of%20the%20three%20counts%20on%20its%20own.%20Then%20write%20the%20join%20out%20in%0A%20%20%20%20full%20and%20add%20%60count(DISTINCT%20t.%22TerritoryID%22)%60%20to%20confirm%20that%2049%20different%20territories%0A%20%20%20%20survive%20it.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20%60%22Order%20Details%22%60%20needs%20its%20quotes%0A%0A%20%20%20%20%60%60%60sql%0A%20%20%20%20SELECT%20*%20FROM%20Order%20Details%20LIMIT%203%0A%20%20%20%20%60%60%60%0A%0A%20%20%20%20%60%60%60%0A%20%20%20%20Parser%20Error%3A%20syntax%20error%20at%20or%20near%20%22Order%22%0A%0A%20%20%20%20LINE%201%3A%20SELECT%20*%20FROM%20Order%20Details%20LIMIT%203%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%5E%0A%20%20%20%20%60%60%60%0A%0A%20%20%20%20The%20parser%20reads%20%60Order%60%20as%20the%20table%20name%20and%20then%20finds%20a%20second%20word%20where%20it%0A%20%20%20%20expected%20a%20comma%20or%20a%20clause.%20Double%20quotes%20tell%20it%20the%20space%20belongs%20to%20the%20name.%0A%0A%20%20%20%20Every%20other%20table%20in%20this%20warehouse%20is%20one%20word%20and%20would%20run%20unquoted.%20This%20course%0A%20%20%20%20quotes%20every%20identifier%20anyway%2C%20because%20the%20column%20names%20here%20are%20%60CamelCase%60%20and%0A%20%20%20%20several%20engines%20fold%20an%20unquoted%20name%20to%20lower%20case%20before%20looking%20it%20up.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20run%20the%20unquoted%20version%20and%20read%20the%20message.%20Then%20run%0A%20%20%20%20%60SELECT%20count(*)%20FROM%20%22Order%20Details%22%60%20and%20confirm%20609283.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20A%20comma%20join%20with%20no%20condition%0A%0A%20%20%20%20An%20older%20form%20lists%20the%20tables%20separated%20by%20commas%20and%20puts%20the%20join%20condition%20in%0A%20%20%20%20%60WHERE%60.%20It%20is%20valid%20SQL%2C%20it%20predates%20%60JOIN%20...%20ON%60%2C%20and%20DuckDB%20accepts%20it.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20%20%20%20%20(SELECT%20count(*)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20FROM%20orders%20AS%20o%2C%20%22Order%20Details%22%20AS%20d%0A%20%20%20%20%20%20%20%20%20%20%20%20%20WHERE%20o.%22OrderID%22%20%3D%20d.%22OrderID%22%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20AND%20o.%22OrderID%22%20BETWEEN%2010248%20AND%2010252%0A%20%20%20%20%20%20%20%20%20%20%20%20)%20AS%20condition_present%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20(SELECT%20count(*)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20FROM%20orders%20AS%20o%2C%20%22Order%20Details%22%20AS%20d%0A%20%20%20%20%20%20%20%20%20%20%20%20%20WHERE%20o.%22OrderID%22%20BETWEEN%2010248%20AND%2010252%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20AND%20d.%22OrderID%22%20BETWEEN%2010248%20AND%2010252%0A%20%20%20%20%20%20%20%20%20%20%20%20)%20AS%20condition_forgotten%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20**The%20trap.**%20Leaving%20the%20join%20condition%20out%20of%20a%20comma%20join%20is%20legal.%20Every%20row%20on%0A%20%20%20%20the%20left%20is%20paired%20with%20every%20row%20on%20the%20right%2C%20five%20times%2014%20is%2070%2C%20and%20nothing%20warns%0A%20%20%20%20you.%20That%20result%20is%20called%20a%20cross%20join%2C%20or%20a%20**Cartesian%20product**%2C%20and%20here%20it%0A%20%20%20%20arrived%20by%20accident.%0A%0A%20%20%20%20Write%20%60JOIN%20...%20ON%60%20and%20the%20condition%20has%20a%20slot%20of%20its%20own%2C%20and%20leaving%20it%20out%20is%20a%0A%20%20%20%20syntax%20error.%0A%0A%20%20%20%20%60%60%60sql%0A%20%20%20%20SELECT%20*%20FROM%20orders%20AS%20o%20JOIN%20%22Order%20Details%22%20AS%20d%20LIMIT%203%0A%20%20%20%20%60%60%60%0A%0A%20%20%20%20%60%60%60%0A%20%20%20%20Parser%20Error%3A%20syntax%20error%20at%20or%20near%20%22LIMIT%22%0A%0A%20%20%20%20LINE%201%3A%20SELECT%20*%20FROM%20orders%20AS%20o%20JOIN%20%22Order%20Details%22%20AS%20d%20LIMIT%203%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%5E%0A%20%20%20%20%60%60%60%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20run%20both%20comma%20versions%20and%20compare%2014%20with%2070.%20Then%20write%20the%20second%0A%20%20%20%20one%20with%20%60JOIN%60%20and%20no%20%60ON%60%20clause%20and%20read%20the%20error%20you%20get.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20%E2%9C%94%20Check%20your%20understanding%0A%0A%20%20%20%201.%20Your%20join%20returns%20200%20rows.%20What%20two%20numbers%20do%20you%20need%20before%20you%20can%20judge%20whether%0A%20%20%20%20%20%20%20that%20is%20the%20right%20answer%3F%0A%20%20%20%202.%20Two%20tables%20in%20your%20query%20both%20have%20a%20%60CustomerID%60%20column%20and%20you%20asked%20for%0A%20%20%20%20%20%20%20%60%22CustomerID%22%60%20with%20no%20prefix.%20What%20does%20the%20database%20do%2C%20and%20what%20would%20be%20worse%0A%20%20%20%20%20%20%20about%20it%20guessing%3F%0A%20%20%20%203.%20You%20join%20%60orders%60%20to%20%60customers%60%20and%20get%20fewer%20rows%20than%20%60orders%60%20has.%20What%20has%20to%0A%20%20%20%20%20%20%20be%20true%20of%20the%20data%20for%20that%20to%20happen%3F%0A%20%20%20%204.%20A%20colleague%20sends%20you%20a%20query%20with%20two%20table%20names%20separated%20by%20a%20comma%20and%20a%0A%20%20%20%20%20%20%20%60WHERE%60%20clause%20holding%20three%20conditions.%20What%20is%20the%20first%20thing%20you%20look%20for%3F%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20---%0A%20%20%20%20%23%23%2018.%20%60LEFT%20JOIN%60%2C%20and%20what%20it%20preserves%0A%0A%20%20%20%20An%20inner%20join%20reports%20on%20rows%20that%20matched.%20A%20%60LEFT%20JOIN%60%20keeps%20every%20row%20of%20the%20left%0A%20%20%20%20table%20whether%20or%20not%20it%20found%20a%20partner%2C%20and%20fills%20the%20right%20side%20with%20%60NULL%60%20where%0A%20%20%20%20there%20was%20none.%20Its%20full%20name%20is%20**left%20outer%20join**%2C%20and%20%60OUTER%60%20is%20optional%20the%20way%0A%20%20%20%20%60INNER%60%20is.%20The%20four%20territories%20section%2017%20silently%20dropped%20are%20the%20reason%20this%0A%20%20%20%20exists.%0A%0A%20%20%20%20%F0%9F%93%96%20**Used%20in%3A**%20Combining%20tables%20with%20%60JOIN%60%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20Every%20row%20of%20the%20left%20table%20survives%0A%0A%20%20%20%20The%20query%20below%20is%20the%20previous%20one%20with%20%60JOIN%60%20changed%20to%20%60LEFT%20JOIN%60.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20%20%20%20%20count(*)%20AS%20rows_out%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20count(DISTINCT%20t.%22TerritoryID%22)%20AS%20territories_seen%0A%20%20%20%20%20%20%20%20FROM%20territories%20AS%20t%0A%20%20%20%20%20%20%20%20LEFT%20JOIN%20employeeterritories%20AS%20et%20ON%20t.%22TerritoryID%22%20%3D%20et.%22TerritoryID%22%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20There%20are%2053%20territories%20going%20in%20and%2053%20rows%20coming%20out%2C%20covering%20all%2053.%20The%20word%0A%20%20%20%20%60LEFT%60%20names%20the%20table%20that%20is%20protected%2C%20which%20is%20the%20one%20written%20first.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20change%20%60LEFT%20JOIN%60%20back%20to%20%60JOIN%60%20and%20confirm%20the%20same%20query%20gives%2049.%0A%20%20%20%20Then%20change%20it%20to%20%60RIGHT%20JOIN%60%20and%20state%2C%20before%20you%20run%20it%2C%20which%20table%20is%20protected%0A%20%20%20%20now%20and%20what%20number%20you%20expect.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20The%20unmatched%20side%20arrives%20as%20%60NULL%60%0A%0A%20%20%20%20Region%204%20has%20eight%20territories%20and%20Janet%20Leverling%20covers%20four%20of%20them.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20t.%22TerritoryID%22%2C%20t.%22TerritoryDescription%22%2C%20et.%22EmployeeID%22%0A%20%20%20%20%20%20%20%20FROM%20territories%20AS%20t%0A%20%20%20%20%20%20%20%20LEFT%20JOIN%20employeeterritories%20AS%20et%20ON%20t.%22TerritoryID%22%20%3D%20et.%22TerritoryID%22%0A%20%20%20%20%20%20%20%20WHERE%20t.%22RegionID%22%20%3D%204%0A%20%20%20%20%20%20%20%20ORDER%20BY%20t.%22TerritoryID%22%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20Atlanta%2C%20Savannah%2C%20Orlando%20and%20Tampa%20carry%20employee%203.%20Columbia%2C%20Bentonville%2C%20Dallas%0A%20%20%20%20and%20Austin%20carry%20%60NULL%60%2C%20and%20every%20column%20that%20would%20have%20come%20from%0A%20%20%20%20%60employeeterritories%60%20is%20%60NULL%60%20on%20those%20rows.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20add%20%60et.%22TerritoryID%22%60%20to%20the%20select%20list%20and%20look%20at%20what%20it%20holds%20on%0A%20%20%20%20the%20Bentonville%20row.%20That%20column%20appears%20in%20the%20%60ON%60%20clause%2C%20so%20it%20looks%20like%20it%20has%0A%20%20%20%20to%20have%20a%20value.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20Finding%20what%20has%20no%20match%20at%20all%0A%0A%20%20%20%20Keep%20every%20left%20row%2C%20then%20keep%20only%20the%20ones%20whose%20right%20side%20came%20back%20empty.%20The%0A%20%20%20%20result%20is%20everything%20with%20no%20partner.%20That%20shape%20is%20called%20an%20anti-join.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20t.%22TerritoryID%22%2C%20t.%22TerritoryDescription%22%2C%20t.%22RegionID%22%0A%20%20%20%20%20%20%20%20FROM%20territories%20AS%20t%0A%20%20%20%20%20%20%20%20LEFT%20JOIN%20employeeterritories%20AS%20et%20ON%20t.%22TerritoryID%22%20%3D%20et.%22TerritoryID%22%0A%20%20%20%20%20%20%20%20WHERE%20et.%22TerritoryID%22%20IS%20NULL%0A%20%20%20%20%20%20%20%20ORDER%20BY%20t.%22TerritoryID%22%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20Four%20territories%20come%20back%2C%20all%20of%20them%20in%20region%204%2C%20and%20nobody%20is%20assigned%20to%20any%20of%0A%20%20%20%20them.%20That%20is%20a%20business%20fact%20the%20inner%20join%20could%20not%20have%20shown%20you.%0A%0A%20%20%20%20Test%20a%20column%20from%20the%20right%20table%2C%20and%20pick%20one%20that%20can%20never%20be%20%60NULL%60%20in%20its%20own%0A%20%20%20%20right.%20The%20key%20you%20joined%20on%20is%20the%20safe%20choice.%20Testing%20a%20nullable%20column%20such%20as%0A%20%20%20%20%60et.%22EmployeeID%22%60%20would%20also%20catch%20rows%20that%20matched%20and%20happened%20to%20have%20no%20value%0A%20%20%20%20there.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20write%20the%20anti-join%20the%20other%20way%20around%2C%20from%20%60employeeterritories%60%0A%20%20%20%20out%20to%20%60territories%60%2C%20looking%20for%20assignments%20that%20point%20at%20a%20territory%20that%20does%20not%0A%20%20%20%20exist.%20State%20what%20a%20nonzero%20answer%20would%20mean%20before%20you%20run%20it.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20%60count(*)%60%20counts%20a%20row%20that%20matched%20nothing%0A%0A%20%20%20%20A%20row%20that%20found%20no%20partner%20is%20still%20a%20row.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20%20%20%20%20t.%22TerritoryID%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20t.%22TerritoryDescription%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20count(*)%20AS%20with_star%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20count(et.%22EmployeeID%22)%20AS%20with_column%0A%20%20%20%20%20%20%20%20FROM%20territories%20AS%20t%0A%20%20%20%20%20%20%20%20LEFT%20JOIN%20employeeterritories%20AS%20et%20ON%20t.%22TerritoryID%22%20%3D%20et.%22TerritoryID%22%0A%20%20%20%20%20%20%20%20WHERE%20t.%22RegionID%22%20%3D%204%0A%20%20%20%20%20%20%20%20GROUP%20BY%20t.%22TerritoryID%22%2C%20t.%22TerritoryDescription%22%0A%20%20%20%20%20%20%20%20ORDER%20BY%20t.%22TerritoryID%22%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20Bentonville%20has%20nobody%20assigned%2C%20and%20%60count(*)%60%20reports%20one%20assignment%20for%20it.%0A%20%20%20%20%60count(*)%60%20counts%20rows%2C%20and%20the%20%60LEFT%20JOIN%60%20produced%20a%20row.%20%60count(et.%22EmployeeID%22)%60%0A%20%20%20%20counts%20values%2C%20%60NULL%60%20is%20not%20a%20value%2C%20and%20it%20reports%20zero.%0A%0A%20%20%20%20**The%20trap.**%20%22How%20many%20employees%20cover%20each%20territory%22%20written%20with%20%60count(*)%60%20on%20a%0A%20%20%20%20%60LEFT%20JOIN%60%20gives%20every%20empty%20territory%20a%20coverage%20of%20one.%20The%20number%20is%20plausible%2C%0A%20%20%20%20the%20query%20runs%2C%20and%20the%20four%20towns%20with%20no%20salesperson%20disappear%20into%20the%20average.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20run%20both%20counts%20over%20all%2053%20territories%20with%20no%20%60GROUP%20BY%60%20and%20no%0A%20%20%20%20%60WHERE%60%2C%20and%20check%20the%20two%20totals%20against%2053%20and%2049.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20A%20right-table%20filter%20in%20%60WHERE%60%20undoes%20the%20%60LEFT%60%0A%0A%20%20%20%20Both%20queries%20below%20are%20%60LEFT%20JOIN%60s%20asking%20about%20employee%203.%20One%20puts%20the%20condition%20in%0A%20%20%20%20%60WHERE%60%20and%20the%20other%20puts%20it%20in%20%60ON%60.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20%20%20%20%20(SELECT%20count(*)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20FROM%20territories%20AS%20t%0A%20%20%20%20%20%20%20%20%20%20%20%20%20LEFT%20JOIN%20employeeterritories%20AS%20et%20ON%20t.%22TerritoryID%22%20%3D%20et.%22TerritoryID%22%0A%20%20%20%20%20%20%20%20%20%20%20%20%20WHERE%20et.%22EmployeeID%22%20%3D%203%0A%20%20%20%20%20%20%20%20%20%20%20%20)%20AS%20filtered_in_where%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20(SELECT%20count(*)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20FROM%20territories%20AS%20t%0A%20%20%20%20%20%20%20%20%20%20%20%20%20LEFT%20JOIN%20employeeterritories%20AS%20et%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20ON%20t.%22TerritoryID%22%20%3D%20et.%22TerritoryID%22%20AND%20et.%22EmployeeID%22%20%3D%203%0A%20%20%20%20%20%20%20%20%20%20%20%20)%20AS%20filtered_in_on%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20The%20%60ON%60%20clause%20decides%20which%20right%20rows%20are%20allowed%20to%20match%2C%20and%20the%20left%20row%0A%20%20%20%20survives%20either%20way%2C%20so%20the%20second%20query%20keeps%20all%2053.%20%60WHERE%60%20runs%20after%20the%20join%20and%0A%20%20%20%20throws%20rows%20away.%20Every%20unmatched%20left%20row%20holds%20%60NULL%60%20in%20%60et.%22EmployeeID%22%60%2C%20and%0A%20%20%20%20%60NULL%20%3D%203%60%20is%20unknown%2C%20so%20all%2049%20of%20the%20protected%20rows%20vanish%20and%20four%20remain.%0A%0A%20%20%20%20**The%20trap.**%20A%20%60LEFT%20JOIN%60%20whose%20%60WHERE%60%20clause%20tests%20a%20right-table%20column%20returns%0A%20%20%20%20exactly%20what%20an%20inner%20join%20would%20return.%20Any%20condition%20on%20the%20right%20table%20belongs%20in%0A%20%20%20%20%60ON%60.%20The%20one%20exception%20is%20the%20%60IS%20NULL%60%20test%20in%20the%20anti-join%20above%2C%20which%20is%20a%0A%20%20%20%20question%20about%20the%20join%20itself.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20take%20the%20anti-join%20from%20the%20previous%20point%20and%20move%0A%20%20%20%20%60et.%22TerritoryID%22%20IS%20NULL%60%20from%20%60WHERE%60%20into%20the%20%60ON%60%20clause.%20Count%20the%20rows%20and%20explain%0A%20%20%20%20what%20the%20query%20now%20means.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20%60NULL%60%20values%20that%20print%20the%20same%0A%0A%20%20%20%20A%20%60NULL%60%20from%20a%20%60LEFT%20JOIN%60%20means%20no%20matching%20row%20existed.%20A%20%60NULL%60%20in%20a%20column%20means%0A%20%20%20%20the%20row%20exists%20and%20the%20value%20was%20never%20recorded.%20Both%20show%20as%20%60NULL%60.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20%20%20%20%20count(*)%20AS%20orders%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20count(%22ShippedDate%22)%20AS%20shipped%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20count(*)%20-%20count(%22ShippedDate%22)%20AS%20never_shipped%0A%20%20%20%20%20%20%20%20FROM%20orders%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20There%20are%2021%20orders%20with%20no%20%60ShippedDate%60.%20No%20join%20was%20involved.%20%60orders%60%20is%20one%20table%2C%0A%20%20%20%20the%20rows%20are%20there%2C%20and%20the%20value%20is%20missing%2C%20which%20is%20section%209's%20kind%20of%20%60NULL%60.%0A%0A%20%20%20%20Telling%20the%20two%20apart%20is%20a%20question%20about%20where%20you%20are%20looking.%20A%20%60NULL%60%20that%20came%0A%20%20%20%20from%20a%20join%20disappears%20when%20you%20query%20the%20right%20table%20on%20its%20own%3B%20a%20%60NULL%60%20in%20the%20row%0A%20%20%20%20is%20still%20there.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20list%20those%2021%20orders%20with%20their%20customer's%20company%20name%2C%20using%20a%20join.%0A%20%20%20%20Then%20state%20which%20kind%20of%20%60NULL%60%20you%20would%20be%20looking%20at%20if%20an%20order%20carried%20a%0A%20%20%20%20%60CustomerID%60%20that%20no%20customer%20had.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20%E2%9C%94%20Check%20your%20understanding%0A%0A%20%20%20%201.%20You%20have%20a%20%60LEFT%20JOIN%60%20and%20the%20result%20has%20the%20same%20number%20of%20rows%20as%20the%20left%0A%20%20%20%20%20%20%20table.%20What%20does%20that%20tell%20you%2C%20and%20what%20does%20it%20fail%20to%20tell%20you%3F%0A%20%20%20%202.%20A%20monthly%20report%20counts%20customers%20per%20sales%20region%20with%20%60count(*)%60%20over%20a%0A%20%20%20%20%20%20%20%60LEFT%20JOIN%60.%20What%20number%20does%20an%20empty%20region%20show%2C%20and%20what%20would%20you%20write%0A%20%20%20%20%20%20%20instead%3F%0A%20%20%20%203.%20Somebody%20moves%20a%20condition%20from%20%60ON%60%20to%20%60WHERE%60%20in%20a%20%60LEFT%20JOIN%60%20and%20the%20row%20count%0A%20%20%20%20%20%20%20drops.%20Explain%20what%20happened%20without%20using%20the%20word%20%22outer%22.%0A%20%20%20%204.%20You%20want%20every%20territory%20with%20the%20employee%20covering%20it%2C%20including%20the%20ones%20nobody%0A%20%20%20%20%20%20%20covers.%20Which%20table%20goes%20on%20the%20left%2C%20and%20what%20number%20tells%20you%20afterward%20that%20the%0A%20%20%20%20%20%20%20uncovered%20ones%20are%20in%20your%20result%3F%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20---%0A%20%20%20%20%23%23%2019.%20When%20a%20%60JOIN%60%20duplicates%20rows%0A%0A%20%20%20%20A%20join%20pairs%20rows.%20When%20one%20row%20on%20the%20left%20matches%20three%20rows%20on%20the%20right%2C%20that%20left%0A%20%20%20%20row%20comes%20out%20three%20times%2C%20and%20every%20column%20it%20carries%20comes%20out%20three%20times%20with%20it.%0A%20%20%20%20That%20is%20a%20**fan-out**.%0A%0A%20%20%20%20Nothing%20in%20the%20syntax%20mentions%20this.%20The%20query%20runs%2C%20the%20result%20still%20looks%20like%20a%0A%20%20%20%20table%20of%20orders%2C%20and%20any%20total%20taken%20from%20a%20left-table%20column%20is%20too%20big.%0A%0A%20%20%20%20%F0%9F%93%96%20**Used%20in%3A**%20Combining%20tables%20with%20%60JOIN%60%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20Count%20the%20rows%20before%20and%20after%0A%0A%20%20%20%20The%20five%20orders%2010248%20through%2010252%20are%20joined%20to%20their%20lines%20below.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20%20%20%20%20(SELECT%20count(*)%20FROM%20orders%0A%20%20%20%20%20%20%20%20%20%20%20%20%20WHERE%20%22OrderID%22%20BETWEEN%2010248%20AND%2010252%0A%20%20%20%20%20%20%20%20%20%20%20%20)%20AS%20orders_alone%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20(SELECT%20count(*)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20FROM%20orders%20AS%20o%0A%20%20%20%20%20%20%20%20%20%20%20%20%20JOIN%20%22Order%20Details%22%20AS%20d%20ON%20o.%22OrderID%22%20%3D%20d.%22OrderID%22%0A%20%20%20%20%20%20%20%20%20%20%20%20%20WHERE%20o.%22OrderID%22%20BETWEEN%2010248%20AND%2010252%0A%20%20%20%20%20%20%20%20%20%20%20%20)%20AS%20after_the_join%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20Five%20orders%20go%20in%20and%2014%20rows%20come%20out.%20The%20join%20added%20nine%20rows%20and%20no%20new%20order.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20run%20the%20join%20itself%20without%20the%20%60count(*)%60%2C%20read%20the%2014%20rows%2C%20and%20write%0A%20%20%20%20down%20how%20many%20times%20%6010248%60%20appears%20in%20the%20%60OrderID%60%20column.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20Where%20the%2014%20comes%20from%0A%0A%20%20%20%20One%20row%20of%20%60%22Order%20Details%22%60%20is%20one%20product%20on%20one%20order.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20o.%22OrderID%22%2C%20round(o.%22Freight%22%2C%202)%20AS%20freight%2C%20count(*)%20AS%20n_lines%0A%20%20%20%20%20%20%20%20FROM%20orders%20AS%20o%0A%20%20%20%20%20%20%20%20JOIN%20%22Order%20Details%22%20AS%20d%20ON%20o.%22OrderID%22%20%3D%20d.%22OrderID%22%0A%20%20%20%20%20%20%20%20WHERE%20o.%22OrderID%22%20BETWEEN%2010248%20AND%2010252%0A%20%20%20%20%20%20%20%20GROUP%20BY%20o.%22OrderID%22%2C%20o.%22Freight%22%0A%20%20%20%20%20%20%20%20ORDER%20BY%20o.%22OrderID%22%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20The%20line%20counts%20are%20three%2C%20two%2C%20three%2C%20three%20and%20three%2C%20and%20those%20add%20to%2014.%0A%0A%20%20%20%20Before%20the%20join%2C%20one%20row%20was%20one%20order.%20After%20it%2C%20one%20row%20is%20one%20order%20line%2C%20and%20the%0A%20%20%20%20order's%20own%20columns%20have%20been%20copied%20onto%20each%20of%20them.%20The%20freight%20of%2016.75%20sits%20on%0A%20%20%20%20all%20three%20rows%20of%20order%2010248.%0A%0A%20%20%20%20**The%20grain%20of%20a%20result%20is%20the%20grain%20of%20the%20finest%20table%20in%20it.**%20Grain%20is%20also%20called%0A%20%20%20%20**granularity**.%20Name%20that%20grain%20out%20loud%20before%20you%20aggregate%20anything.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20look%20at%20the%20%60freight%60%20and%20%60n_lines%60%20columns%20side%20by%20side%20and%20predict%0A%20%20%20%20what%20%60sum(o.%22Freight%22)%60%20over%20the%2014%20rows%20would%20give%20for%20order%2010248%20alone.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20The%20number%20that%20goes%20wrong%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20%20%20%20%20(SELECT%20count(*)%20FROM%20orders%0A%20%20%20%20%20%20%20%20%20%20%20%20%20WHERE%20%22OrderID%22%20BETWEEN%2010248%20AND%2010252%0A%20%20%20%20%20%20%20%20%20%20%20%20)%20AS%20rows_before%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20(SELECT%20round(sum(%22Freight%22)%2C%202)%20FROM%20orders%0A%20%20%20%20%20%20%20%20%20%20%20%20%20WHERE%20%22OrderID%22%20BETWEEN%2010248%20AND%2010252%0A%20%20%20%20%20%20%20%20%20%20%20%20)%20AS%20freight_before%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20(SELECT%20count(*)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20FROM%20orders%20AS%20o%0A%20%20%20%20%20%20%20%20%20%20%20%20%20JOIN%20%22Order%20Details%22%20AS%20d%20ON%20o.%22OrderID%22%20%3D%20d.%22OrderID%22%0A%20%20%20%20%20%20%20%20%20%20%20%20%20WHERE%20o.%22OrderID%22%20BETWEEN%2010248%20AND%2010252%0A%20%20%20%20%20%20%20%20%20%20%20%20)%20AS%20rows_after%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20(SELECT%20round(sum(o.%22Freight%22)%2C%202)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20FROM%20orders%20AS%20o%0A%20%20%20%20%20%20%20%20%20%20%20%20%20JOIN%20%22Order%20Details%22%20AS%20d%20ON%20o.%22OrderID%22%20%3D%20d.%22OrderID%22%0A%20%20%20%20%20%20%20%20%20%20%20%20%20WHERE%20o.%22OrderID%22%20BETWEEN%2010248%20AND%2010252%0A%20%20%20%20%20%20%20%20%20%20%20%20)%20AS%20freight_after%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20The%20five%20orders%20carry%20120.50%20in%20freight.%20Joined%20to%20their%20lines%2C%20the%20same%20sum%20reports%0A%20%20%20%20339.25.%0A%0A%20%20%20%20Check%20it%20by%20hand.%20Multiply%20each%20order's%20freight%20by%20its%20line%20count%20and%20add%20the%20five%0A%20%20%20%20products%20together.%0A%0A%20%20%20%20%60%60%60%0A%20%20%20%2016.75%20x%203%20%20%3D%20%2050.25%0A%20%20%20%2022.25%20x%202%20%20%3D%20%2044.50%0A%20%20%20%2025.00%20x%203%20%20%3D%20%2075.00%0A%20%20%20%2020.25%20x%203%20%20%3D%20%2060.75%0A%20%20%20%2036.25%20x%203%20%20%3D%20108.75%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20------%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20339.25%0A%20%20%20%20%60%60%60%0A%0A%20%20%20%20The%20result%20is%20about%202.8%20times%20the%20real%20figure.%20Nothing%20about%20it%20looks%20broken.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20do%20that%20arithmetic%20yourself%20and%20confirm%20339.25.%20Then%20drop%2010249%2C%20the%0A%20%20%20%20order%20with%20two%20lines%2C%20out%20of%20the%20range%20and%20predict%20both%20totals%20before%20you%20rerun.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20The%20same%20mistake%20on%20the%20whole%20warehouse%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20%20%20%20%20(SELECT%20round(sum(%22Freight%22)%2C%202)%20FROM%20orders)%20AS%20freight_in_orders%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20(SELECT%20round(sum(o.%22Freight%22)%2C%202)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20FROM%20orders%20AS%20o%0A%20%20%20%20%20%20%20%20%20%20%20%20%20JOIN%20%22Order%20Details%22%20AS%20d%20ON%20o.%22OrderID%22%20%3D%20d.%22OrderID%22%0A%20%20%20%20%20%20%20%20%20%20%20%20)%20AS%20freight_after_join%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20Total%20freight%20in%20%60orders%60%20is%204047470.50.%20The%20joined%20version%20says%20206911676.00%2C%20about%0A%20%20%20%2051%20times%20as%20large.%20The%20average%20order%20here%20carries%2037%20lines%2C%20and%20the%20factor%20lands%20above%0A%20%20%20%20that%20because%20the%20orders%20with%20the%20most%20freight%20also%20carry%20the%20most%20lines.%0A%0A%20%20%20%20A%20number%20that%20large%20is%20loud%20enough%20to%20catch.%20The%20five-order%20version%20is%20not%2C%20and%0A%20%20%20%20neither%20is%20the%20same%20error%20run%20over%20one%20quarter%20or%20one%20sales%20region%2C%20which%20produces%20a%0A%20%20%20%20figure%20somebody%20will%20put%20on%20a%20slide.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20run%20both%20totals%20restricted%20to%20orders%20placed%20in%202016%2C%20using%0A%20%20%20%20%60WHERE%20%22OrderDate%22%20%3E%3D%20DATE%20'2016-01-01'%20AND%20%22OrderDate%22%20%3C%20DATE%20'2017-01-01'%60%2C%20and%20work%0A%20%20%20%20out%20the%20ratio%20between%20them.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20Which%20columns%20survive%20a%20fan-out%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20%20%20%20%20(SELECT%20sum(%22Quantity%22)%20FROM%20%22Order%20Details%22%0A%20%20%20%20%20%20%20%20%20%20%20%20%20WHERE%20%22OrderID%22%20BETWEEN%2010248%20AND%2010252%0A%20%20%20%20%20%20%20%20%20%20%20%20)%20AS%20units_from_details%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20(SELECT%20sum(d.%22Quantity%22)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20FROM%20orders%20AS%20o%0A%20%20%20%20%20%20%20%20%20%20%20%20%20JOIN%20%22Order%20Details%22%20AS%20d%20ON%20o.%22OrderID%22%20%3D%20d.%22OrderID%22%0A%20%20%20%20%20%20%20%20%20%20%20%20%20WHERE%20o.%22OrderID%22%20BETWEEN%2010248%20AND%2010252%0A%20%20%20%20%20%20%20%20%20%20%20%20)%20AS%20units_after_join%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20Both%20say%20282.%20%60Quantity%60%20lives%20on%20%60%22Order%20Details%22%60%2C%20which%20is%20the%20table%20the%20joined%0A%20%20%20%20result%20is%20at%20the%20grain%20of%2C%20so%20each%20value%20appears%20exactly%20once%20and%20the%20total%20is%0A%20%20%20%20untouched.%20%60Freight%60%20lives%20on%20%60orders%60%2C%20one%20level%20up%2C%20and%20gets%20copied.%0A%0A%20%20%20%20So%20the%20rule%20is%20about%20where%20the%20column%20lives.%20A%20column%20from%20the%20many%20side%20is%20safe%20to%0A%20%20%20%20add%20up%20after%20the%20join.%20A%20column%20from%20the%20one%20side%20is%20not.%0A%0A%20%20%20%20So%20a%20query%20that%20joins%20order%20lines%20to%20products%20and%20sums%20a%20quantity%20is%20correct%2C%20because%0A%20%20%20%20quantity%20is%20on%20the%20many%20side.%20Copy%20that%20pattern%2C%20swap%20in%20an%20order-level%20column%2C%20and%0A%20%20%20%20you%20get%20a%20wrong%20number%20and%20no%20warning.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20pick%20two%20more%20columns%2C%20one%20from%20each%20table%2C%20and%20predict%20for%20each%0A%20%20%20%20whether%20a%20%60sum%60%20over%20the%20join%20matches%20a%20%60sum%60%20over%20its%20own%20table.%20Then%20check%20both.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20The%20fix%20that%20passes%20the%20small%20test%0A%0A%20%20%20%20%60SUM(DISTINCT%20...)%60%20is%20the%20first%20repair%20almost%20anybody%20reaches%20for%2C%20including%20an%20agent.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20%20%20%20%20(SELECT%20round(sum(DISTINCT%20o.%22Freight%22)%2C%202)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20FROM%20orders%20AS%20o%0A%20%20%20%20%20%20%20%20%20%20%20%20%20JOIN%20%22Order%20Details%22%20AS%20d%20ON%20o.%22OrderID%22%20%3D%20d.%22OrderID%22%0A%20%20%20%20%20%20%20%20%20%20%20%20%20WHERE%20o.%22OrderID%22%20BETWEEN%2010248%20AND%2010252%0A%20%20%20%20%20%20%20%20%20%20%20%20)%20AS%20distinct_on_five_orders%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20(SELECT%20round(sum(DISTINCT%20o.%22Freight%22)%2C%202)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20FROM%20orders%20AS%20o%0A%20%20%20%20%20%20%20%20%20%20%20%20%20JOIN%20%22Order%20Details%22%20AS%20d%20ON%20o.%22OrderID%22%20%3D%20d.%22OrderID%22%0A%20%20%20%20%20%20%20%20%20%20%20%20)%20AS%20distinct_on_everything%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20(SELECT%20round(sum(%22Freight%22)%2C%202)%20FROM%20orders)%20AS%20the_real_total%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20On%20the%20five%20orders%20it%20returns%20120.50%2C%20which%20is%20right.%20Across%20the%20warehouse%20it%20returns%0A%20%20%20%20594656.00%20against%20a%20true%204047470.50.%0A%0A%20%20%20%20%60SUM(DISTINCT%20...)%60%20removes%20duplicate%20**values**%2C%20and%20the%20join%20duplicated%20**rows**.%0A%20%20%20%20The%20five%20orders%20happen%20to%20carry%20five%20different%20freight%20amounts%2C%20so%20the%20two%20ideas%20agree.%0A%20%20%20%20Across%2016282%20orders%20there%20are%20only%202140%20different%20freight%20amounts%2C%20so%20every%20order%0A%20%20%20%20sharing%20an%20amount%20with%20another%20one%20is%20thrown%20away%20along%20with%20the%20copies.%0A%0A%20%20%20%20**The%20trap.**%20The%20test%20that%20would%20have%20caught%20this%20is%20the%20one%20already%20run%2C%20and%20it%0A%20%20%20%20passed.%20Check%20a%20repair%20against%20a%20range%20wide%20enough%20to%20hold%20a%20repeated%20value%2C%20and%0A%20%20%20%20against%20the%20total%20you%20are%20trying%20to%20reproduce.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20find%20two%20orders%20with%20the%20same%20freight%0A%20%20%20%20(%60GROUP%20BY%20%22Freight%22%20HAVING%20count(*)%20%3E%201%60)%2C%20add%20them%20to%20a%20range%2C%20and%20watch%0A%20%20%20%20%60sum(DISTINCT%20...)%60%20lose%20one%20of%20them.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20The%20fix%20that%20works%0A%0A%20%20%20%20Collapse%20the%20many%20side%20to%20one%20row%20per%20order%20first.%20Then%20join.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20o.%22OrderID%22%2C%20round(o.%22Freight%22%2C%202)%20AS%20freight%2C%20lines.n_lines%2C%20lines.units%0A%20%20%20%20%20%20%20%20FROM%20orders%20AS%20o%0A%20%20%20%20%20%20%20%20JOIN%20(%0A%20%20%20%20%20%20%20%20%20%20%20%20SELECT%20%22OrderID%22%2C%20count(*)%20AS%20n_lines%2C%20sum(%22Quantity%22)%20AS%20units%0A%20%20%20%20%20%20%20%20%20%20%20%20FROM%20%22Order%20Details%22%0A%20%20%20%20%20%20%20%20%20%20%20%20GROUP%20BY%20%22OrderID%22%0A%20%20%20%20%20%20%20%20)%20AS%20lines%20ON%20o.%22OrderID%22%20%3D%20lines.%22OrderID%22%0A%20%20%20%20%20%20%20%20WHERE%20o.%22OrderID%22%20BETWEEN%2010248%20AND%2010252%0A%20%20%20%20%20%20%20%20ORDER%20BY%20o.%22OrderID%22%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20count(*)%20AS%20rows_out%2C%20round(sum(o.%22Freight%22)%2C%202)%20AS%20total_freight%0A%20%20%20%20%20%20%20%20FROM%20orders%20AS%20o%0A%20%20%20%20%20%20%20%20JOIN%20(%0A%20%20%20%20%20%20%20%20%20%20%20%20SELECT%20%22OrderID%22%2C%20count(*)%20AS%20n_lines%0A%20%20%20%20%20%20%20%20%20%20%20%20FROM%20%22Order%20Details%22%0A%20%20%20%20%20%20%20%20%20%20%20%20GROUP%20BY%20%22OrderID%22%0A%20%20%20%20%20%20%20%20)%20AS%20lines%20ON%20o.%22OrderID%22%20%3D%20lines.%22OrderID%22%0A%20%20%20%20%20%20%20%20WHERE%20o.%22OrderID%22%20BETWEEN%2010248%20AND%2010252%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20The%20result%20is%20five%20rows%20and%20120.50.%20The%20right%20side%20now%20holds%20one%20row%20per%20%60OrderID%60%2C%20so%0A%20%20%20%20no%20order%20can%20be%20duplicated%2C%20and%20the%20line%20counts%20and%20unit%20totals%20are%20still%20there.%0A%0A%20%20%20%20That%20parenthesized%20query%20is%20a%20subquery%20in%20%60FROM%60%2C%20which%20is%20section%2021.%20Section%2022%0A%20%20%20%20writes%20the%20same%20thing%20with%20a%20name%20in%20front%20of%20it%2C%20and%20that%20is%20the%20form%20you%20will%0A%20%20%20%20actually%20use.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20change%20the%20inner%20query's%20%60GROUP%20BY%60%20to%20%60%22OrderID%22%2C%20%22ProductID%22%60%20and%0A%20%20%20%20rerun%20both%20cells.%20Count%20the%20rows%20and%20explain%20which%20number%20moved%20and%20why.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20%E2%9C%94%20Check%20your%20understanding%0A%0A%20%20%20%201.%20A%20join%20between%20two%20tables%20returns%20more%20rows%20than%20either%20table%20holds.%20What%20do%20you%0A%20%20%20%20%20%20%20now%20know%20about%20the%20data%3F%0A%20%20%20%202.%20You%20joined%20orders%20to%20order%20lines%20and%20summed%20a%20column.%20What%20single%20question%20decides%0A%20%20%20%20%20%20%20whether%20that%20total%20is%20right%3F%0A%20%20%20%203.%20Your%20revenue%20figure%20is%202.8%20times%20what%20finance%20reports.%20Name%20two%20checks%20you%20would%0A%20%20%20%20%20%20%20run%20before%20reporting%20the%20figure%20to%20anybody.%0A%20%20%20%204.%20Somebody%20hands%20you%20a%20query%20with%20%60SUM(DISTINCT)%60%20in%20it.%20What%20do%20you%20ask%20them%3F%0A%20%20%20%205.%20A%20colleague%20argues%20the%20join%20is%20fine%20because%20the%20row%20count%20went%20from%20five%20to%2014%20and%0A%20%20%20%20%20%20%2014%20is%20not%20a%20multiple%20of%20five.%20What%20is%20wrong%20with%20that%20argument%3F%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20---%0A%20%20%20%20%23%23%2020.%20Self%20joins%20and%20multi-table%20joins%0A%0A%20%20%20%20%3E%20**Advanced**%0A%0A%20%20%20%20Nothing%20in%20this%20course%20requires%20this%20section.%0A%0A%20%20%20%20A%20self%20join%20reads%20a%20relationship%20that%20is%20stored%20inside%20one%20table.%20A%20multi-table%20join%0A%20%20%20%20follows%20keys%20through%20three%20or%20four%20tables%20in%20a%20single%20statement.%20Both%20are%20ordinary%0A%20%20%20%20joins%2C%20and%20both%20make%20section%2019's%20row%20multiplication%20easy%20to%20walk%20into.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20A%20table%20joined%20to%20itself%0A%0A%20%20%20%20%60employees%60%20has%20a%20column%20%60ReportsTo%60%20holding%20another%20employee's%20%60EmployeeID%60.%20To%20print%0A%20%20%20%20a%20manager's%20name%20beside%20an%20employee's%20name%2C%20the%20table%20has%20to%20appear%20twice%2C%20and%20the%0A%20%20%20%20aliases%20are%20the%20only%20thing%20keeping%20the%20two%20copies%20apart.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20%20%20%20%20e.%22EmployeeID%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20e.%22FirstName%22%20%7C%7C%20'%20'%20%7C%7C%20e.%22LastName%22%20AS%20employee%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20e.%22ReportsTo%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20m.%22FirstName%22%20%7C%7C%20'%20'%20%7C%7C%20m.%22LastName%22%20AS%20manager%0A%20%20%20%20%20%20%20%20FROM%20employees%20AS%20e%0A%20%20%20%20%20%20%20%20LEFT%20JOIN%20employees%20AS%20m%20ON%20e.%22ReportsTo%22%20%3D%20m.%22EmployeeID%22%0A%20%20%20%20%20%20%20%20ORDER%20BY%20e.%22EmployeeID%22%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20Nine%20people%20come%20back%2C%20eight%20of%20them%20with%20a%20named%20manager%2C%20and%20Andrew%20Fuller%20carries%0A%20%20%20%20%60NULL%60%20because%20nobody%20is%20above%20him.%20Two%20things%20check%20this%20query.%20The%20count%20matches%20the%0A%20%20%20%20table%2C%20and%20exactly%20one%20row%20has%20no%20manager.%0A%0A%20%20%20%20The%20join%20has%20to%20be%20a%20%60LEFT%20JOIN%60%20for%20the%20boss%20to%20appear%20at%20all.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20count%20the%20rows%20with%20%60LEFT%20JOIN%60%20and%20again%20with%20%60JOIN%60%2C%20and%20state%20which%0A%20%20%20%20person%20you%20lose.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20The%20condition%20has%20to%20name%20the%20key%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20%20%20%20%20(SELECT%20count(*)%20FROM%20employees)%20AS%20employees%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20(SELECT%20count(*)%20FROM%20employees%20AS%20e%0A%20%20%20%20%20%20%20%20%20%20%20%20%20JOIN%20employees%20AS%20m%20ON%20e.%22ReportsTo%22%20%3D%20m.%22EmployeeID%22%0A%20%20%20%20%20%20%20%20%20%20%20%20)%20AS%20inner_join%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20(SELECT%20count(*)%20FROM%20employees%20AS%20e%0A%20%20%20%20%20%20%20%20%20%20%20%20%20LEFT%20JOIN%20employees%20AS%20m%20ON%20e.%22ReportsTo%22%20%3D%20m.%22EmployeeID%22%0A%20%20%20%20%20%20%20%20%20%20%20%20)%20AS%20left_join%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20(SELECT%20count(*)%20FROM%20employees%20AS%20e%0A%20%20%20%20%20%20%20%20%20%20%20%20%20JOIN%20employees%20AS%20m%20ON%20e.%22ReportsTo%22%20%3D%20m.%22ReportsTo%22%0A%20%20%20%20%20%20%20%20%20%20%20%20)%20AS%20joined_on_the_wrong_column%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20The%20last%20one%20runs%2C%20raises%20nothing%2C%20and%20returns%2034%20rows%20from%20a%20table%20of%20nine.%20Joining%20on%0A%20%20%20%20%60ReportsTo%20%3D%20ReportsTo%60%20pairs%20every%20employee%20with%20every%20colleague%20who%20shares%20a%20manager%2C%0A%20%20%20%20including%20themselves.%0A%0A%20%20%20%20**The%20trap.**%20A%20self%20join%20whose%20%60ON%60%20clause%20names%20something%20other%20than%20the%20key%20is%20a%0A%20%20%20%20fan-out.%20Joining%20employees%20to%20employees%0A%20%20%20%20%60ON%20e.OrganizationLevel%20%3D%20m.OrganizationLevel%20%2B%201%60%20looks%20like%20a%20way%20to%20walk%20a%0A%20%20%20%20hierarchy%2C%20and%20it%20pairs%20every%20employee%20with%20every%20employee%20one%20level%20above%2C%20so%20a%0A%20%20%20%20company%20with%2020%20managers%20on%20a%20level%20gives%20each%20of%20their%20reports%2020%20managers.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20run%20the%20wrong%20version%20in%20full%2C%20without%20%60count(*)%60%2C%20and%20find%20the%20rows%0A%20%20%20%20where%20an%20employee%20is%20their%20own%20colleague.%20Then%20add%20%60AND%20e.%22EmployeeID%22%20%3C%3E%20m.%22EmployeeID%22%60%0A%20%20%20%20and%20count%20again.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20Adding%20tables%20a%20%60JOIN%60%20at%20a%20time%0A%0A%20%20%20%20Every%20%60JOIN%60%20after%20the%20first%20brings%20a%20table%20and%20an%20%60ON%60%20clause%20with%20it.%20The%20query%20below%0A%20%20%20%20reaches%20%60customers%60%2C%20%60%22Order%20Details%22%60%2C%20%60products%60%20and%20%60employees%60%20from%20%60orders%60.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20%20%20%20%20c.%22CompanyName%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20p.%22ProductName%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20d.%22Quantity%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20round(d.%22UnitPrice%22%2C%202)%20AS%20unit_price%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20e.%22LastName%22%20AS%20sales_rep%0A%20%20%20%20%20%20%20%20FROM%20orders%20AS%20o%0A%20%20%20%20%20%20%20%20JOIN%20customers%20AS%20c%20ON%20o.%22CustomerID%22%20%3D%20c.%22CustomerID%22%0A%20%20%20%20%20%20%20%20JOIN%20%22Order%20Details%22%20AS%20d%20ON%20o.%22OrderID%22%20%3D%20d.%22OrderID%22%0A%20%20%20%20%20%20%20%20JOIN%20products%20AS%20p%20ON%20d.%22ProductID%22%20%3D%20p.%22ProductID%22%0A%20%20%20%20%20%20%20%20JOIN%20employees%20AS%20e%20ON%20o.%22EmployeeID%22%20%3D%20e.%22EmployeeID%22%0A%20%20%20%20%20%20%20%20WHERE%20o.%22OrderID%22%20%3D%2010248%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20The%20result%20is%20three%20rows%2C%20one%20per%20order%20line.%20Every%20join%20here%20climbs%20from%20a%20foreign%0A%20%20%20%20key%20to%20a%20primary%20key%2C%20which%20matches%20at%20most%20one%20row%2C%20so%20nothing%20multiplies%20after%20the%0A%20%20%20%20first%20join%20to%20%60%22Order%20Details%22%60.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20delete%20the%20%60WHERE%60%20clause%20and%20count%20the%20rows.%20Compare%20that%20number%20with%0A%20%20%20%20%60count(*)%60%20on%20%60%22Order%20Details%22%60%20and%20explain%20why%20they%20agree.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20Child%20tables%20that%20multiply%20each%20other%0A%0A%20%20%20%20Steven%20Buchanan%20is%20employee%205.%20He%20has%20orders%2C%20and%20he%20covers%20territories%2C%20and%20those%20two%0A%20%20%20%20facts%20have%20nothing%20to%20do%20with%20each%20other.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20%20%20%20%20(SELECT%20count(*)%20FROM%20orders%20WHERE%20%22EmployeeID%22%20%3D%205)%20AS%20his_orders%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20(SELECT%20count(*)%20FROM%20employeeterritories%20WHERE%20%22EmployeeID%22%20%3D%205%0A%20%20%20%20%20%20%20%20%20%20%20%20)%20AS%20his_territories%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20(SELECT%20count(*)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20FROM%20employees%20AS%20e%0A%20%20%20%20%20%20%20%20%20%20%20%20%20JOIN%20orders%20AS%20o%20ON%20e.%22EmployeeID%22%20%3D%20o.%22EmployeeID%22%0A%20%20%20%20%20%20%20%20%20%20%20%20%20JOIN%20employeeterritories%20AS%20et%20ON%20e.%22EmployeeID%22%20%3D%20et.%22EmployeeID%22%0A%20%20%20%20%20%20%20%20%20%20%20%20%20WHERE%20e.%22EmployeeID%22%20%3D%205%0A%20%20%20%20%20%20%20%20%20%20%20%20)%20AS%20joined_to_both%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20There%20are%201804%20orders%20and%20seven%20territories%2C%20and%20the%20join%20returns%2012628%20rows%2C%20which%20is%0A%20%20%20%201804%20times%207.%20Every%20order%20is%20paired%20with%20every%20territory.%20Any%20total%20of%20order%20freight%0A%20%20%20%20taken%20from%20that%20result%20is%20seven%20times%20too%20large%2C%20and%20any%20count%20of%20territories%20is%201804%0A%20%20%20%20times%20too%20large.%0A%0A%20%20%20%20Two%20independent%20children%20of%20the%20same%20parent%20multiply%20each%20other.%20Section%2019%20is%20one%0A%20%20%20%20table%20fanning%20out%3B%20this%20is%20two%2C%20and%20the%20factor%20is%20the%20product.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20run%20the%20same%20three%20counts%20for%20employee%207%2C%20who%20covers%2010%20territories%2C%0A%20%20%20%20and%20predict%20the%20third%20number%20before%20you%20look.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20%E2%9C%94%20Check%20your%20understanding%0A%0A%20%20%20%201.%20Why%20does%20a%20self%20join%20need%20aliases%20when%20a%20two-table%20join%20can%20sometimes%20get%20away%0A%20%20%20%20%20%20%20without%20them%3F%0A%20%20%20%202.%20Your%20hierarchy%20query%20returns%20more%20rows%20than%20the%20table%20has%20people.%20What%20is%20the%20first%0A%20%20%20%20%20%20%20thing%20you%20check%20in%20the%20%60ON%60%20clause%3F%0A%20%20%20%203.%20A%20four-table%20join%20returns%20the%20same%20row%20count%20as%20the%20largest%20of%20the%20four%20tables.%0A%20%20%20%20%20%20%20What%20does%20that%20tell%20you%20about%20the%20three%20joins%3F%0A%20%20%20%204.%20You%20need%20each%20salesperson's%20total%20order%20value%20and%20the%20number%20of%20territories%20they%0A%20%20%20%20%20%20%20cover.%20Explain%20why%20one%20query%20with%20two%20joins%20gives%20the%20wrong%20answer%2C%20and%20describe%20a%0A%20%20%20%20%20%20%20shape%20that%20gives%20the%20right%20one.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20---%0A%20%20%20%20%23%23%2021.%20Subqueries%0A%0A%20%20%20%20A%20subquery%20is%20a%20%60SELECT%60%20written%20inside%20another%20statement.%20It%20runs%2C%20and%20the%20query%0A%20%20%20%20around%20it%20uses%20the%20result.%20Where%20it%20sits%20decides%20what%20it%20is%20allowed%20to%20return%3A%20one%0A%20%20%20%20value%20in%20most%20of%20a%20%60WHERE%60%20clause%2C%20a%20column%20of%20values%20in%20front%20of%20%60IN%60%2C%20and%20a%20whole%0A%20%20%20%20table%20in%20%60FROM%60.%0A%0A%20%20%20%20%F0%9F%93%96%20**Used%20in%3A**%20Combining%20tables%20with%20%60JOIN%60%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20A%20subquery%20that%20returns%20a%20single%20value%0A%0A%20%20%20%20%60WHERE%20%22UnitPrice%22%20%3E%20avg(%22UnitPrice%22)%60%20is%20not%20allowed%2C%20because%20%60WHERE%60%20runs%20before%20any%0A%20%20%20%20aggregate%20exists%20(section%2016).%20A%20subquery%20gets%20the%20average%20from%20its%20own%20separate%20pass%0A%20%20%20%20over%20the%20table%2C%20so%20the%20value%20is%20ready%20before%20the%20outer%20%60WHERE%60%20needs%20it.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20%20%20%20%20(SELECT%20round(avg(%22UnitPrice%22)%2C%202)%20FROM%20products)%20AS%20average_price%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20(SELECT%20count(*)%20FROM%20products)%20AS%20all_products%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20(SELECT%20count(*)%20FROM%20products%0A%20%20%20%20%20%20%20%20%20%20%20%20%20WHERE%20%22UnitPrice%22%20%3E%20(SELECT%20avg(%22UnitPrice%22)%20FROM%20products)%0A%20%20%20%20%20%20%20%20%20%20%20%20)%20AS%20above_average%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20The%20average%20price%20is%2028.87%20and%2025%20of%20the%2077%20products%20sit%20above%20it.%20A%20subquery%20in%20this%0A%20%20%20%20position%2C%20a%20**scalar%20subquery**%2C%20has%20to%20return%20one%20row%20and%20one%20column.%20Two%20rows%20is%20a%0A%20%20%20%20runtime%20error%2C%20so%20this%20is%20one%20mistake%20the%20database%20catches%20for%20you.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20list%20those%2025%20products%2C%20most%20expensive%20first.%20Then%20change%20%60avg%60%20to%0A%20%20%20%20%60median%60%20and%20count%20again%2C%20and%20state%20which%20of%20the%20two%20numbers%20you%20would%20quote%20to%20somebody%0A%20%20%20%20asking%20about%20a%20typical%20price.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20A%20subquery%20that%20returns%20a%20list%0A%0A%20%20%20%20%60IN%60%20takes%20a%20column%20of%20values%2C%20and%20a%20subquery%20can%20supply%20it.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20%20%20%20%20(SELECT%20count(*)%20FROM%20orders%0A%20%20%20%20%20%20%20%20%20%20%20%20%20WHERE%20%22CustomerID%22%20IN%20(%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20SELECT%20%22CustomerID%22%20FROM%20customers%20WHERE%20%22Country%22%20%3D%20'France'%0A%20%20%20%20%20%20%20%20%20%20%20%20%20)%0A%20%20%20%20%20%20%20%20%20%20%20%20)%20AS%20with_in%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20(SELECT%20count(*)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20FROM%20orders%20AS%20o%0A%20%20%20%20%20%20%20%20%20%20%20%20%20JOIN%20customers%20AS%20c%20ON%20o.%22CustomerID%22%20%3D%20c.%22CustomerID%22%0A%20%20%20%20%20%20%20%20%20%20%20%20%20WHERE%20c.%22Country%22%20%3D%20'France'%0A%20%20%20%20%20%20%20%20%20%20%20%20)%20AS%20with_a_join%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20Both%20say%201909.%20The%20join%20and%20the%20%60IN%60%20answer%20the%20same%20question%2C%20and%20they%20part%20company%0A%20%20%20%20in%20two%20places.%20The%20join%20can%20put%20%60c.%22CompanyName%22%60%20in%20the%20output%20and%20%60IN%60%20cannot.%20And%20a%0A%20%20%20%20join%20to%20a%20table%20where%20the%20key%20repeats%20duplicates%20rows%2C%20which%20is%20section%2019%2C%20while%20%60IN%60%0A%20%20%20%20never%20does.%20An%20%60IN%60%20subquery%20like%20this%20is%20a%20**semi-join**.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20add%20%60c.%22CompanyName%22%60%20to%20the%20join%20version%2C%20then%20try%20to%20add%20it%20to%20the%0A%20%20%20%20%60IN%60%20version.%20Then%20check%20with%20%60count(DISTINCT%20...)%60%20that%20the%201909%20orders%20come%20from%2011%0A%20%20%20%20French%20customers.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20%60NOT%20IN%60%20and%20a%20%60NULL%60%20in%20the%20list%0A%0A%20%20%20%20The%20question%20is%20which%20employees%20manage%20nobody.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20%20%20%20%20(SELECT%20count(*)%20FROM%20employees%0A%20%20%20%20%20%20%20%20%20%20%20%20%20WHERE%20%22EmployeeID%22%20NOT%20IN%20(SELECT%20%22ReportsTo%22%20FROM%20employees)%0A%20%20%20%20%20%20%20%20%20%20%20%20)%20AS%20manages_nobody%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20(SELECT%20count(*)%20FROM%20employees%0A%20%20%20%20%20%20%20%20%20%20%20%20%20WHERE%20%22EmployeeID%22%20NOT%20IN%20(%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20SELECT%20%22ReportsTo%22%20FROM%20employees%20WHERE%20%22ReportsTo%22%20IS%20NOT%20NULL%0A%20%20%20%20%20%20%20%20%20%20%20%20%20)%0A%20%20%20%20%20%20%20%20%20%20%20%20)%20AS%20manages_nobody_fixed%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20The%20first%20answer%20is%20zero%20and%20the%20correct%20answer%20is%20seven.%0A%0A%20%20%20%20%60ReportsTo%60%20is%20%60NULL%60%20for%20Andrew%20Fuller%2C%20so%20the%20list%20the%20subquery%20hands%20over%20is%0A%20%20%20%20%602%2C%20NULL%2C%202%2C%202%2C%202%2C%205%2C%205%2C%202%2C%205%60.%20%60x%20NOT%20IN%20(...)%60%20is%20true%20only%20when%20%60x%60%20differs%20from%0A%20%20%20%20every%20entry%2C%20comparing%20anything%20with%20%60NULL%60%20gives%20unknown%20(section%209)%2C%20and%20unknown%20is%0A%20%20%20%20not%20true.%20So%20the%20test%20never%20passes%20and%20every%20row%20is%20filtered%20out.%0A%0A%20%20%20%20**The%20trap.**%20Zero%20rows%20is%20a%20perfectly%20plausible%20business%20answer.%20Nothing%20here%0A%20%20%20%20distinguishes%20%22no%20employee%20manages%20anybody%22%20from%20%22this%20query%20cannot%20return%20a%20row%22.%0A%0A%20%20%20%20Any%20%60NOT%20IN%60%20over%20a%20column%20that%20can%20hold%20%60NULL%60%20needs%20%60IS%20NOT%20NULL%60%20inside%20the%0A%20%20%20%20subquery%2C%20or%20%60NOT%20EXISTS%60%2C%20or%20the%20anti-join%20from%20section%2018.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20list%20the%20seven%20with%20the%20fixed%20version.%20Then%20write%20the%20same%20question%20as%20a%0A%20%20%20%20%60LEFT%20JOIN%60%20anti-join%2C%20%60employees%60%20to%20%60employees%60%20on%0A%20%20%20%20%60e.%22EmployeeID%22%20%3D%20r.%22ReportsTo%22%60%2C%20and%20confirm%20you%20get%20the%20same%20seven.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20A%20subquery%20in%20%60FROM%60%20is%20a%20table%0A%0A%20%20%20%20Section%2019's%20fix%20is%20this%20shape.%20Aggregate%20the%20many%20side%20down%20to%20one%20row%20per%20key%2C%20then%0A%20%20%20%20join%20to%20it.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%20c.%22CompanyName%22%2C%20round(t.freight%2C%202)%20AS%20freight%2C%20t.n_orders%0A%20%20%20%20%20%20%20%20FROM%20(%0A%20%20%20%20%20%20%20%20%20%20%20%20SELECT%20%22CustomerID%22%2C%20sum(%22Freight%22)%20AS%20freight%2C%20count(*)%20AS%20n_orders%0A%20%20%20%20%20%20%20%20%20%20%20%20FROM%20orders%0A%20%20%20%20%20%20%20%20%20%20%20%20GROUP%20BY%20%22CustomerID%22%0A%20%20%20%20%20%20%20%20)%20AS%20t%0A%20%20%20%20%20%20%20%20JOIN%20customers%20AS%20c%20ON%20t.%22CustomerID%22%20%3D%20c.%22CustomerID%22%0A%20%20%20%20%20%20%20%20ORDER%20BY%20t.freight%20DESC%0A%20%20%20%20%20%20%20%20LIMIT%205%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20B's%20Beverages%20tops%20the%20list%20at%2054797.25%20across%20210%20orders.%20Every%20column%20the%20subquery%0A%20%20%20%20exposes%20has%20to%20be%20named%2C%20which%20is%20why%20%60sum(%22Freight%22)%60%20carries%20%60AS%20freight%60.%20DuckDB%0A%20%20%20%20lets%20you%20leave%20the%20alias%20%60AS%20t%60%20off%20the%20subquery%20itself%3B%20several%20other%20engines%20refuse%2C%0A%20%20%20%20so%20write%20it.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20delete%20%60AS%20t%60%20and%20run%20it.%20DuckDB%20accepts%20the%20subquery%20without%20a%20name%2C%0A%20%20%20%20and%20the%20error%20says%20%60Referenced%20table%20%22t%22%20not%20found!%60%2C%20because%20the%20rest%20of%20the%20query%20lost%20the%0A%20%20%20%20name%20it%20was%20using.%20Put%20%60AS%20t%60%20back%2C%20move%20the%20%60LIMIT%205%60%20inside%20the%20subquery%2C%20run%20it%2C%20and%0A%20%20%20%20explain%20why%20the%20five%20companies%20changed.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20%E2%9C%94%20Check%20your%20understanding%0A%0A%20%20%20%201.%20A%20subquery%20in%20a%20%60WHERE%60%20clause%20returns%20two%20rows%20and%20the%20query%20fails.%20Where%20else%0A%20%20%20%20%20%20%20could%20that%20same%20subquery%20sit%20without%20failing%3F%0A%20%20%20%202.%20When%20does%20%60IN%60%20with%20a%20subquery%20give%20a%20different%20row%20count%20from%20the%20equivalent%20join%3F%0A%20%20%20%203.%20%60NOT%20IN%60%20returns%20zero%20rows%20and%20you%20were%20expecting%20a%20handful.%20What%20do%20you%20check%2C%20and%0A%20%20%20%20%20%20%20what%20is%20the%20one-line%20fix%3F%0A%20%20%20%204.%20You%20have%20a%20subquery%20in%20%60FROM%60%20that%20groups%20order%20lines%20by%20%60OrderID%60.%20What%20has%20to%20be%0A%20%20%20%20%20%20%20true%20of%20its%20output%20for%20the%20join%20around%20it%20to%20be%20safe%3F%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20---%0A%20%20%20%20%23%23%2022.%20Common%20table%20expressions%20(%60WITH%60)%0A%0A%20%20%20%20%60WITH%20name%20AS%20(query)%60%20puts%20a%20subquery%20in%20front%20of%20the%20statement%20and%20gives%20it%20a%20name.%0A%20%20%20%20The%20named%20query%20is%20a%20**common%20table%20expression**%2C%20abbreviated%20CTE.%20Everything%20after%20it%0A%20%20%20%20can%20read%20that%20name%20as%20if%20it%20were%20a%20table.%20The%20result%20is%20the%20same%20as%20a%20subquery%20in%0A%20%20%20%20%60FROM%60%2C%20and%20the%20query%20reads%20from%20the%20top%20down%20in%20the%20order%20the%20work%20happens.%0A%0A%20%20%20%20%F0%9F%93%96%20**Used%20in%3A**%20Combining%20tables%20with%20%60JOIN%60%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20%60WITH%60%20names%20a%20step%0A%0A%20%20%20%20This%20is%20section%2019's%20fix%20with%20the%20parentheses%20moved%20to%20the%20front.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20WITH%20lines%20AS%20(%0A%20%20%20%20%20%20%20%20%20%20%20%20SELECT%20%22OrderID%22%2C%20count(*)%20AS%20n_lines%2C%20sum(%22Quantity%22)%20AS%20units%0A%20%20%20%20%20%20%20%20%20%20%20%20FROM%20%22Order%20Details%22%0A%20%20%20%20%20%20%20%20%20%20%20%20GROUP%20BY%20%22OrderID%22%0A%20%20%20%20%20%20%20%20)%0A%20%20%20%20%20%20%20%20SELECT%20o.%22OrderID%22%2C%20round(o.%22Freight%22%2C%202)%20AS%20freight%2C%20lines.n_lines%2C%20lines.units%0A%20%20%20%20%20%20%20%20FROM%20orders%20AS%20o%0A%20%20%20%20%20%20%20%20JOIN%20lines%20ON%20o.%22OrderID%22%20%3D%20lines.%22OrderID%22%0A%20%20%20%20%20%20%20%20WHERE%20o.%22OrderID%22%20BETWEEN%2010248%20AND%2010252%0A%20%20%20%20%20%20%20%20ORDER%20BY%20o.%22OrderID%22%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20The%20five%20rows%2C%20the%20freight%20and%20the%20line%20counts%20are%20the%20same%20as%20before.%20The%20name%0A%20%20%20%20%60lines%60%20goes%20wherever%20a%20table%20name%20would%20go%2C%20and%20the%20reader%20meets%20the%20definition%20before%0A%20%20%20%20the%20query%20that%20uses%20it.%0A%0A%20%20%20%20A%20%60WITH%60%20clause%20can%20be%20read%20on%20its%20own.%20Replacing%20the%20whole%20final%20%60SELECT%60%20with%0A%20%20%20%20%60SELECT%20*%20FROM%20lines%20LIMIT%205%60%20is%20how%20you%20check%20a%20step%20in%20the%20middle%20of%20a%20query%20you%20are%0A%20%20%20%20debugging.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20run%20the%20total%20version%20as%20a%20CTE%20and%20confirm%20five%20rows%20and%20120.50.%20Then%0A%20%20%20%20replace%20the%20final%20select%20with%20%60SELECT%20*%20FROM%20lines%20LIMIT%205%60%20and%20read%20what%20the%20step%0A%20%20%20%20produced.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20The%20same%20question%20written%20both%20ways%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20%20%20%20%20(SELECT%20count(*)%20FROM%20orders%0A%20%20%20%20%20%20%20%20%20%20%20%20%20WHERE%20%22Freight%22%20%3E%20(SELECT%20avg(%22Freight%22)%20FROM%20orders)%0A%20%20%20%20%20%20%20%20%20%20%20%20)%20AS%20with_a_subquery%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20(WITH%20avg_freight%20AS%20(SELECT%20avg(%22Freight%22)%20AS%20f%20FROM%20orders)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20SELECT%20count(*)%20FROM%20orders%0A%20%20%20%20%20%20%20%20%20%20%20%20%20JOIN%20avg_freight%20ON%20orders.%22Freight%22%20%3E%20avg_freight.f%0A%20%20%20%20%20%20%20%20%20%20%20%20)%20AS%20with_a_cte%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20Both%20give%208025%20orders%20above%20the%20average%20freight%20of%20248.59.%0A%0A%20%20%20%20The%20subquery%20mentions%20nothing%20outside%20itself%2C%20so%20both%20versions%20compute%20the%20average%0A%20%20%20%20once.%20What%20the%20CTE%20adds%20is%20that%20the%20step%20has%20a%20name%20and%20somebody%20can%0A%20%20%20%20read%20it.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20time%20both%20with%20%60SELECT%20count(*)%60%20over%20the%20full%2016282%20orders%20and%20see%0A%20%20%20%20whether%20you%20can%20measure%20a%20difference.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20A%20correlated%20subquery%0A%0A%20%20%20%20%22Products%20priced%20above%20the%20average%20for%20their%20own%20category%22%20needs%20a%20different%20average%0A%20%20%20%20for%20each%20row%2C%20and%20the%20subquery%20mentions%20%60p.%22CategoryID%22%60%20from%20the%20outer%20query%20to%20get%0A%20%20%20%20it.%20A%20subquery%20that%20mentions%20a%20column%20of%20the%20outer%20query%20is%20**correlated**.%20Read%0A%20%20%20%20literally%2C%20it%20runs%20once%20for%20every%20outer%20row.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20%20%20%20%20(SELECT%20count(*)%20FROM%20products%20AS%20p%0A%20%20%20%20%20%20%20%20%20%20%20%20%20WHERE%20p.%22UnitPrice%22%20%3E%20(%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20SELECT%20avg(p2.%22UnitPrice%22)%20FROM%20products%20AS%20p2%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20WHERE%20p2.%22CategoryID%22%20%3D%20p.%22CategoryID%22%0A%20%20%20%20%20%20%20%20%20%20%20%20%20)%0A%20%20%20%20%20%20%20%20%20%20%20%20)%20AS%20correlated_subquery%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20(WITH%20category_avg%20AS%20(%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20SELECT%20%22CategoryID%22%2C%20avg(%22UnitPrice%22)%20AS%20avg_price%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20FROM%20products%20GROUP%20BY%20%22CategoryID%22%0A%20%20%20%20%20%20%20%20%20%20%20%20%20)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20SELECT%20count(*)%20FROM%20products%20AS%20p%0A%20%20%20%20%20%20%20%20%20%20%20%20%20JOIN%20category_avg%20AS%20a%20ON%20p.%22CategoryID%22%20%3D%20a.%22CategoryID%22%0A%20%20%20%20%20%20%20%20%20%20%20%20%20WHERE%20p.%22UnitPrice%22%20%3E%20a.avg_price%0A%20%20%20%20%20%20%20%20%20%20%20%20)%20AS%20cte_and_join%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20Both%20say%2027.%20Read%20literally%2C%20the%20first%20computes%20an%20average%20for%20each%20of%20the%2077%0A%20%20%20%20products%20and%20the%20second%20computes%20eight%20averages%20and%20joins%20to%20them.%20DuckDB%20does%20not%0A%20%20%20%20run%20the%20first%20one%20literally.%20It%20rewrites%20it%20into%20the%20second%20shape%2C%20one%20grouping%20and%0A%20%20%20%20one%20join%2C%20before%20it%20runs.%20SQLite%20does%20run%20it%20once%20per%20row.%20The%20CTE%20version%20spells%20out%0A%20%20%20%20the%20efficient%20shape%2C%20so%20it%20does%20not%20depend%20on%20the%20engine%20finding%20it.%0A%0A%20%20%20%20The%20join%20in%20the%20second%20version%20is%20safe%20from%20section%2019%20because%20%60category_avg%60%20holds%20one%0A%20%20%20%20row%20per%20%60CategoryID%60%2C%20which%20is%20the%20same%20property%20that%20made%20the%20fix%20work%20there.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20run%20the%20CTE%20on%20its%20own%20and%20confirm%20it%20returns%20eight%20rows.%20Then%20list%20the%0A%20%20%20%2027%20products%20with%20their%20category%20and%20their%20category%20average%20side%20by%20side.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20A%20chain%20of%20CTEs%0A%0A%20%20%20%20One%20%60WITH%60%20can%20define%20several%20names%2C%20separated%20by%20commas.%20The%20second%20may%20read%20the%0A%20%20%20%20first.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20WITH%20lines%20AS%20(%0A%20%20%20%20%20%20%20%20%20%20%20%20SELECT%20%22ProductID%22%2C%20sum(%22Quantity%22)%20AS%20units%0A%20%20%20%20%20%20%20%20%20%20%20%20FROM%20%22Order%20Details%22%0A%20%20%20%20%20%20%20%20%20%20%20%20GROUP%20BY%20%22ProductID%22%0A%20%20%20%20%20%20%20%20)%2C%0A%20%20%20%20%20%20%20%20by_category%20AS%20(%0A%20%20%20%20%20%20%20%20%20%20%20%20SELECT%20c.%22CategoryName%22%2C%20sum(l.units)%20AS%20units%0A%20%20%20%20%20%20%20%20%20%20%20%20FROM%20lines%20AS%20l%0A%20%20%20%20%20%20%20%20%20%20%20%20JOIN%20products%20AS%20p%20ON%20l.%22ProductID%22%20%3D%20p.%22ProductID%22%0A%20%20%20%20%20%20%20%20%20%20%20%20JOIN%20categories%20AS%20c%20ON%20p.%22CategoryID%22%20%3D%20c.%22CategoryID%22%0A%20%20%20%20%20%20%20%20%20%20%20%20GROUP%20BY%20c.%22CategoryName%22%0A%20%20%20%20%20%20%20%20)%0A%20%20%20%20%20%20%20%20SELECT%20%22CategoryName%22%2C%20units%0A%20%20%20%20%20%20%20%20FROM%20by_category%0A%20%20%20%20%20%20%20%20ORDER%20BY%20units%20DESC%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20Confections%20leads%20on%202628466%20units%2C%20and%20Produce%20trails%20on%201010224.%20The%20query%20has%20three%0A%20%20%20%20steps%20and%20each%20one%20carries%20a%20name%3A%20total%20by%20product%2C%20roll%20up%20to%20category%2C%20sort.%0A%0A%20%20%20%20Each%20name%20is%20checkable%20on%20its%20own%20by%20changing%20the%20final%20select%2C%20which%20is%20the%20practical%0A%20%20%20%20reason%20to%20write%20a%20long%20query%20this%20way.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20replace%20the%20last%20select%20with%20%60SELECT%20*%20FROM%20lines%20LIMIT%205%60%20and%20check%20that%0A%20%20%20%20the%20units%20for%20one%20product%20match%20%60sum(%22Quantity%22)%60%20on%20%60%22Order%20Details%22%60%20for%20that%0A%20%20%20%20product.%20Then%20add%20a%20third%20step%20keeping%20only%20categories%20above%202000000%20units.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20%E2%9C%94%20Check%20your%20understanding%0A%0A%20%20%20%201.%20A%20colleague%20says%20a%20CTE%20is%20always%20faster%20than%20a%20subquery.%20When%20is%20that%20true%20and%20when%0A%20%20%20%20%20%20%20is%20it%20not%3F%0A%20%20%20%202.%20Your%20query%20has%20four%20CTEs%20and%20returns%20a%20number%20you%20do%20not%20believe.%20Describe%20how%20you%0A%20%20%20%20%20%20%20find%20the%20step%20that%20is%20wrong.%0A%20%20%20%203.%20What%20makes%20a%20subquery%20correlated%2C%20and%20how%20do%20you%20spot%20one%20by%20reading%20it%3F%0A%20%20%20%204.%20You%20write%20a%20CTE%20that%20aggregates%20order%20lines%20by%20%60OrderID%60%20and%20join%20it%20to%20%60orders%60.%0A%20%20%20%20%20%20%20What%20property%20of%20that%20CTE%20makes%20the%20join%20safe%2C%20and%20how%20would%20you%20check%20it%3F%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20---%0A%20%20%20%20%23%23%2023.%20Window%20functions%0A%0A%20%20%20%20%3E%20**Advanced**%0A%0A%20%20%20%20Nothing%20in%20this%20course%20requires%20this%20section.%0A%0A%20%20%20%20A%20window%20function%20keeps%20every%20row%20and%20adds%20a%20column%20computed%20across%20a%20set%20of%20rows.%0A%20%20%20%20%60GROUP%20BY%60%20collapses%20rows%20into%20groups.%20When%20one%20question%20needs%20the%20row-level%20detail%20and%0A%20%20%20%20a%20group-level%20number%20in%20the%20same%20result%2C%20a%20window%20function%20gives%20both.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20%60OVER%20()%60%20looks%20at%20every%20row%0A%0A%20%20%20%20An%20empty%20%60OVER%20()%60%20means%20the%20window%20is%20the%20whole%20result.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20%20%20%20%20c.%22CategoryName%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20count(*)%20AS%20n_products%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20sum(count(*))%20OVER%20()%20AS%20all_products%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20round(100.0%20*%20count(*)%20%2F%20sum(count(*))%20OVER%20()%2C%201)%20AS%20pct%0A%20%20%20%20%20%20%20%20FROM%20products%20AS%20p%0A%20%20%20%20%20%20%20%20JOIN%20categories%20AS%20c%20ON%20p.%22CategoryID%22%20%3D%20c.%22CategoryID%22%0A%20%20%20%20%20%20%20%20GROUP%20BY%20c.%22CategoryName%22%0A%20%20%20%20%20%20%20%20ORDER%20BY%20n_products%20DESC%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20Confections%20holds%2013%20of%20the%2077%20products%2C%20which%20is%2016.9%20percent.%20The%20%60GROUP%20BY%60%20runs%0A%20%20%20%20first%20and%20produces%20eight%20rows%2C%20and%20the%20window%20function%20then%20sums%20across%20those%20eight%2C%0A%20%20%20%20so%20%60all_products%60%20reads%2077%20on%20every%20row.%0A%0A%20%20%20%20Section%2021%20answers%20the%20same%20question%20with%20a%20scalar%20subquery.%20The%20window%20form%20takes%20one%0A%20%20%20%20pass%20and%20puts%20the%20share%20beside%20the%20count.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20delete%20%60OVER%20()%60%20and%20read%20the%20error.%20Then%20change%20it%20to%0A%20%20%20%20%60OVER%20(PARTITION%20BY%20c.%22CategoryName%22)%60%20and%20explain%20why%20every%20percentage%20becomes%20100.0.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20%60PARTITION%20BY%60%20restarts%20the%20window%0A%0A%20%20%20%20The%20query%20below%20keeps%20the%20two%20most%20expensive%20products%20in%20each%20category.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20%20%20%20%20c.%22CategoryName%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20p.%22ProductName%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20round(p.%22UnitPrice%22%2C%202)%20AS%20price%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20RANK()%20OVER%20(%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20PARTITION%20BY%20c.%22CategoryName%22%20ORDER%20BY%20p.%22UnitPrice%22%20DESC%0A%20%20%20%20%20%20%20%20%20%20%20%20)%20AS%20rank_in_category%0A%20%20%20%20%20%20%20%20FROM%20products%20AS%20p%0A%20%20%20%20%20%20%20%20JOIN%20categories%20AS%20c%20ON%20p.%22CategoryID%22%20%3D%20c.%22CategoryID%22%0A%20%20%20%20%20%20%20%20QUALIFY%20rank_in_category%20%3C%3D%202%0A%20%20%20%20%20%20%20%20ORDER%20BY%20c.%22CategoryName%22%2C%20rank_in_category%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20Eight%20categories%20give%20two%20rows%20each%2C%20which%20is%2016%20rows.%20%60PARTITION%20BY%60%20restarts%20the%0A%20%20%20%20ranking%20at%20every%20new%20category%2C%20and%20the%20%60ORDER%20BY%60%20inside%20%60OVER%60%20is%20the%20ranking%20rule%2C%0A%20%20%20%20which%20has%20nothing%20to%20do%20with%20the%20%60ORDER%20BY%60%20at%20the%20end%20of%20the%20statement.%0A%0A%20%20%20%20%60QUALIFY%60%20filters%20on%20a%20window%20function's%20result.%20DuckDB%2C%20Snowflake%20and%20BigQuery%20have%0A%20%20%20%20it.%20Elsewhere%20the%20query%20goes%20inside%20a%20CTE%20and%20the%20filter%20goes%20in%20a%20%60WHERE%60%20outside%20it%2C%0A%20%20%20%20because%20%60WHERE%60%20runs%20before%20window%20functions%20are%20computed.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20rewrite%20this%20with%20a%20CTE%20and%20an%20outer%20%60WHERE%20rank_in_category%20%3C%3D%202%60%2C%20and%0A%20%20%20%20confirm%20the%20same%2016%20rows.%20Then%20put%20that%20%60WHERE%60%20into%20the%20original%20query%20and%20read%20the%0A%20%20%20%20error.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20%60RANK()%60%20and%20%60ROW_NUMBER()%60%20disagree%20on%20ties%0A%0A%20%20%20%20Two%20products%20cost%2038.00.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20%20%20%20%20%22ProductName%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20round(%22UnitPrice%22%2C%202)%20AS%20price%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20RANK()%20OVER%20(ORDER%20BY%20%22UnitPrice%22%20DESC)%20AS%20rk%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20ROW_NUMBER()%20OVER%20(ORDER%20BY%20%22UnitPrice%22%20DESC)%20AS%20rn%0A%20%20%20%20%20%20%20%20FROM%20products%0A%20%20%20%20%20%20%20%20WHERE%20%22UnitPrice%22%20BETWEEN%2032.80%20AND%2038.00%0A%20%20%20%20%20%20%20%20ORDER%20BY%20%22UnitPrice%22%20DESC%2C%20%22ProductName%22%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20Gnocchi%20di%20nonna%20Alice%20and%20Queso%20Manchego%20La%20Pastora%20both%20cost%2038.00.%20%60RANK()%60%20gives%0A%20%20%20%20them%20both%201%20and%20then%20skips%20to%203.%20%60ROW_NUMBER()%60%20gives%20one%20of%20them%201%20and%20the%20other%202%2C%0A%20%20%20%20and%20which%20one%20gets%201%20is%20arbitrary%2C%20because%20the%20%60ORDER%20BY%60%20inside%20%60OVER%60%20does%20not%0A%20%20%20%20separate%20them.%0A%0A%20%20%20%20**The%20trap.**%20%22Top%2010%20by%20revenue%22%20written%20with%20%60ROW_NUMBER()%60%20always%20returns%20exactly%0A%20%20%20%2010%20rows%20and%20drops%20a%20tie%20without%20saying%20so%3B%20written%20with%20%60RANK()%60%20it%20can%20return%2011.%0A%20%20%20%20Decide%20which%20behavior%20the%20question%20wants%2C%20and%20add%20a%20tiebreaker%20column%20to%20the%0A%20%20%20%20%60ORDER%20BY%60%20inside%20%60OVER%60%20when%20the%20answer%20has%20to%20be%20the%20same%20every%20time.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20add%20%60%2C%20%22ProductName%22%60%20inside%20both%20%60OVER%60%20clauses%2C%20run%20it%20twice%2C%20and%20state%0A%20%20%20%20which%20of%20the%20two%20columns%20stopped%20moving.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20%60LAG()%60%20reads%20the%20previous%20row%0A%0A%20%20%20%20The%20query%20below%20groups%20freight%20by%20month%20for%20the%20second%20half%20of%202016%20and%20adds%20the%20change%0A%20%20%20%20from%20the%20month%20before.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20WITH%20monthly%20AS%20(%0A%20%20%20%20%20%20%20%20%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20date_trunc('month'%2C%20%22OrderDate%22)%3A%3ADATE%20AS%20month%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20round(sum(%22Freight%22)%2C%202)%20AS%20freight%0A%20%20%20%20%20%20%20%20%20%20%20%20FROM%20orders%0A%20%20%20%20%20%20%20%20%20%20%20%20WHERE%20%22OrderDate%22%20%3E%3D%20DATE%20'2016-07-01'%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20AND%20%22OrderDate%22%20%3C%20DATE%20'2017-01-01'%0A%20%20%20%20%20%20%20%20%20%20%20%20GROUP%20BY%201%0A%20%20%20%20%20%20%20%20)%0A%20%20%20%20%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20%20%20%20%20month%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20freight%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20LAG(freight)%20OVER%20(ORDER%20BY%20month)%20AS%20prev_month%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20freight%20-%20LAG(freight)%20OVER%20(ORDER%20BY%20month)%20AS%20change%0A%20%20%20%20%20%20%20%20FROM%20monthly%0A%20%20%20%20%20%20%20%20ORDER%20BY%20month%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20August%20is%20down%204411.00%20on%20July%20and%20December%20is%20up%204017.75%20on%20November.%20Period%20over%0A%20%20%20%20period%20change%20is%20the%20most%20common%20business%20use%20of%20a%20window%20function.%0A%0A%20%20%20%20July%20has%20no%20previous%20month%2C%20so%20%60prev_month%60%20and%20%60change%60%20are%20%60NULL%60%20on%20that%20row.%20That%0A%20%20%20%20is%20correct%2C%20and%20it%20will%20quietly%20change%20any%20average%20taken%20over%20the%20%60change%60%20column.%0A%0A%20%20%20%20The%20date%20range%20is%20written%20half%20open%2C%20from%20the%20first%20of%20July%20up%20to%20but%20not%20including%0A%20%20%20%20the%20first%20of%20January%2C%20which%20is%20section%2012.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20change%20%60LAG%60%20to%20%60LEAD%60%20and%20state%20which%20row%20goes%20%60NULL%60%20now.%20Then%20add%0A%20%20%20%20%60round(100.0%20*%20change%20%2F%20prev_month%2C%201)%20AS%20pct_change%60%20and%20look%20at%20the%20July%20row.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20A%20frame%20decides%20which%20rows%20the%20window%20sees%0A%0A%20%20%20%20A%20running%20total%20needs%20each%20row%20to%20see%20everything%20before%20it%20and%20nothing%20after%20it%2C%20and%0A%20%20%20%20the%20frame%20clause%20is%20where%20you%20specify%20it.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(con)%3A%0A%20%20%20%20con.sql(%22%22%22%0A%20%20%20%20%20%20%20%20WITH%20monthly%20AS%20(%0A%20%20%20%20%20%20%20%20%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20date_trunc('month'%2C%20%22OrderDate%22)%3A%3ADATE%20AS%20month%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20round(sum(%22Freight%22)%2C%202)%20AS%20freight%0A%20%20%20%20%20%20%20%20%20%20%20%20FROM%20orders%0A%20%20%20%20%20%20%20%20%20%20%20%20WHERE%20%22OrderDate%22%20%3E%3D%20DATE%20'2016-07-01'%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20AND%20%22OrderDate%22%20%3C%20DATE%20'2017-01-01'%0A%20%20%20%20%20%20%20%20%20%20%20%20GROUP%20BY%201%0A%20%20%20%20%20%20%20%20)%0A%20%20%20%20%20%20%20%20SELECT%0A%20%20%20%20%20%20%20%20%20%20%20%20month%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20freight%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20sum(freight)%20OVER%20(%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20ORDER%20BY%20month%20ROWS%20BETWEEN%20UNBOUNDED%20PRECEDING%20AND%20CURRENT%20ROW%0A%20%20%20%20%20%20%20%20%20%20%20%20)%20AS%20running_total%0A%20%20%20%20%20%20%20%20FROM%20monthly%0A%20%20%20%20%20%20%20%20ORDER%20BY%20month%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%60UNBOUNDED%20PRECEDING%60%20to%20%60CURRENT%20ROW%60%20means%20every%20row%20from%20the%20start%20of%20the%20window%20up%0A%20%20%20%20to%20this%20one%2C%20which%20is%20a%20running%20total.%20The%20last%20value%2C%20184977.00%2C%20is%20the%20sum%20of%20all%20six%0A%20%20%20%20months%2C%20and%20checking%20it%20against%20a%20plain%20%60sum%60%20over%20the%20same%20range%20is%20the%20verification%0A%20%20%20%20for%20this%20kind%20of%20query.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20change%20the%20frame%20to%20%60ROWS%20BETWEEN%202%20PRECEDING%20AND%20CURRENT%20ROW%60%20and%0A%20%20%20%20describe%20in%20one%20sentence%20what%20the%20column%20now%20holds.%20Then%20remove%20the%20frame%20clause%0A%20%20%20%20entirely%20and%20compare.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20%23%23%23%20%E2%9C%94%20Check%20your%20understanding%0A%0A%20%20%20%201.%20You%20need%20each%20order's%20freight%20and%20the%20average%20freight%20for%20its%20customer%2C%20in%20the%20same%0A%20%20%20%20%20%20%20row.%20Explain%20why%20%60GROUP%20BY%60%20cannot%20do%20this.%0A%20%20%20%202.%20Your%20ranking%20query%20returns%2011%20rows%20when%20the%20question%20asked%20for%2010.%20Which%20function%0A%20%20%20%20%20%20%20is%20in%20it%2C%20and%20what%20would%20the%20other%20one%20have%20returned%3F%0A%20%20%20%203.%20A%20monthly%20growth%20report%20shows%20a%20blank%20first%20row.%20What%20is%20that%2C%20and%20what%20would%20you%0A%20%20%20%20%20%20%20tell%20the%20person%20reading%20the%20report%3F%0A%20%20%20%204.%20%60QUALIFY%60%20is%20not%20available%20in%20your%20engine.%20Describe%20the%20rewrite%20and%20explain%20why%20%60WHERE%60%0A%20%20%20%20%20%20%20does%20not%20work.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20---%0A%20%20%20%20%23%23%20Chapter%20challenge%0A%0A%20%20%20%20Nothing%20here%20is%20due.%20It%20exists%20because%20each%20section%20above%20teaches%20one%20thing%2C%20and%20one%0A%20%20%20%20real%20question%20needs%20several%20of%20them%20at%20once.%0A%0A%20%20%20%20**Answer%20a%20business%20question%20that%20needs%20three%20tables%2C%20and%20prove%20the%20number.**%20Pick%0A%20%20%20%20something%20somebody%20would%20actually%20ask%20of%20this%20warehouse.%20Which%20category%20earns%20the%20most%0A%20%20%20%20freight.%20Which%20customers%20order%20from%20more%20than%20half%20the%20categories.%20Which%20products%20have%0A%20%20%20%20never%20been%20ordered%20by%20a%20customer%20outside%20their%20supplier's%20country.%20Your%20own%20question%20is%0A%20%20%20%20better%20than%20any%20of%20these%2C%20and%20it%20has%20to%20need%20at%20least%20three%20tables%20and%20at%20least%20one%0A%20%20%20%20aggregate.%0A%0A%20%20%20%20**Done%20looks%20like**%20one%20notebook%20holding%20four%20things.%0A%0A%20%20%20%201.%20The%20query%2C%20with%20every%20column%20qualified%20by%20a%20table%20alias.%0A%20%20%20%202.%20A%20row%20count%20before%20each%20join%20and%20after%20it%2C%20in%20the%20notebook%2C%20run.%0A%20%20%20%203.%20One%20sentence%20naming%20the%20grain%20of%20your%20result.%20What%20is%20one%20row.%0A%20%20%20%204.%20A%20%22How%20I%20know%20these%20numbers%20are%20right%22%20paragraph%20with%20one%20independent%20check%20that%20runs.%0A%20%20%20%20%20%20%20Recomputing%20a%20total%20a%20second%20way%20is%20the%20usual%20one.%0A%0A%20%20%20%20**Four%20decisions%20are%20yours%2C%20and%20no%20query%20will%20make%20them.**%0A%0A%20%20%20%201.%20**What%20grain%20does%20your%20answer%20live%20at%3F**%20One%20row%20per%20customer%2C%20per%20order%2C%20per%20order%0A%20%20%20%20%20%20%20line.%20The%20number%20changes%20with%20the%20answer.%0A%20%20%20%202.%20**Which%20join%20type%20at%20each%20step%3F**%20For%20every%20%60INNER%20JOIN%60%20you%20write%2C%20state%20what%20it%0A%20%20%20%20%20%20%20drops%20and%20how%20many%20rows%20that%20was.%0A%20%20%20%203.%20**What%20happens%20to%20the%20rows%20with%20no%20match%3F**%20Count%20them%2C%20then%20decide%20whether%20they%0A%20%20%20%20%20%20%20are%20a%20zero%20or%20an%20absence%2C%20and%20state%20which.%0A%20%20%20%204.%20**Where%20does%20the%20aggregate%20happen%3F**%20Before%20the%20join%20or%20after%20it.%20Section%2019%20is%20the%0A%20%20%20%20%20%20%20whole%20reason%20this%20is%20a%20decision.%0A%0A%20%20%20%20**Going%20further.**%20Add%20a%20window%20function%20so%20the%20result%20carries%20both%20the%20detail%20and%20the%0A%20%20%20%20group%20total%2C%20which%20is%20section%2023.%20Add%20a%20self%20join%20if%20your%20question%20has%20anything%0A%20%20%20%20reporting%20to%20anything%20else%2C%20which%20is%20section%2020.%20Then%20take%20the%20same%20question%20to%20a%0A%20%20%20%20second%20grain%2C%20one%20row%20per%20month%20instead%20of%20one%20row%20per%20customer%2C%20and%20see%20which%20of%20your%0A%20%20%20%20conclusions%20survives.%20And%20hand%20the%20finished%20notebook%20to%20somebody%20who%20is%20not%20in%20this%0A%20%20%20%20course%20without%20explaining%20it%2C%20and%20write%20down%20every%20question%20they%20had%20to%20ask%20before%20they%20believed%0A%20%20%20%20the%20number.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20---%0A%20%20%20%20%23%23%20%F0%9F%A4%96%20Ask%20your%20agent%0A%0A%20%20%20%20Two%20different%20uses%2C%20and%20the%20second%20one%20is%20the%20one%20this%20course%20grades.%0A%0A%20%20%20%20%23%23%23%20Ask%20it%20about%20what%20this%20chapter%20left%20out%0A%0A%20%20%20%20-%20%60FULL%20OUTER%20JOIN%60%20and%20%60RIGHT%20JOIN%60%20are%20named%20nowhere%20above.%20Ask%20what%20each%20returns%2C%0A%20%20%20%20%20%20ask%20for%20one%20business%20question%20that%20genuinely%20needs%20a%20%60FULL%20OUTER%20JOIN%60%2C%20and%20ask%0A%20%20%20%20%20%20which%20engines%20support%20them.%0A%20%20%20%20-%20Ask%20what%20%60USING%20(ProductID)%60%20does%2C%20how%20it%20differs%20from%20an%20%60ON%60%20clause%2C%20and%20what%0A%20%20%20%20%20%20%60NATURAL%20JOIN%60%20does.%20Then%20ask%20why%20most%20style%20guides%20ban%20%60NATURAL%20JOIN%60.%0A%20%20%20%20-%20Ask%20how%20%60NOT%20EXISTS%60%20differs%20from%20%60NOT%20IN%60%2C%20and%20have%20it%20explain%20what%20happens%20to%20each%0A%20%20%20%20%20%20when%20the%20inner%20query%20returns%20a%20%60NULL%60.%0A%20%20%20%20-%20Ask%20what%20%60WITH%20RECURSIVE%60%20is%2C%20and%20have%20it%20write%20the%20query%20that%20prints%20the%20whole%0A%20%20%20%20%20%20management%20chain%20from%20Northwind's%20%60employees%60%20table.%0A%20%20%20%20-%20Ask%20what%20%60EXPLAIN%60%20prints%20in%20DuckDB%2C%20run%20it%20on%20the%20fan-out%20query%20from%20section%2019%2C%0A%20%20%20%20%20%20and%20ask%20it%20to%20point%20out%20where%20the%20row%20count%20grows.%0A%0A%20%20%20%20Test%20whatever%20comes%20back.%20Run%20it.%0A%0A%20%20%20%20%23%23%23%20Watch%20what%20it%20decides%20without%20telling%20you%0A%0A%20%20%20%20Give%20it%20this%2C%20exactly%3A%0A%0A%20%20%20%20%3E%20*%22Using%20the%20Northwind%20tables%20%60orders%60%20and%20%60Order%20Details%60%2C%20write%20me%20a%20SQL%20query%20that%0A%20%20%20%20%3E%20gives%20total%20freight%20by%20customer.%22*%0A%0A%20%20%20%20Then%20read%20what%20came%20back%20and%20answer%20four%20questions.%0A%0A%20%20%20%201.%20**Did%20it%20join%20the%20two%20tables%3F**%20If%20it%20did%2C%20count%20the%20rows%20its%20query%20returns%20and%0A%20%20%20%20%20%20%20compare%20with%2093.%0A%20%20%20%202.%20**Which%20number%20does%20its%20query%20produce%20for%20total%20freight%20across%20all%20customers%3F**%20Run%0A%20%20%20%20%20%20%20it%20and%20compare%20with%204047470.50.%0A%20%20%20%203.%20**Did%20it%20say%20anything%20about%20duplication%3F**%20Some%20agents%20add%20a%20comment%20about%20it%2C%20some%0A%20%20%20%20%20%20%20add%20%60DISTINCT%60%2C%20and%20some%20do%20neither.%0A%20%20%20%204.%20**What%20grain%20did%20it%20assume%3F**%20You%20asked%20for%20%22by%20customer%22%20and%20nothing%20else%2C%20and%20it%20chose%0A%20%20%20%20%20%20%20whether%20that%20means%20per%20customer%2C%20per%20customer%20per%20year%2C%20or%20per%20customer%20per%20order.%0A%0A%20%20%20%20If%20it%20got%20this%20right%20the%20first%20time%2C%20ask%20it%20the%20same%20question%20with%20%60employees%60%20and%0A%20%20%20%20%60employeeterritories%60%20added%2C%20which%20is%20section%2020's%20compounding%20case.%0A%0A%20%20%20%20%F0%9F%91%89%20**Try%20it%3A**%20send%20the%20same%20request%20twice%2C%20in%20two%20separate%20conversations%2C%20and%20put%20the%0A%20%20%20%20two%20queries%20side%20by%20side.%20Then%20send%20it%20a%20third%20time%20with%20one%20extra%20sentence%20saying%0A%20%20%20%20freight%20is%20recorded%20once%20per%20order%2C%20and%20see%20whether%20the%20query%20changes.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(mo)%3A%0A%20%20%20%20mo.md(r%22%22%22%0A%20%20%20%20---%0A%20%20%20%20%23%23%20Chapter%20C%20checklist%0A%0A%20%20%20%20-%20%5B%20%5D%20I%20can%20write%20an%20inner%20join%20between%20two%20tables%20and%20qualify%20every%20column%20in%20it%0A%20%20%20%20-%20%5B%20%5D%20I%20can%20state%20what%20an%20inner%20join%20dropped%2C%20and%20count%20it%0A%20%20%20%20-%20%5B%20%5D%20I%20can%20state%20which%20table%20a%20%60LEFT%20JOIN%60%20protects%20and%20what%20arrives%20as%20%60NULL%60%0A%20%20%20%20-%20%5B%20%5D%20I%20can%20write%20an%20anti-join%20to%20find%20rows%20with%20no%20match%20anywhere%0A%20%20%20%20-%20%5B%20%5D%20I%20can%20explain%20why%20a%20right-table%20condition%20belongs%20in%20%60ON%60%20and%20not%20in%20%60WHERE%60%0A%20%20%20%20-%20%5B%20%5D%20I%20can%20tell%20a%20%60NULL%60%20from%20a%20missing%20match%20apart%20from%20a%20%60NULL%60%20in%20the%20row%0A%20%20%20%20-%20%5B%20%5D%20I%20can%20count%20the%20rows%20before%20a%20join%20and%20after%20it%2C%20and%20explain%20why%20the%20two%20differ%0A%20%20%20%20-%20%5B%20%5D%20I%20can%20name%20the%20grain%20of%20a%20joined%20result%20before%20I%20aggregate%20anything%0A%20%20%20%20-%20%5B%20%5D%20I%20can%20state%20which%20columns%20are%20safe%20to%20sum%20after%20a%20join%20and%20which%20are%20not%0A%20%20%20%20-%20%5B%20%5D%20I%20can%20explain%20why%20%60SUM(DISTINCT%20...)%60%20passes%20a%20small%20test%20and%20fails%20a%20large%20one%0A%20%20%20%20-%20%5B%20%5D%20I%20can%20aggregate%20the%20many%20side%20first%20and%20join%20to%20the%20result%0A%20%20%20%20-%20%5B%20%5D%20I%20can%20write%20a%20subquery%20in%20%60WHERE%60%2C%20in%20%60IN%60%20and%20in%20%60FROM%60%0A%20%20%20%20-%20%5B%20%5D%20I%20can%20explain%20what%20%60NOT%20IN%60%20does%20when%20the%20list%20contains%20a%20%60NULL%60%0A%20%20%20%20-%20%5B%20%5D%20I%20can%20turn%20a%20nested%20query%20into%20a%20named%20%60WITH%60%20step%20and%20check%20that%20step%20on%20its%20own%0A%0A%20%20%20%20**Part%20D%20builds%20a%20database.**%20It%20covers%20%60CREATE%20TABLE%60%2C%20%60INSERT%60%2C%20loading%20a%20CSV%2C%20and%0A%20%20%20%20running%20SQL%20from%20a%20notebook%2C%20which%20is%20how%20every%20query%20in%20this%20book%20reached%20the%20page.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0Aif%20__name__%20%3D%3D%20%22__main__%22%3A%0A%20%20%20%20app.run()%0A
2e4f6bc602def597014df6f0d187e815