Data and Business Intelligence Glossary Terms

A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
S
T
V
W
X

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.

<em>Fig. 1</em>. A join in the query builder.
Fig. 1. A join in the query builder.

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).

Key article

Related terms

Further reading