Day 4¶

Today we will learn how to:

create SQL JOIN statements to query more than one table at a time; and
use SQL WHERE, ORDER BY, LIMIT and OFFSET modifiers to find subsets of data and return it in a way that makes sense; and
create SQL INSERT, DELETE, and UPDATE statements to modify our database.

And then we will celebrate and share our work!

Ready?? Let's go!

1. Catch up and Review¶

We may want to take some time to make sure everyone can finish enough of their work to take something home that they can use. To review, here's what we've done:

First: we began with a spreadsheet and created and populated a two-table database.
Second: we learned how to back up and secure our database, and how to create SQL SELECT statements to retrieve information from one table in our database.

2. Get Ready¶

Warning

Is your database backed up? Please make sure you have followed the instructions here to do that.

3. In Class¶

We're gonna get fancy now. Buckle up. 💪 💻 🤓

3.1. `SELECT` information from more than one table using `JOIN`.¶

Let's imagine you have two tables, users and words, and they are related such that the users table primary key, id is set as the foreign key in the words table, as user_id.

words

id	word	translation	user_id
1	a	hello.	3
2	buts	boots.	3
3	tbilso	dragonfly.	1
4	tsech	thunder.	2
5	ubmmm	hummingbird.

Tip

In SQL we can specify a field in a table by writing the name of the table, then a period, then the name of the field: words.user-id for the user-id field in the words table. We'd say this as "words dot user-id".

users

id	first	last	email
1	Audra	Vincent	audra.vincent@mail.xyx
2	Michelle	Clark	michelle.clark@mail.xyz
3	Lawrence	Nicodemus
4	Amy	Fountain	avf@arizona.edu

And let's say we want get back the word and translation fields from the words table, and the first and last fields from the users table. You might think we can use a SELECT query that looks sort of like this:

SELECT words.word, words.translation, users.first, users.last
FROM words, users;

The command above will run, and it will return results like this, which are probably not what we were hoping for.

word	translation	first	last
		Audra	Vincent
		Michelle	Clark
		Lawrence	Nicodemus
		Amy	Fountain
a	hello.	Audra	Vincent
a	hello.	Michelle	Clark
a	hello.	Lawrence	Nicodemus
a	hello.	Amy	Fountain
{etc.}

The problem is that we haven't explictly told the database how to match things up using our primary and foreign keys, so it's just giving us rows and values that we asked for.

The JOIN command is what tells the database how to do the match-up. This is needed because the tables don't actually match up on their own. You might have noticed that there is a user who has no words associated with them (user 4), and there is a word that has no user associated with it (word 5). The database has to know how we want it to handle mismatches.

3.1.1. INNER JOIN to get the intersection of two tables¶

Let's imagine that you want to get back only the words that have users, and only the users who have words. That is, you want all the rows that intersect between the two tables. This requires an INNER JOIN command:

SELECT words.word, words.translation, users.first, users.last
FROM words 
INNER JOIN users ON words.user_id=users.id;

The results of this command will be:

word	translation	first	last
a	hello.	Lawrence	Nicodemus
buts	boots	Lawrence	Nicodemus
tbilso	dragonfly.	Audra	Vincent
tsech	thunder.	Michelle	Clark

The results do not include user 4, nor word 5.

3.1.2. LEFT JOIN gets all of the rows in the first table, but only the matching rows in the second one.¶

So what if you want to see every word, whether or not it has a user, but you don't want to get back users who don't have a word.

You'll use a LEFT JOIN

Tip

The JOIN commands in SQL care about in which order, left to right, you name your tables. Since I'm listing the fields in the words table first in my command, that is my left table. The users table is on the right.

A LEFT JOIN includes all the records from the first table you mention, regardless of whether they have a match in the second, but only the records in the second table that match something in the first. Here's the SQL:

SELECT words.word, words.translation, users.first, users.last
FROM words 
LEFT JOIN users ON words.user_id=users.id;

The results of this command will be:

word	translation	first	last
a	hello.	Lawrence	Nicodemus
buts	boots	Lawrence	Nicodemus
tbilso	dragonfly.	Audra	Vincent
tsech	thunder.	Michelle	Clark
ubmmm	hummingbird.

3.1.2. FULL JOIN gets all the rows in both tables, but matched up!¶

A FULL JOIN returns all the rows of both tables, with matching information where it exists. So the command:

SELECT words.word, words.translation, users.first, users.last
FROM words 
FULL JOIN users ON words.user_id=users.id;

Would return this table:

word	translation	first	last
a	hello.	Lawrence	Nicodemus
buts	boots	Lawrence	Nicodemus
tbilso	dragonfly.	Audra	Vincent
tsech	thunder.	Michelle	Clark
ubmmm	hummingbird.
		Amy	Fountain

Note

SQLite doesn't actually support FULL JOIN, but other database applications do. There are workarounds in SQLite to get the same result as we would from a FULL JOIN, they're just a little more complicated.

3.1.3. Other `JOIN` types, and grokking `JOIN`¶

These are not the only kinds of JOIN statements in SQL. You might guess that there's a RIGHT JOIN that would return the mirror image of the LEFT JOIN, and you'd be correct! But there are also outer joins, natural joins, cross joins, even self joins.

Some learners find venn diagrams like these (thanks to Zahin Rahman at learnSQL.com for this particular one) to be helpful in understanding these different join types:

But anyone who is learning about databases will end up spending time with JOIN as a concept, and it'll take time and experience to figure these out. For an introduction, we think that INNER JOIN, LEFT JOIN and FULL JOIN are a reasonable start. We have tutorials on JOIN here

3.2. `WHERE`, `ORDER BY`, `LIMIT` and `OFFSET` clauses.¶

SQL is a verb-inital language that orders objects after the verb, and then takes modifying clauses. Perhaps the most useful modifier is WHERE.

3.2.1. SQL `WHERE`¶

Let's go to our SQL WHERE Clause Tutorial to work through the mechanics of WHERE.

3.2.2. SQL `ORDER BY`¶

Let's go to our SQL ORDER BY Clause Tutorial to work through the mechanics of ORDER BY.

3.2.3. SQL `LIMIT` and `OFFSET`¶

Let's go to our SQL LIMIT and OFFSET Tutorial to work through the mechanics of LIMIT and OFFSET.

3.3. `INSERT`, `DELETE` and `UPDATE` commands.¶

At last, we're ready to modify our database using SQL.

3.3.1. Our Notes¶

Here are links to our notes on these topics.

3.3.2. Our Tutorials¶

Here are links to our tutorials on these topics.

4. Showcase and Celebrate Our Work!¶

dog party by Gary Larson

Individuals and teams will share what they've created, and we'll celebrate each others' accomplishments.

Collect Feedback on our Workshop¶

We would love your feedback about this workshop! You can respond here or ...

Contact Us!¶

You can contact John, Gus, or Amy. We would love to stay in touch with you!

Next Steps¶

Our Workshop is complete! We hope you'll celebrate with us and then go make all of the databases!!