05 join

%23%20%2F%2F%2F%20script%0A%23%20requires-python%20%3D%20%22%3E%3D3.13%22%0A%23%20dependencies%20%3D%20%5B%0A%23%20%20%20%20%20%22marimo%22%2C%0A%23%20%20%20%20%20%22marimo-learn%3E%3D0.7.0%22%2C%0A%23%20%20%20%20%20%22polars%3D%3D1.24.0%22%2C%0A%23%20%20%20%20%20%22sqlalchemy%22%2C%0A%23%20%5D%0A%23%20%2F%2F%2F%0A%0Aimport%20marimo%0A%0A__generated_with%20%3D%20%220.21.1%22%0Aapp%20%3D%20marimo.App(width%3D%22medium%22)%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_()%3A%0A%20%20%20%20import%20marimo%20as%20mo%0A%20%20%20%20import%20marimo_learn%20as%20mol%0A%20%20%20%20import%20sqlalchemy%0A%0A%20%20%20%20db_path%20%3D%20mol.localize_file(%22lab.db%22)%0A%20%20%20%20DATABASE_URL%20%3D%20f%22sqlite%3A%2F%2F%2F%7Bdb_path%7D%22%0A%20%20%20%20engine%20%3D%20sqlalchemy.create_engine(DATABASE_URL)%0A%20%20%20%20return%20engine%2C%20mo%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_()%3A%0A%20%20%20%20from%20marimo_learn%20import%20LabelingWidget%2C%20OrderingWidget%0A%0A%20%20%20%20return%20LabelingWidget%2C%20OrderingWidget%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%20Combining%20Tables%0A%0A%20%20%20%20Relational%20databases%20get%20their%20name%20from%20the%20fact%20that%20they%20store%20the%20relations%20between%20tables.%20This%20tutorial%20shows%20how%20to%20connect%20and%20combine%20information%20from%20multiple%20tables.%20We%20will%20save%20most%20of%20the%20exercises%20for%20the%20next%20tutorial%2C%20where%20we%20start%20working%20with%20our%20first%20complex%20database.%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%20Basic%20Joins%0A%0A%20%20%20%20The%20%60jobs%60%20database%20has%20two%20tables.%20The%20first%2C%20called%20%60job%60%2C%20shows%20the%20credits%20that%20students%20can%20earn%20doing%20different%20kinds%20of%20jobs%2C%20and%20has%20two%20rows%20and%20two%20columns%3A%0A%0A%20%20%20%20%7C%20name%20%7C%20credits%20%7C%0A%20%20%20%20%7C%20%3A---%20%7C%20------%3A%20%7C%0A%20%20%20%20%7C%20calibrate%20%7C%201.5%20%7C%0A%20%20%20%20%7C%20clean%20%7C%200.5%20%7C%0A%0A%20%20%20%20The%20other%20table%2C%20%60work%60%2C%20keeps%20track%20of%20who%20has%20done%20which%20jobs%3A%0A%0A%20%20%20%20%7C%20person%20%7C%20job%20%7C%0A%20%20%20%20%7C%20%3A-----%20%7C%20%3A--%20%7C%0A%20%20%20%20%7C%20Amal%20%7C%20calibrate%20%7C%0A%20%20%20%20%7C%20Amal%20%7C%20clean%20%7C%0A%20%20%20%20%7C%20Amal%20%7C%20complain%20%7C%0A%20%20%20%20%7C%20Gita%20%7C%20clean%20%7C%0A%20%20%20%20%7C%20Gita%20%7C%20clean%20%7C%0A%20%20%20%20%7C%20Gita%20%7C%20complain%20%7C%0A%20%20%20%20%7C%20Madhi%20%7C%20complain%20%7C%0A%0A%20%20%20%20We%20want%20to%20know%20how%20many%20credits%20each%20student%20has%20earned.%20The%20first%20step%20in%20answering%20this%20is%20to%20**join**%20the%20tables%20together.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(engine%2C%20mo)%3A%0A%20%20%20%20_df%20%3D%20mo.sql(%0A%20%20%20%20%20%20%20%20f%22%22%22%0A%20%20%20%20%20%20%20%20select%20*%0A%20%20%20%20%20%20%20%20from%20job%20join%20work%3B%0A%20%20%20%20%20%20%20%20%22%22%22%2C%0A%20%20%20%20%20%20%20%20engine%3Dengine%0A%20%20%20%20)%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%60join%60%20operation%20creates%20a%20temporary%20table%20in%20memory%20by%20combining%20every%20row%20of%20%60job%60%20with%20every%20row%20of%20%60work%60.%20Since%20%60job%60%20has%20two%20rows%20and%20%60work%60%20has%20seven%2C%20the%20temporary%20table%20has%202%C3%977%3D14%20rows.%0A%0A%20%20%20%20Some%20of%20these%20rows%20are%20useful%3A%20the%20first%2C%20for%20example%2C%20tells%20us%20that%20Amal%20did%20some%20calibration%2C%20and%20that%20calibrating%20is%20worth%201.5%20credits.%20The%20second%2C%20however%2C%20combines%20information%20about%20calibrating%20with%20the%20fact%20that%20Amal%20did%20some%20cleaning.%20We%20can%20get%20rid%20of%20the%20rows%20that%20aren't%20useful%20by%20filtering%20with%20%60where%60.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(engine%2C%20mo)%3A%0A%20%20%20%20_df%20%3D%20mo.sql(%0A%20%20%20%20%20%20%20%20f%22%22%22%0A%20%20%20%20%20%20%20%20select%20*%0A%20%20%20%20%20%20%20%20from%20job%20join%20work%0A%20%20%20%20%20%20%20%20where%20job.name%20%3D%20work.job%3B%0A%20%20%20%20%20%20%20%20%22%22%22%2C%0A%20%20%20%20%20%20%20%20engine%3Dengine%0A%20%20%20%20)%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%20This%20query%20demonstrates%20two%20things%3A%0A%0A%20%20%20%201.%20When%20we%20are%20working%20with%20two%20or%20more%20tables%2C%20we%20refer%20to%20columns%20using%20%60table_name.column_name%60%2C%20as%20in%20%60job.name%60%20or%20%60work.job%60.%20We%20don't%20absolutely%20need%20to%20do%20this%20in%20this%20query%2C%20since%20columns'%20names%20are%20all%20unique%2C%20but%20it's%20very%20common%20to%20have%20columns%20with%20the%20same%20names%20in%20different%20tables.%20In%20those%20cases%20the%20two-part%20names%20are%20required%20to%20avoid%20ambiguity%3B%20it%20is%20therefore%20good%20practice%20to%20*always*%20use%20two-part%20names%20when%20working%20with%20multiple%20tables.%0A%20%20%20%202.%20There%20isn't%20an%20entry%20in%20%60job%60%20for%20%60complain%60%2C%20so%20%60job.name%20%3D%20work.job%60%20isn't%20true%20for%20any%20of%20the%20combined%20rows%20that%20involve%20complaining.%20On%20the%20other%20hand%2C%20Gita%20cleaned%20up%20the%20lab%20twice%2C%20so%20there%20are%20two%20records%20in%20the%20result%20for%20that.%20This%20shows%20that%20%60join%60%20doesn't%20automatically%20remove%20duplicates.%0A%0A%20%20%20%20While%20we%20can%20use%20%60where%60%2C%20the%20SQL%20standard%20encourages%20us%20to%20use%20a%20different%20keyword%20%60on%60%3A%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(engine%2C%20mo)%3A%0A%20%20%20%20_df%20%3D%20mo.sql(%0A%20%20%20%20%20%20%20%20f%22%22%22%0A%20%20%20%20%20%20%20%20select%20*%0A%20%20%20%20%20%20%20%20from%20job%20join%20work%0A%20%20%20%20%20%20%20%20on%20job.name%20%3D%20work.job%3B%0A%20%20%20%20%20%20%20%20%22%22%22%2C%0A%20%20%20%20%20%20%20%20engine%3Dengine%0A%20%20%20%20)%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%20Many%20years%20ago%2C%20using%20%60on%60%20sometimes%20gave%20slightly%20higher%20performance.%20Today%2C%20though%2C%20the%20two%20forms%20are%20equivalent%20from%20the%20database%20manager's%20point%20of%20view.%20Many%20people%20still%20prefer%20%60on%60%20for%20readability%3A%20it%20shows%20how%20the%20rows%20are%20being%20combined%2C%20while%20%60where%60%20shows%20how%20combined%20rows%20are%20being%20filtered.%20As%20with%20almost%20everything%20in%20programming%2C%20what%20matters%20most%20is%20to%20pick%20one%20and%20stick%20to%20it%20so%20that%20your%20queries%20are%20consistent.%0A%0A%20%20%20%20The%20standard%20also%20encourages%20us%20to%20write%20our%20join%20as%20%60inner%20join%60%2C%20because%20as%20we%20will%20see%20in%20a%20moment%2C%20other%20kinds%20of%20joins%20exist.%20People%20often%20skip%20this%20and%20just%20write%20%60join%60%2C%20or%20even%20use%20a%20simple%20comma%20between%20the%20table%20names%2C%20but%20from%20now%20on%20we%20will%20be%20pedantic%20to%20make%20what%20we're%20doing%20clearer.%0A%0A%20%20%20%20We%20are%20now%20able%20to%20answer%20our%20original%20question%3A%20how%20many%20credits%20has%20each%20student%20earned%3F%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(engine%2C%20mo)%3A%0A%20%20%20%20_df%20%3D%20mo.sql(%0A%20%20%20%20%20%20%20%20f%22%22%22%0A%20%20%20%20%20%20%20%20select%20work.person%2C%20sum(job.credits)%20as%20total%20%20%20%20%20--%20add%20up%20the%20credits%20for%20each%20person%0A%20%20%20%20%20%20%20%20from%20job%20inner%20join%20work%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--%20notice%3A%20inner%20join%0A%20%20%20%20%20%20%20%20on%20job.name%20%3D%20work.job%0A%20%20%20%20%20%20%20%20group%20by%20work.person%3B%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--%20put%20all%20the%20credits%20for%20each%20person%20into%20a%20separate%20bucket%0A%20%20%20%20%20%20%20%20%22%22%22%2C%0A%20%20%20%20%20%20%20%20engine%3Dengine%0A%20%20%20%20)%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%20Left%20Joins%0A%0A%20%20%20%20The%20query%20above%20shows%20us%20how%20many%20credits%20Amal%20and%20Gita%20have%20earned%2C%20but%20doesn't%20show%20anything%20for%20Madhi.%20Ideally%2C%20we'd%20like%20a%20row%20showing%20that%20she%20has%20earned%20zero%20credits.%20To%20get%20this%2C%20we%20need%20to%20use%20a%20different%20kind%20of%20join%20called%20a%20**left%20join**.%20A%20left%20join%20is%20created%20by%20following%20these%20rules%3A%0A%0A%20%20%20%201.%20If%20the%20row%20from%20the%20left-hand%20table%20matches%20one%20or%20more%20rows%20from%20the%20right-hand%20table%2C%20combine%20them%20as%20an%20inner%20join%20would.%0A%20%20%20%202.%20If%20the%20row%20from%20the%20left-hand%20table%20_doesn't_%20match%20any%20rows%20from%20the%20right-hand%20table%2C%20create%20one%20row%20in%20the%20result%20with%20the%20values%20from%20the%20left%20row%20and%20%60null%60%20where%20the%20values%20from%20the%20right-hand%20table%20would%20be.%0A%0A%20%20%20%20An%20example%20will%20make%20this%20clearer.%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(engine%2C%20mo)%3A%0A%20%20%20%20_df%20%3D%20mo.sql(%0A%20%20%20%20%20%20%20%20f%22%22%22%0A%20%20%20%20%20%20%20%20select%20*%0A%20%20%20%20%20%20%20%20from%20work%20left%20join%20job%0A%20%20%20%20%20%20%20%20on%20work.job%20%3D%20job.name%3B%0A%20%20%20%20%20%20%20%20%22%22%22%2C%0A%20%20%20%20%20%20%20%20engine%3Dengine%0A%20%20%20%20)%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%20Let's%20trace%20this%20query's%20execution%20step%20by%20step%3A%0A%0A%20%20%20%201.%20The%20%60(Amal%2C%20calibrate)%60%20row%20from%20%60work%60%20matches%20the%20%60(calibrate%2C%201.5)%60%20row%20from%20%60job%60%2C%20so%20that%20is%20the%20first%20row%20of%20output.%0A%20%20%20%202.%20Similarly%2C%20the%20%60(Amal%2C%20clean)%60%20row%20matches%20the%20%60(clean%2C%200.5)%60%20row%2C%20so%20we%20get%20the%20second%20row%20of%20output.%0A%20%20%20%203.%20But%20%60(Amal%2C%20complain)%60%20_doesn't_%20match%20anything%20from%20%60job%60%2C%20so%20we%20get%20a%20row%20with%20the%20values%20from%20the%20left%20table%20(%60Amal%60%20and%20%60complain%60)%20and%20%60null%60%20for%20%60name%60%20and%20%60work%60.%0A%20%20%20%204.%20We%20then%20get%20two%20rows%20for%20Gita%20cleaning%20because%20there's%20a%20match%E2%80%A6%0A%20%20%20%205.%20%E2%80%A6and%20two%20rows%20with%20%60null%60%20values%20for%20Gita%20and%20Madhi%20complaining%20because%20there%20isn't.%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%3E%20What%20do%20we%20get%20if%20we%20invert%20the%20order%20of%20the%20tables%2C%20i.e.%2C%20do%20%60job%20left%20join%20work%60%3F%20Why%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%23%23%20Coalesce%0A%0A%20%20%20%20We%20can%20now%20sum%20up%20everyone's%20credits%3A%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(engine%2C%20mo)%3A%0A%20%20%20%20_df%20%3D%20mo.sql(%0A%20%20%20%20%20%20%20%20f%22%22%22%0A%20%20%20%20%20%20%20%20select%20work.person%2C%20sum(job.credits)%20as%20total%0A%20%20%20%20%20%20%20%20from%20work%20left%20join%20job%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--%20notice%3A%20left%20join%0A%20%20%20%20%20%20%20%20on%20work.job%20%3D%20job.name%0A%20%20%20%20%20%20%20%20group%20by%20work.person%3B%0A%20%20%20%20%20%20%20%20%22%22%22%2C%0A%20%20%20%20%20%20%20%20engine%3Dengine%0A%20%20%20%20)%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%20This%20is%20*almost*%20what%20we%20want%3A%20we%20have%20a%20row%20for%20Madhi%2C%20but%20her%20%60total%60%20is%20%60null%60%20because%20that's%20what%20%60sum%60%20produces%20when%20all%20of%20the%20values%20it's%20adding%20up%20are%20%60null%60.%20We%20can%20fix%20this%20using%20a%20built-in%20SQL%20function%20called%20%60coalesce%60%3A%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell%0Adef%20_(engine%2C%20mo)%3A%0A%20%20%20%20_df%20%3D%20mo.sql(%0A%20%20%20%20%20%20%20%20f%22%22%22%0A%20%20%20%20%20%20%20%20select%0A%20%20%20%20%20%20%20%20%20%20%20%20work.person%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20coalesce(sum(job.credits)%2C%200)%20as%20total%0A%20%20%20%20%20%20%20%20from%0A%20%20%20%20%20%20%20%20%20%20%20%20work%20left%20join%20job%0A%20%20%20%20%20%20%20%20on%0A%20%20%20%20%20%20%20%20%20%20%20%20work.job%20%3D%20job.name%0A%20%20%20%20%20%20%20%20group%20by%0A%20%20%20%20%20%20%20%20%20%20%20%20work.person%3B%0A%20%20%20%20%20%20%20%20%22%22%22%2C%0A%20%20%20%20%20%20%20%20engine%3Dengine%0A%20%20%20%20)%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%60coalesce%60%20takes%20two%20inputs.%20If%20the%20first%20is%20not%20%60null%60%2C%20%60coalesce%60%20returns%20that.%20If%20the%20first%20value%20*is*%20%60null%60%2C%20on%20the%20other%20hand%2C%20%60coalesce%60%20returns%20its%20second%20input.%20In%20simpler%20terms%2C%20it%20gives%20us%20a%20value%20or%20a%20default%20if%20the%20value%20is%20%60null%60.%0A%0A%20%20%20%20Note%20that%20we%20have%20split%20this%20query%20across%20several%20lines%20with%20the%20keywords%20at%20the%20left%20margin%20and%20the%20parts%20of%20the%20query%20that%20belong%20to%20them%20indented%20below%20them.%20As%20our%20queries%20become%20more%20complex%2C%20this%20style%20makes%20them%20easier%20to%20read.%20As%20with%20%60join%60%20versus%20%60inner%20join%60%2C%20the%20most%20important%20thing%20is%20to%20be%20consistent%20so%20that%20the%20reader%20isn't%20distracted%20by%20stylistic%20differences.%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%20Check%20Understanding%0A%0A%20%20%20%20!%5Bconcept%20map%5D(%2Fpublic%2F05_concepts.svg)%0A%20%20%20%20%22%22%22)%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(OrderingWidget%2C%20mo)%3A%0A%20%20%20%20_widget%20%3D%20mo.ui.anywidget(%0A%20%20%20%20%20%20%20%20OrderingWidget(%0A%20%20%20%20%20%20%20%20%20%20%20%20question%3D%22Arrange%20the%20steps%20SQL%20follows%20when%20executing%20an%20INNER%20JOIN.%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20items%3D%5B%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22Combine%20every%20row%20from%20the%20left%20table%20with%20every%20row%20from%20the%20right%20table%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22Apply%20the%20ON%20condition%20to%20keep%20only%20matching%20row%20pairs%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22Apply%20any%20WHERE%20clause%20to%20filter%20the%20matched%20rows%20further%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22Apply%20SELECT%20to%20return%20only%20the%20requested%20columns%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%5D%2C%0A%20%20%20%20%20%20%20%20)%0A%20%20%20%20)%0A%20%20%20%20_widget%0A%20%20%20%20return%0A%0A%0A%40app.cell(hide_code%3DTrue)%0Adef%20_(LabelingWidget%2C%20mo)%3A%0A%20%20%20%20_widget%20%3D%20mo.ui.anywidget(%0A%20%20%20%20%20%20%20%20LabelingWidget(%0A%20%20%20%20%20%20%20%20%20%20%20%20question%3D%22Drag%20each%20label%20to%20the%20line%20of%20the%20query%20it%20best%20describes.%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20labels%3D%5B%22left%20table%22%2C%20%22right%20table%22%2C%20%22join%20condition%22%2C%20%22fallback%20for%20null%22%5D%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20text_lines%3D%5B%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22from%20work%20left%20join%20job%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22on%20work.job%20%3D%20job.name%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22coalesce(sum(job.credits)%2C%200)%20as%20total%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%5D%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20correct_labels%3D%7B0%3A%20%5B0%2C%201%5D%2C%201%3A%20%5B2%5D%2C%202%3A%20%5B3%5D%7D%2C%0A%20%20%20%20%20%20%20%20)%0A%20%20%20%20)%0A%20%20%20%20_widget%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