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.
Subqueries A query can be based on the results of an embedded query. The embedded query (SQL command) is analyzed first and then the primary query uses those results to obtain its answer. The embedded query is contained in the primary query’s Where clause (surrounded by parenthesis). The embedded query is evaluated first, creating a temporary table of records. The main query then processes, using the temporary query results as its row criteria. Subqueries can only return the values from one column. Queries can be nested to as many levels as necessary (query inside a query, inside a query…) One common use of subqueries is to determine which records are related to a statistic (MIN, MAX, etc). ”Determine which customer has the highest credit limit.” ”List the students who scored lower than the average on the test.” Select custfirst, custlast From tblCustomer Where creditlimit = (Select Max(creditlimit) From tblCustomer) Select studentfirst, studentlast From tblTest Where score < (Select Avg(score) From tblTest) Show the employee who has the highest salary. Show the donor name who made the largest donation (2 nested queries)
Intersect MySQL does not support Intersect. Use a subquery. The Intersect operator returns only those records that appear in both temporary tables. Normally, the query requirements will include word “AND”. Duplicate using an In subquery List employees who have traveled to both Chicago and New York (Note: cannot be done using just a join) Minus MySQL does not support Minus. Use a subquery. The Minus operator returns the records from the first query with any matching records from the second query removed. Normally, the query requirements will include the words “BUT NOT” Duplicate using a Not In subquery List employees who have traveled to Chicago but not New York Any and All Any and All allow you to create queries that compare a value in a record with a list of values supplied by a subquery. As far as I can tell, Any and All queries can alternatively be accomplished using (Select Min(value) From table) or Max(Value) subqueries appropriately Examples: