Join
What is a join?
A join is the combination of results from two tables in a relational database.
While the word “join” makes it sound like you’re merging the tables themselves, a join actually takes the rows from two (or more) different tables and returns a new set of rows that combines columns from those tables, using entity keys and foreign keys to determine which rows are related.
Types of joins
There are four types of SQL joins:
- Left outer join: select all records from Table A, along with records from Table B that meet the join condition, if any.
- Right outer join: select all records from Table B, along with records from Table A that meet the join condition, if any.
- Inner join: only select the records from Table A and B where the join condition is met.
- Full outer join: select all records from both tables, whether or not the join condition is met.
Example joins in Metabase
Metabase defaults to left outer joins for questions asked in the query builder, but inner joins are the default for native SQL queries (that is, if you just use JOIN in your query rather than specifying which type of join).
Let’s say we wanted to return results from both the People and Orders tables in Metabase’s Sample Database, like a table of includes an order ID, the name of the person who placed that order, and their user ID.
Query builder join
Figure 1 shows what this join would look like in Metabase’s notebook editor. We’d also want to pick which columns are visible, so we aren’t shown every column from both tables.
Native SQL query join
If we were to write this same query in SQL, it may look something like this:
SELECT
orders.id AS "Order ID",
people.name AS "Name",
people.id AS "User ID"
FROM
people
JOIN
orders ON people.id = orders.user_ID
Here we’ve identified where the join happens (in this case, joining at People → ID and Orders → User_ID, an entity key and foreign key).
