Databases typically contain multiple tables, and queries can require information contained within different tables to compute a desired result. For instance, we may have a second table describing the mean daily high temperature of different cities.

sqlite> create table temps as
       ...>   select "Berkeley" as city, 68 as temp union
       ...>   select "Chicago"         , 59         union
       ...>   select "Minneapolis"     , 55;
    

Data are combined by joining multiple tables together into one, a fundamental operation in database systems. There are many methods of joining, all closely related, but we will focus on just one method in this text. When tables are joined, the resulting table contains a new row for each combination of rows in the input tables. If two tables are joined and the left table has $m$ rows and the right table has $n$ rows, then the joined table will have $m \cdot n$ rows. Joins are expressed in SQL by separating table names by commas in the from clause of a select statement.

sqlite> select * from cities, temps;
    38|122|Berkeley|Berkeley|68
    38|122|Berkeley|Chicago|59
    38|122|Berkeley|Minneapolis|55
    42|71|Cambridge|Berkeley|68
    42|71|Cambridge|Chicago|59
    42|71|Cambridge|Minneapolis|55
    45|93|Minneapolis|Berkeley|68
    45|93|Minneapolis|Chicago|59
    45|93|Minneapolis|Minneapolis|55
    

Joins are typically accompanied by a where clause that expresses a relationship between the two tables. For example, if we wanted to collect data into a table that would allow us to correlate latitude and temperature, we would select rows from the join where the same city is mentioned in each. Within the cities table, the city name is stored in a column called name. Within the temps table, the city name is stored in a column called city. The where clause can select for rows in the joined table in which these values are equal. In SQL, numeric equality is tested with a single = symbol.

sqlite> select name, latitude, temp from cities, temps where name = city;
    Berkeley|38|68
    Minneapolis|45|55
    

Tables may have overlapping column names, and so we need a method for disambiguating column names by table. A table may also be joined with itself, and so we need a method for disambiguating tables. To do so, SQL allows us to give aliases to tables within a from clause using the keyword as and to refer to a column within a particular table using a dot expression. The following select statement computes the temperature difference between pairs of unequal cities. The alphabetical ordering constraint in the where clause ensures that each pair will only appear once in the result.

sqlite> select a.city, b.city, a.temp - b.temp
       ...>        from temps as a, temps as b where a.city < b.city;
    Berkeley|Chicago|10
    Berkeley|Minneapolis|15
    Chicago|Minneapolis|5
    

Our two means of combining tables in SQL, join and union, allow for a great deal of expressive power in the language.