(The joins we have seen so far are inner joins.) The command looks like this:įROM weather LEFT OUTER JOIN cities ON (weather.city = cities.name) Ĭity | temp_lo | temp_hi | prcp | date | name | location This kind of query is called an outer join. If no matching row is found we want some “ empty values” to be substituted for the cities table's columns. What we want the query to do is to scan the weather table and for each row to find the matching cities row(s). Now we will figure out how we can get the Hayward records back in. This syntax is not as commonly used as the one above, but we show it here to help you understand the following topics. Join queries of the kind seen thus far can also be written in this alternative form:įROM weather INNER JOIN cities ON (weather.city = cities.name) It is widely considered good style to qualify all column names in a join query, so that the query won't fail if a duplicate column name is later added to one of the tables. Weather.prcp, weather.date, cities.location SELECT weather.city, weather.temp_lo, weather.temp_hi, If there were duplicate column names in the two tables you'd need to qualify the column names to show which one you meant, as in: Since the columns all had different names, the parser automatically found which table they belong to. SELECT city, temp_lo, temp_hi, prcp, date, locationĮxercise: Attempt to determine the semantics of this query when the WHERE clause is omitted. In practice this is undesirable, though, so you will probably want to list the output columns explicitly rather than using *: This is correct because the lists of columns from the weather and cities tables are concatenated. There are two columns containing the city name. We will see shortly how this can be fixed. This is because there is no matching entry in the cities table for Hayward, so the join ignores the unmatched rows in the weather table. There is no result row for the city of Hayward.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |