This is a relational structure‐oriented concept that has to do with querying data that exists in two or more tables using a single query. It is possible to use JOINs on NoSQL data, but the nature of nonstructured or semi‐structured means it won’t be a very performant experience. If the JOIN on non‐ or unstructured data will not happen often, it might be useful, but not on huge datasets. If you find yourself needing a JOIN, you might consider restructuring the data to be relational. There are four kinds of JOINs as seen in Figure 2.28: INNER, LEFT, RIGHT, and FULL. The light shading shows the rows that will be returned.
Table 2.9 provides a summary of the various types of JOINs.
FIGURE 2.28 Representation of SQL JOINs
TABLE 2.9JOIN types
| Type | Description |
| INNER JOIN | Returns all rows if joined column is matched |
| FULL JOIN | Returns all rows on all tables even if no matches found |
| LEFT JOIN | Returns all rows from the left table and matches on the right |
| RIGHT JOIN | Returns all rows from the right table and matches on the left |
Here is an example of each join using the brainjammer database created in Exercise 2.1, starting with an INNER JOIN. An INNER JOIN selects all the rows that match the ON clause. Rows that do not match are not included. This query shows the session datetime and its associated scenario:
SELECT SESSION.SESSION_DATETIME, SCENARIO.SCENARIOFROM SESSIONINNER JOIN SCENARIO ON SESSION.SCENARIO_ID = SCENARIO.SCENARIO_ID
+————————-+—————-+
| SESSION_DATETIME | SCENARIO |
+————————-+—————-+
| 2021-07-30 08:00:00.000 | TikTok |
| 2021-08-31 09:30:00.000 | ClassicalMusic |
| 2021-09-30 09:35:00.000 | PlayingGuitar |
+————————-+—————-+
A LEFT JOIN resembles the following. The READING table is considered left and any result that is NULL means that there is no matching session in the SESSION table. There are no matches on the right table and therefore nothing is returned for the SESSION_DATETIME, but since all readings have a READING_DATETIME, all readings are returned.
SELECT READING.READING_DATETIME, [SESSION].[SESSION_DATETIME]FROM READINGLEFT JOIN [SESSION]ON [SESSION].[SESSION_DATETIME] = READING.READING_DATETIME
+————————-+——————+
| READING_DATETIME | SESSION_DATETIME |
+————————-+——————+
| 2021-07-30 08:02:49.687 | NULL |
| 2021-07-30 08:02:49.687 | NULL |
| 2021-07-30 08:02:49.687 | NULL |
| 2021-07-30 08:02:49.687 | NULL |
| 2021-07-30 08:02:49.687 | NULL |
| 2021-08-31 09:34:53.687 | NULL |
| … | … |
+————————-+——————+
A RIGHT JOIN provides the opposite of the LEFT JOIN. Instead of returning data from the left table, the data on the right table is returned as well as anything matching on the left. Three sessions are loaded into the database, all of which have a SESSION_DATETIME, but none of them match the READING_DATETIME on the READING table.
SELECT [SESSION].[SESSION_DATETIME], READING.READING_DATETIMEFROM READINGRIGHT JOIN [SESSION]ON [SESSION].[SESSION_DATETIME] = READING.READING_DATETIME
+————————-+——————+
| SESSION_DATETIME | READING_DATETIME |
+————————-+——————+
| 2021-07-30 08:02:49.687 | NULL |
| 2021-08-31 09:34:53.687 | NULL |
| 2021-09-30 09:37:18.557 | NULL |
+————————-+——————+
A FULL JOIN returns all the records from both tables regardless of a match. A word of caution: This query can return a lot of results, potentially two tables’ worth of data.
SELECT [SESSION].[SESSION_DATETIME], READING.READING_DATETIMEFROM READINGFULL JOIN [SESSION]ON [SESSION].[SESSION_DATETIME] = READING.READING_DATETIME
+————————-+————————-+
| SESSION_DATETIME | READING_DATETIME |
+————————-+————————-+
| 2021-07-30 08:02:49.687 | NULL |
| 2021-08-31 09:34:53.687 | NULL |
| 2021-09-30 09:37:18.557 | NULL |
| NULL | 2021-07-30 08:02:49.687 |
| NULL | 2021-07-30 08:02:49.687 |
| NULL | 2021-07-30 08:02:49.687 |
| … | … |
+————————-+————————-+
These kinds of queries are helpful to find sessions that don’t have any readings or readings that do not have any sessions. Because the database has referential integrity, some scenarios for which a JOIN comes in handy may not add great results. However, for many databases that were created without such constraints and that have grown too large to implement such an enforcement, this kind of command is very useful.
