In a well-structured relational database, different kinds of items are kept clearly separated from each other, as records in different database tables. Conceptually, each table forms a different compartment, and it’s important to keep things strictly compartmentalized. At the same time though, items of different types are not necessarily independent, they often relate to one another, and related records can be connected by one record storing the identifier of another.
It’s all about finding the balance between keeping items separated according to their type, while allowing for joining separate items together according to their relationships.
In this post we’ll look in detail at the joining part, and what’s going on with the famous JOIN operator in SQL.
Say you work with books, and you work with authors. Let’s look at a classic way to structure a relational database.
Here’s what all this looks like in SQL:
Now that we’ve created the authors and books table, we create some records for each with the following SQL and we’ll have our database going with records for our two tables.
With the above database going, let’s see what we would see if we connect it to Basedash.
First, if you’re an admin in Basedash, you could browse to the authors table under your SQL connection:
You can see the records on the table, and from this interface you can also create, delete, and update records. Note the column names in the table header, next to the name of each column is also an icon indicating the data type (int for id, text for name, and also text for city).
Then, if you browse to the books table, here’s what you’d see:
Same as before, but now look at the author_id column. It has an icon indicating that it contains a foreign-key (the identifier of a specific record on another table, in this case the books table). Also notice that in this column we don’t see ID numbers in the cells. Instead, for convenience, Basedash renders a label representing the specific record which is pointed-to. (If need be, when you hover over the label you can see the ID number.)
Next, if you’re an admin in Basedash, you could create a query on the authors and books table with a JOIN clause like so:
And what you get would be authors and books matched up, allowing you to see values from each table brought together. We’re selecting values from “books joined with authors”. Note the WHERE clause which specifies that the join be done with the columns on which we earlier defined the foreign-key relationship.
And here’s what you’d see when you run that query in Basedash:
And that’s the magic moment, leveraging the relations between tables to see a complete picture. Classic relational database setup and usage.
Since we setup the books.author_id column with a foreign-key constraint pointing to the authors.id column, if you’re an admin in Basedash you could create a view of the books table, with the authors table joined, like so:
In the view builder interface, there’s a section for adding joined tables, and you can select any available tables to join. Basedash detects that there’s a foreign-key constraint on the author_id column pointing to the authors table, and so it presents the option to join the authors table onto the view.
Then with the join in place, you would be able to build the view and make use of columns from the base table and any joined tables as well.
In the above screenshot, we select the books.title column and the authors.name column to appear in the view. Note how the Basedash UI presents the table name next to the column name once you’re working in a view with one or more joins.
Imagine that we do the same as above, but when creating our authors and books table we neglect to specify the FOREIGN KEY constraint like so:
And then imagine, we proceeded to Basedash. We would have pretty much the same thing. The authors table is exactly the same as before, but the books table as a subtle difference. The author_id column wouldn’t have the foreign-key icon, and its contents would be just the plain numbers stored on that table. And there’d be no label showing a representation of a specific foreign record:
But perhaps we proceed nonetheless. We might write the same SQL query as before. In fact, the same SQL query would work, we’d get the same result:
Note the query and the WHERE clause are exactly the same. In SQL you can perform JOINs whether or not there’s a foreign-key constraint. All that matters is that the data types match up (in this case integers). So be careful, just because WHERE books.author_id = authors.id reads well, it doesn’t mean necessarily that there’s an explicit relationship, backed by an in-tact foreign-key constraint on the database.
If we proceed to create a view based on the books table, we would not have the option to join the authors table:
In the above screenshot, note that the joined tables section is then missing from the view builder. Basedash detects foreign-key constraints on your database, and the view builder only supports joining tables via columns that are backed by foreign-key constraints. (In the future we plan to support to the view builder for “manual” or arbitrary joins on columns not necessarily having foreign-key constraints, giving you more of the power that you get from writing SQL queries).
Ok, so seeing that in SQL there’s nothing stopping you from writing a JOIN that’s not backed by a foreign-key constraint, consider this wacky example. Imagine that in our database we also setup a table called grocery_list_items. We could use this table as our shopping list. Here’s how we could create and populate it in SQL:
And then here’s what our newly-created grocery_list_items table would look like when we browse to it in Basedash:
Wouldn’t you know? The quantity column contains integers. Does this give you any ideas? What other columns in other tables in our database contain integers? How about the author_id column on the books table? Why not write a query like so:
And here’s what we’d see in Basedash:
We see for example, that we get “For Whom the Bell Tolls” paired with coffee, and “Sometimes a Great Notion” paired with bananas. Wild! Also note that “For Whom the Bell Tolls” appears twice. The second time its paired with milk. This should give you an idea of the wackiness you can get when joining arbitrary tables without being thoughtful.
Imagine that we enrich our query a bit like so, so that we can see the matching of integer values that is taking place:
When we run the query in Basedash we would indeed see the integer values match up:
Even though this particular example is nonsensical, do note, however, that there are many cases where performing JOINs (of all different kinds) on arbitrary tables is legitimate, assuming the relationship is a meaningful one. Joins in SQL are not limited to foreign-key constrained columns for good reason, there’s many cases where the full power of arbitrary joins is essential for working with databases. This example is meant to show though, that not all joins make sense.
Let’s say you aren’t the database admin, and you weren’t around when the tables in question were set up, nor do you have access to internal documents on the database structure, nor are you setup with a tool for administering your database and seeing its structure that way. But let’s say you do have the ability to write queries against the database using SQL. If you know the right recipe for the database dialect in question, tou can find out whether there is a foreign-key constraint present on a given column with a plain SQL query.
Here, for example is what that query would look like for our example database, assuming that we’re working with the MySQL dialect:
From: https://stackoverflow.com/a/201678/15487978
And if you run that query in Basedash here’s what you’d see:
In this way you can verify the presence or absence of a foreign-key constraint with a quick query. Recipes for other database dialects, apart from MySQL can readily be found online too.
Get to know what Basedash can do and how it changes traditional internal tools.
See a full app that connects to a Postgres database and external API made from scratch.