The SQL Language (Part-3)

Joins Between Tables

With the JOIN function, we can access the multiple table at once or access the same table in such a way that multiple rows of the table are being processed at the same time.
Queries that access multiple tables at one time are called join queries. They combine rows from one table with rows from one table with rows from a second table, with expression specifying which rows are to be paired.

For Example:

SELECT * FROM weather JOIN cities ON city = name;

The Output will be:
city | temp_lo | temp_hi | prcp | date | name
| location

San Francisco | 46 | 50 | 0.25 | 1994-11-27 | San Francisco
| (-194,53)
San Francisco | 43 | 57 | 0 | 1994-11-29 | San Francisco
| (-194,53)
(2 rows)

There is no result row for the city of Hayward. 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. We will see shortly how this can be fixed.

There are two coloumns containing the city name. This is correct because this lists of coloumns from the weather and cities tables are concatenated. In practice this is undesirable, through, so you will probably want to list the outputs columns explicitly rather than using.

SELECT city, temp_lo, temp_hi, prcp, date, location
 FROM weather JOIN cities ON city = name;

Since the column all had different names, the parser automatically found which table they belong to. If there were duplicate columns names in the two tables you would need to qualify the columns names to show which one you meant, as in

SELECT weather.city, weather.temp_lo, weather.temp_hi,
 weather.prcp, weather.date, cities.location
 FROM weather 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.

We will figure out how we can get the Hayward records back in, What we want the query to do is to scan the weather table and for each row to find the matching cities row. If no matching found we will use "empty calues" to substitute for cities table's columns. This kind of query is called an outer join.

SELECT *
 FROM weather LEFT OUTER JOIN cities ON weather.city = cities.name;

city | temp_lo | temp_hi | prcp | date | name
| location

Hayward | 37 | 54 | | 1994-11-29 |
|
San Francisco | 46 | 50 | 0.25 | 1994-11-27 | San Francisco
| (-194,53)
San Francisco | 43 | 57 | 0 | 1994-11-29 | San Francisco
| (-194,53)
(3 rows)

This is called left outer join because the table mentioned on teh left of the join operator will have each of its rows in the output at least once.

There are also right outer joins and full outer joins.

We can also join a table against itself. This is called self join. As an example, suppose we wish to find all the weather records that are in the temperature range of other weather records.

SELECT w1.city, w1.temp_lo AS low, w1.temp_hi AS high,
 w2.city, w2.temp_lo AS low, w2.temp_hi AS high
 FROM weather w1 JOIN weather w2
 ON w1.temp_lo < w2.temp_lo AND w1.temp_hi > w2.temp_hi;

city | low | high | city | low | high

San Francisco | 43 | 57 | San Francisco | 46 | 50
Hayward | 37 | 54 | San Francisco | 46 | 50
(2 rows)

We can relable the weather table as w1 and w2 to be able to distinguish the left and right side of join.

SELECT *
 FROM weather w JOIN cities c ON w.city = c.name;