07dJoiningTables

Database Management

Mick McQuaid

mcq@utexas.edu

University of Texas at Austin

13 May 2026

Joining Tables

The normalization process divides the data up into separate tables to prevent insertion, deletion, and update anomalies

As a result, most real-world activities require data from more than one table

The SQL JOIN clause is the way to obtain data from more than one table

Several join clauses can be used in one SELECT statement to join several tables together

There are several kinds of JOIN clauses, illustrated by the Venn diagram on the following frame

The default join for SQL is the inner join, shown in the middle. It returns the intersection of the tables. In other words, the values that match in both tables. We saw an example of left or right join previously in the flights database. We wanted to see the names of the airports for the flights, but we didn’t want to return any airport names for airports that didn’t have flights.

You may wonder why we have both left and right joins when they are obviously mirror images of each other and we could just change the order of the table names with either join type to get the same effect as the other. I frankly don’t know why it’s needed. It may just be that it’s sometimes easier to form a mental model of one than the other.

Full outer joins are quite rare, especially in this class where they are unheard of. For example, in another class I required students to read a 4.4GB CSV file, and asked them to join the resulting table with another table of similar size. Those who tried an outer join quickly ran out of memory. It’s instructive to ask why someone would do that, though. In the case of my students, they found that an inner join returned no records so they panicked and tried other kinds of joins. The correct thing to do would have been to diagnose the problem rather than trying different kinds of joins. In their situation, it seems to me that one table was all mixed case and the other was all upper case.

The inner join is the default join and, if you omit the INNER keyword and just say JOIN, you’ll get an inner join. For backward compatibility with an older version of SQL, you can even omit the JOIN keyword altogether and just put a comma between the table names. That’s considered bad practice but I sometimes do it without thinking. So you might see such SELECT statements but should not emulate them.

Optional

Database classes sometimes make you memorize De Morgan’s Laws to understand the logic of joins (I won’t)

De Morgan’s laws state that
- The negation of “A and B” is the same as “not A or not B”
- The negation of “A or B” is the same as “not A and not B”

People who studied “new math” in grade school may already be familiar with a bit of set theory and may be comfortable with this

You can visit, for instance, https://en.wikipedia.org/wiki/De_Morgan%27s_laws to understand the logic of joins (and view more Venn diagrams)

Up next

Let’s get out of the slideshow and into some actual JOIN clauses

Again, we’ll use the scratchpad.sql file in the sqliteExamples directory

You can scroll down to line 65 or search for the text “Using Joins”

END

Colophon

This slideshow was produced using quarto

Fonts are Roboto, Roboto Light, and Victor Mono Nerd Font