Outer Joins List the dates that each Some queries that join tables require that all the records of employee travelled one table (usually the table) be displayed even if parent there are no matching (child) records. Display number of Examples donations made by each List the total sales for all sales people this month. List donor. sales people even if they haven't sold anything yet. List all students' scores on an assignment, even those First use a Join. that haven't submitted it yet. Note Volker Gaul missing For the rows where the parent table doesn't have matching and Adam Aamond child records, the data for the (missing) child record will appear as NULL Use Outer Join Outer joins come in three flavors Note Volker Gaul value is Left Join (note: the word isn't used) outer Null Parent table is listed first (on the left) in the join Adam Aamond is 0 Right Join Parent table is listed second (on the right) in the Limit the results to only join include trips taken to Full Join Chicago (continue to show Not supported by MySQL all employees) Non-matching records from BOTH tables are (use and, not where) included Why would there be child records without a Donations in 2009 only. parent? That's the point—find the child records without a parent (orphan records) The parent table doesn't have to be listed as the primary table (the child can be), but normally, Left or Right designates where the parent is (which table should include all records). If you want to add a criteria to an outer join, you MUST use an clause after the clause, not in order to AND ON WHERE show all records from the outer join table. Using cannot be combined with And
Outer Joins Some queries that join tables require that all the records of one table (usually the parent table) be displayed even if there are no matching (child) records. Examples List the total sales for all sales people this month. List sales people even if they haven't sold anything yet. List all students' scores on an assignment, even those that haven't submitted it yet. For the rows where the parent table doesn't have matching child records, the data for the (missing) child record will appear as NULL Outer joins come in three flavors Left Join (note: the word outer isn't used) Parent table is listed first (on the left) in the join Right Join Parent table is listed second (on the right) in the join Full Join Not supported by MySQL Non-matching records from BOTH tables are included Why would there be child records without a parent? That's the point—find the child records without a parent (orphan records) The parent table doesn't have to be listed as the primary table (the child can be), but normally, Left or Right designates where the parent is (which table should include all records). If you want to add a criteria to an outer join, you MUST use an AND clause after the ON clause, not WHERE in order to show all records from the outer join table. Using cannot be combined with And List the dates that each employee travelled Display number of donations made by each donor. First use a Join. Note Volker Gaul missing and Adam Aamond Use Outer Join Note Volker Gaul value is Null Adam Aamond is 0 Limit the results to only include trips taken to Chicago (continue to show all employees) (use and, not where) Donations in 2009 only.
4%20-%20Multi-Table%20Queries.docx