not in<\/em><\/strong> the list of boats that have been reserved<\/p>\n\n\n\nJust as with sailors, we need to generate a list of boat id’s for the inner query. We must return boats that are red AND are not in that list of ids. So the WHERE clause will have two tests in it–one for color and one for non-participation with NOT IN.<\/p>\n\n\n\n
SELECT bname\nFROM Boats\nWHERE color = 'red' AND bid NOT IN (SELECT bid\n FROM Reserves)<\/pre>\n\n\n\nNow we may deal with information need #3, finding sailors that have not reserved a red boat. There’s nothing new about this one. We can rewrite the information need either literally or in our imagination and then translate that into SQL.<\/p>\n\n\n\n
Rewritten information need: <\/strong>Find the names of sailors that are not in the list of sailors that have reserved a red boat.<\/p>\n\n\n\nWe need to generate the list of id’s of sailors that have reserved red boats. For this list, we must use the Reserves table and the Boats table because we need sid’s of sailors with reservations and colors of boats.<\/p>\n\n\n\n
SELECT sname\nFROM Sailors\nWHERE sid NOT IN (SELECT sid\n FROM Reserves R INNER JOIN Boats B\n ON R.bid = B.bid\n WHERE color = 'red')<\/pre>\n\n\n\nOuter Join<\/h2>\n\n\n\n There is another way to write each of these non-participation queries that was mentioned above. We can use special type of join called an OUTER JOIN<\/strong>. So far we have seen the INNER JOIN<\/strong> operation which combines two tables on the left and right side of INNER JOIN by matching up rows from each table that satisfy the join condition. The rows satisfying the join condition from either table are the rows that participate in the join. Any rows from either table that don’t show up in the result do not participatel they are non-participating rows. An OUTER JOIN is a similar operation that can be thought of as “INNER JOIN + non-participating rows”.<\/p>\n\n\n\nInformation need: Find the names of sailors that have reserved only red boats.<\/p>\n\n\n\n
This one doesn’t have “not” in it, but it’s still very similar because “only red” also means “not any other colors”. We have to recognize that the above query returns sailors who also have no reservations (because they are not in the list of sailors who have reserved red boats), so if we modify the outer query to only check for sailors with reservations not being in the list, we will have it:<\/p>\n\n\n\n
SELECT sname\nFROM Sailors S INNER JOIN Reserves R1 ON S.sid = R1.sid\nWHERE S.sid NOT IN (SELECT sid\n FROM Reserves R2 INNER JOIN Boats B \n ON R2.bid = B.bid\n WHERE B.color <> 'red')<\/pre>\n\n\n\nAt this point, we can speed through queries like \u201cFind sailors by name that have reserved a green boat but not a red boat.\u201d We specialize the outer query to deal with reservations for green boats and keep the inner query the same:<\/p>\n\n\n\n
SELECT sname\nFROM Sailors S INNER JOIN Reserves R1 ON S.sid = R1.sid\n INNER JOIN Boats B1ON R1.bid = B1.bid \nWHERE B1.color = 'green' AND \n S.sid NOT IN (SELECT sid\n FROM Reserves R2 INNER JOIN Boats B2 \n ON R2.bid = B2.bid\n WHERE B.color <> 'red')<\/pre>\n\n\n\nThere\u2019s another kind of query where the word may not be immediately obvious, but it\u2019s lurking in the background. Here\u2019s an example: \u201cFind the names of all sailors, and if a sailor has reserved a boat, also include the ids of every boat they have reserved.\u201d Uh-oh. This ones kind of tricky. We\u2019re seemingly being asked for two things here. One is to return all<\/em> sailors, even those that have not reserved a boat. The other is to match up sailors with the ids of boats they’ve reserved. The sailors that have reserved boats participate in the join with reserves, but those that never have do not. Here we need both kinds of sailors.<\/p>\n\n\n\nThere\u2019s a special group of operations called an OUTER JOINs that return both participating and non-participating rows. There are in fact three of these OUTER JOINs, left outer joins (LEFT JOIN), right outer joins (RIGHT JOIN), and full outer joins (not actually available in the DBMS we use). What they all have in common is that they give us the INNER JOIN results we’re used to by now plus information about the rows that do not participate in joins.<\/p>\n\n\n\n
Remember that the FROM clause with an INNER JOIN looks like<\/p>\n\n\n\n
FROM LeftTable INNER JOIN RightTable ON ...<\/pre>\n\n\n\nThe different kinds of outer join depend on whether the table on the left, the right, or both the table on the left and the one on the right are treated specially. In a LEFT JOIN, the table on the left (the first table) is treated differently. Every row from that table will be in the query results whether or not it participates in the join. The syntax is otherwise just like INNER JOIN with the join condition following the ON keyword.<\/p>\n\n\n\n
SELECT *\nFROM Sailors S LEFT JOIN Reserves R ON S.sid = R.sid<\/pre>\n\n\n\nsid<\/th> sname<\/th> rating<\/th> age<\/th> sid<\/th> bid<\/th> date<\/th><\/tr><\/thead> 22<\/td> Dustin<\/td> 7<\/td> 45.0<\/td> 22<\/td> 101<\/td> Nov. 29, 2012<\/td><\/tr> 22<\/td> Dustin<\/td> 7<\/td> 45.0<\/td> 22<\/td> 102<\/td> Nov. 29, 2012<\/td><\/tr> 22<\/td> Dustin<\/td> 7<\/td> 45.0<\/td> 22<\/td> 103<\/td> Nov. 29, 2012<\/td><\/tr> 22<\/td> Dustin<\/td> 7<\/td> 45.0<\/td> 22<\/td> 104<\/td> Nov. 29, 2012<\/td><\/tr> 29<\/td> Brutus<\/td> 1<\/td> 33.0<\/td> NULL<\/td> NULL<\/td> NULL<\/td><\/tr> 31<\/td> Lubber<\/td> 8<\/td> 55.5<\/td> 31<\/td> 102<\/td> Nov. 29, 2012<\/td><\/tr> 31<\/td> Lubber<\/td> 8<\/td> 55.5<\/td> 31<\/td> 103<\/td> Nov. 29, 2012<\/td><\/tr> 31<\/td> Lubber<\/td> 8<\/td> 55.5<\/td> 31<\/td> 104<\/td> Nov. 29, 2012<\/td><\/tr> 32<\/td> Andy<\/td> 8<\/td> 25.5<\/td> NULL<\/td> NULL<\/td> NULL<\/td><\/tr> 58<\/td> Rusty<\/td> 10<\/td> 35.0<\/td> NULL<\/td> NULL<\/td> NULL<\/td><\/tr> 64<\/td> Horatio<\/td> 7<\/td> 35.0<\/td> 64<\/td> 101<\/td> Nov. 29, 2012<\/td><\/tr> 64<\/td> Horatio<\/td> 7<\/td> 35.0<\/td> 64<\/td> 102<\/td> Nov. 29, 2012<\/td><\/tr> 71<\/td> Zorba<\/td> 10<\/td> 16.0<\/td> NULL<\/td> NULL<\/td> NULL<\/td><\/tr> 74<\/td> Horatio<\/td> 9<\/td> 35.0<\/td> 74<\/td> 103<\/td> Nov. 29, 2012<\/td><\/tr> 85<\/td> Art<\/td> 3<\/td> 25.5<\/td> NULL<\/td> NULL<\/td> NULL<\/td><\/tr> 95<\/td> Bob<\/td> 3<\/td> 63.5<\/td> NULL<\/td> NULL<\/td> NULL<\/td><\/tr><\/tbody><\/table>The results of Sailors LEFT JOIN Reserves include all sailors that have reserved a boat along with those that haven’t. <\/figcaption><\/figure>\n\n\n\nA RIGHT JOIN is similar except the non-participating rows from the right table (the second table) are included along with the INNER JOIN results. In a full outer join, non-participating rows from both tables would be included. Not every database includes all three joins. MySQL, for instance includes LEFT JOIN and RIGHT JOIN but not a full outer join, and Microsoft Access includes only LEFT JOIN. The lack of RIGHT JOIN is not a serious issue, though, for one just need to remember to always write the “special” table first and use a LEFT JOIN whenever one of these outer joins is needed.<\/p>\n\n\n\n
If you\u2019re keeping up, you\u2019ll go \u201cwait a minute. If the row from a table doesn\u2019t participate in a join, then there’s no row from the other table to match it up with. What about the columns from the other table that appear in the result?\u201d You\u2019re right about that, and that\u2019s why the outer join is special. SQL creates null values for every column in the other table to \u201cfill in\u201d the missing spaces in the results returned by an outer join. So, for instance, for Sailors LEFT JOIN Reserves, all three Reserves table columns would be full of NULL values for Sailors that have not reserved a boat.<\/p>\n\n\n\n
This property gives us another way to write non-participation queries.<\/p>\n\n\n\n
Information Need: Find the names of sailors that have not reserved a boat.<\/p>\n\n\n\n
SELECT sname\nFROM Sailors S LEFT JOIN Reserves R ON S.sid = R.sid\nWHERE R.sid IS NULL<\/pre>\n\n\n\nIn this query above, we could have used any column from Reserves to test for NULL. Using the foreign key in the join condition is a good choice, though, for it makes our intention clear to only retrieve non-participating rows from the Sailors table.<\/p>\n\n\n\n
Information Need: <\/strong>Find the names of all sailors, and if a sailor has reserved a boat, also include the ids of every boat they have reserved.<\/p>\n\n\n\nSo back to the problematic information need that started the coverage of outer joins. We can use an outer join to construct queries where you are asked for multiple pieces of information, but some of those are \u201coptional\u201d and may not appear in the answer. The names of sailors are mandatory but the ids of boats are optional. We need to include both those columns in the SELECT clause, and the optional ones may sometimes have NULL values. That’s OK. Since we want all the sailors, participating and non-participating ones, we don’t need to filter by participation in the WHERE clause. In fact, we don’t need to filter at all, so the WHERE clause can be omitted.<\/p>\n\n\n\n
SELECT sname, bid\nFROM Sailors S LEFT JOIN Reserves R ON S.sid = R.sid<\/pre>\n\n\n\nBefore you read further, go ahead and try to write SQL for this next one where the id of boats has been changed to the names.<\/p>\n\n\n\n
Information Need: <\/strong> Find the names of all sailors, and if a sailor has reserved a boat, also include the names of every boat they have reserved.<\/p>\n\n\n\nThe obvious approach is to recognize the boat names are in the Boats table and add an INNER JOIN with Boats, too, because that’s what we’re used to doing.<\/p>\n\n\n\n
SELECT sname, bid\nFROM Sailors S LEFT JOIN Reserves R ON S.sid = R.sid\n INNER JOIN Boats B ON R.bid = B.bid<\/pre>\n\n\n\nIf you execute this query, you’ll see that only the participating sailors (those that have reserved a boat) apper. The reason for this is that rows for non-participating sailors will have a NULL value for R.bid, and the join condition can’t be satisfied when R.bid IS NULL. So only rows with non-NULL R.bid values can evaluate to TRUE. This is subtle, but it’s important to understand that joining multiple tables when outer joins are involved requires care. The solution is to first join the Reserves and Boats tables to ensure every reservation can be matched up with its boat. The table resulting from that join can then be outer joined with the Sailors table. There are two ways to make this happen. One is to add () to the query above to ensure the INNER JOIN occurs first. This addition of () is analogous to writing 2 + (3 + 4) on a calculator to force the addition of 3 and 4 to happen before the first term gets added.<\/p>\n\n\n\n
SELECT sname, bname\nFROM Sailors S LEFT JOIN (Reserves R INNER JOIN Boats B ON R.bid = B.bid) ON S.sid = R.sid\n<\/pre>\n\n\n\nThe other solution is to remember that RIGHT JOIN is a thing. We can put the Sailors table after<\/em> the join operators that way and not have to worry about ().<\/p>\n\n\n\nSELECT sname, bname\nFROM Reserves R INNER JOIN Boats B ON R.bid = B.bid \n RIGHT JOIN Sailors S ON R.sid = S.sid\n<\/pre>\n","protected":false},"excerpt":{"rendered":"Queries with the word \u201cnot\u201d in it often signify that we need to carefully think about our logic. Consider these information needs. Find the names of sailors that have not …<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"featured_image_src":null,"featured_image_src_square":null,"_links":{"self":[{"href":"https:\/\/enter77.ius.edu\/cjkimmer\/wp-json\/wp\/v2\/pages\/54"}],"collection":[{"href":"https:\/\/enter77.ius.edu\/cjkimmer\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/enter77.ius.edu\/cjkimmer\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/enter77.ius.edu\/cjkimmer\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/enter77.ius.edu\/cjkimmer\/wp-json\/wp\/v2\/comments?post=54"}],"version-history":[{"count":8,"href":"https:\/\/enter77.ius.edu\/cjkimmer\/wp-json\/wp\/v2\/pages\/54\/revisions"}],"predecessor-version":[{"id":433,"href":"https:\/\/enter77.ius.edu\/cjkimmer\/wp-json\/wp\/v2\/pages\/54\/revisions\/433"}],"wp:attachment":[{"href":"https:\/\/enter77.ius.edu\/cjkimmer\/wp-json\/wp\/v2\/media?parent=54"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}