Week 5 - Database design

Relational Databases

Primary Keys and Foreign Keys

Constraints

Database normalization

PostgreSQL setup

Joins

Java and databases

Practice

Assignment

• Teachers

Back end Track

Table joins

Now that we have designed and set up a normalized database, let’s consider a bit more how we can get information out of it. With all the table relations that are introduced, we need to start making more use of joins to produce more meaningful results with our queries.

You studied joins before in in week 12 of the core program. Here’s another (slightly funny) video explaining joins:

https://www.youtube.com/watch?v=9yeOJ0ZMUYw

Let’s see how we can use joins on our “Postify” database.

Postify schema

Say you are interested in knowing which albums all Dutch artists have released. From the schema you can tell you need information from both the artists table (for the artist_country column) and of course from the album table. SQL provides you with a tool to combine data from two tables: joins.

A join is an instruction to the database engine to combine rows from two tables by matching a column in one table against a column in the other. The matching column is almost always a foreign key pointing at a primary key. Here’s an example (with some random example output):

SELECT artists.artist_country,
       artists.artist_name,
       albums.album_title
FROM   artists
INNER JOIN albums
  ON artists.artist_id = albums.artist_id
WHERE artists.artist_country = 'NL';

Example output:

This query JOINs the tables artists and albums on their foreign key relation artist_id , and filters on all artists from the Netherlands in the where clause. For each row in the artists table, it checks their country is “NL”, then joins all albums. You can see that Roxy Dekker has a single album in our database, and Ronnie Flex has 3.

The join used specifically is an INNER join; It only joins when there is a row in artists that matches a row in albums via the on clause, in this case the artists.artist_id has to match albums.artist_id. If an artists has no albums yet, there would be nothing to join on; the INNER join omits this row from the results. An OUTER join (or equivalently, a LEFT OUTER JOIN) would leave the result in, with null values for the columns that were requested from the albums table.

There are several join types, but the INNER JOIN and LEFT (OUTER) JOIN are most common. Here is an overview:

For more information, take a look at the postgresql tutorial.

The HackYourFuture curriculum is licensed under CC BY-NC-SA 4.0 *https://hackyourfuture.net/*

Built with ❤️ by the HackYourFuture community · Thank you, contributors

Found a mistake or have a suggestion? Let us know in the feedback form.

JASPER test