Putting the 'Relational' in 'Relational Databases'
Join gives us all the fields from all the combinations of rows that match the ON clause
A join of three tables can be accomplished with:
SELECT * FROM country c_1 JOIN city ct ON c_1.code = ct.countrycode, country c_2 JOIN countrylanguage cl ON c_2.code = cl.countrycode
To join with a temporary table created from a WITH clause, use the name assigned to the temporary table. For example:
WITH populated_countries AS ( SELECT * FROM country WHERE population > 0 ) SELECT * FROM country c_1 JOIN populated_countries c ON c_1.code = c.code
The difference between
ON is the more general of the two. You can join tables
ON a column, a set of columns and even a condition. For example:
SELECT * FROM country c JOIN city cy ON ( cy.countrycode = c.code) JOIN countrylanguages cl ON (cl.countrycode = c.code) WHERE ...
USING is useful when both tables share a column of the exact same name on which they join. In this case, you can:
SELECT * FROM customer cust JOIN rentals ren USING (customer_id) JOIN inventory inv USING (inventory_id) JOIN films fil USING (film_id) JOIN film_categories fil_cat USING (film_id) JOIN category cat USING (category_id) WHERE ...
In our countries database, how would we list all countries along with their capital cities?
What happens if some countries don't have a capital city record?
We can look for sets that specifically are missing the right side of a join
Start by making a picture showing the tables of the database and how they relate (which columns represent the same information).
NOTE: You don't actually need a join to get this information - try writing a query for this information once without a join, and once with.
WITHto get the countries and join with that table
WITHto get the countries, and
SELECT DISTINCTto remove duplicates
NOT INwith a
WHERE ... NOT IN ( SELECT ... FROM ... WHERE ...)
OUTER JOIN or
Today's Tentative Schedule
9:15am - Stand Up
9:30am - BDD with Rails
11:00am - Challenge: Car Accelerator with testing
12:00 noon - Lunch
1:00pm - continue with Car Accelerator Challenge with testing
4.30pm - Review
5:00pm - Class Ends