Getting ready

You'll need at least two layers in either a SpatiaLite or PostGIS database. These two layers need at least one column in common, and the column in common should contain unique values in at least one table. In this case, our example uses the census_wake_2000 polygon layer and census_wake_2000_pop.csv.

How to do it…

How it works…

SELECT lists all the columns that you want from the source tables; in this case, * means everything. FROM is the first (left) table, as a is an alias, which is used so that there's less typing later. JOIN is the second (right) table, and ON indicates which columns to should be matched between the two tables. The rest of how this works in relational database theory is best explained in other texts.

There's more…

In databases, there's more than one type of join. You can perform a join where you retain only the matches in both tables, or you can retain all content from the left (first table) and any matches from the right. You can also control how a one-to-many relationship is summarized or select specific records instead of aggregating.

If you want to save the results of a query you have two options. You can make a view or a new table. A view is a saved copy of your query. Every time you open it, the query will be rerun. This is great if your data changes because it will always be up-to-date, and this doesn't use any additional disk space. On the other hand, a table is like saving a new file; it becomes a static new copy of the results. This is good to repeatedly access the same answer, and it is usually faster to use, especially for large tables.

See also