IBM PureData System for Analytics, Version 7.1

Inner join queries

An inner join uses a comparison operator to match rows from two tables that are based on the values in common columns from each table. Inner joins are the most common type of joins.

To pose an inner join query, enter:

MYDB.SCHEMA(USER)=> SELECT * FROM weather INNER JOIN cities ON 
(weather.city = cities.name);

The two columns contain the city name because the weather and the cities tables are concatenated. To avoid the duplication, you can list the output columns explicitly rather than by using an asterisk (*):

MYDB.SCHEMA(USER)=> SELECT city, temp_lo, temp_hi, prcp, date, location
     FROM weather, cities WHERE city = name;

Because the columns all have different names, this example worked as expected. It is good style, however, to fully qualify column names in join queries, as follows:

MYDB.SCHEMA(USER)=> SELECT weather.city, weather.temp_lo, weather.temp_hi, 
weather.prcp, weather.date, cities.location FROM weather, cities WHERE 
cities.name = weather.city;