Tutorial: Databases
This tutorial introduces the concept of joins in a databaseWhat are Joins?
Throughout our tutorials, we have briefly mentioned the concept of Joins but have never gone too in-depth on this topic. Joins may be one of the more difficult topics in databases, but they are nonetheless helpful with finding relationships between our database tables. In SQL, a JOIN clause simply combines rows from two or more tables, based on a related column between them. We can do the same thing using Quorum.
Let us introduce the concept of joins with two example tables: TicketOrders and Customer.
| TickOrderID | CustomerID | TicketQuantity | TicketOrderDate |
|---|---|---|---|
| 8086 | 24 | 4 | 2022-09-17 |
| 8087 | 25 | 1 | 2022-09-20 |
| 8088 | 26 | 2 | 2022-09-22 |
| 8089 | 27 | 7 | 2022-09-22 |
| CustomerID | CustomerName | ContactName | Country |
|---|---|---|---|
| 22 | Harry Styles | Olivia Wilde | UK |
| 23 | Taylor Swift | Jake Gyllenhaal | US |
| 24 | Kanye West | Kim Kardashian | US |
| 25 | Justin Bieber | Selena Gomez | CA |
We notice that these two tables have a relational column, "CustomerID." Inside of TicketOrders, the "CustomerID" refers to the "CustomerID" inside of Customers. This type of join is considered an inner join and we can actually combine these two tables together using "CustomerID" as our joining factor. Now, let us say we do combine these two tables with OrderID, CustomerName, and TicketQuantity our table would look like this table below:
| OrderID | CustomerName | TicketQuantity |
|---|---|---|
| 8084 | Harry Styles | 2 |
| 8085 | Taylor Swift | 3 |
| 8086 | Kanye West | 4 |
| 8087 | Justin Bieber | 1 |
Types of Joins
As our main example went over the concept of an inner join, there are a total of five types of joins: INNER, LEFT, RIGHT, FULL, and CROSS JOINS. We will describe all these joins along with its equivalent action using Quorum language using a table below [1].
| Type pf Join | Join Description |
|---|---|
| Inner Join | The INNER JOIN keyword selects all rows from both the tables as long as the condition is satisfied. This keyword will create the result-set by combining all rows from both the tables where the condition satisfies i.e value of the common field will be the same. |
| Left Join | This join returns all the rows of the table on the left side of the join and matches rows for the table on the right side of the join. For the rows for which there is no matching row on the right side, the result-set will contain null. LEFT JOIN is also known as LEFT OUTER JOIN. |
| Right Join | RIGHT JOIN is similar to LEFT JOIN. This join returns all the rows of the table on the right side of the join and matching rows for the table on the left side of the join. For the rows for which there is no matching row on the left side, the result-set will contain null. RIGHT JOIN is also known as RIGHT OUTER JOIN. |
| Cross Join | The SQL CROSS JOIN produces a result set which is the number of rows in the first table multiplied by the number of rows in the second table if no WHERE clause is used along with CROSS JOIN.This kind of result is called as Cartesian Product. |
| Full Join | FULL JOIN creates the result-set by combining results of both LEFT JOIN and RIGHT JOIN. The result-set will contain all the rows from both tables. For the rows for which there is no matching, the result-set will contain NULL values. |
Table of Joins in Quorum
At the moment, Quorum Studio supports 4 out of the 5 types of joins. This table shows variants of the joins including the default joins as well as using a filter with a join. To find more variants of the Inner, Left, Right, and Cross joins we can reference the main Database library. All joins in Quorum will return a QueryResult object which will be our combined table.
| Action | Description | Usage |
|---|---|---|
| databaseObject:FindInnerJoin(text tableName, text joinedTable, text joinCondition) | This action runs a Find query using a join. A Find query with a join will check the rows of multiple tables and the type of join determines what columns will appear in the final result set. An Inner Join compares every row from the first table to every row on the second table. If the values from the rows satisfy the condition then a new row is added containing the combined columns from both tables. | QueryResult result = database:FindInnerJoin("Orders", "Customers", "Orders:CustomerID = Customers:CustomerID") |
| databaseObject:FindInnerJoin(text tableName, text joinedTable, text joinCondition, text filter) | This action runs a Find query using a join. A Find query with a join will check the rows of multiple tables and the type of join determines what columns will appear in the final result set. An Inner Join compares every row from the first table to every row on the second table. If the values from the rows satisfy the condition then a new row is added containing the combined columns from both tables. This inner join also uses a filter to narrow down results for the new table. | QueryResult result = database:FindInnerJoin("Orders", "Customers", "Orders:CustomerID = Customers:CustomerID", "OrderNumber = 55") |
| databaseObject:FindCrossJoin(text tableName, text joinedTable) | This action runs a Find query using a join. A Find query with a join will check the rows of multiple tables and the type of join determines what columns will appear in the final result set. A Cross Join is different from the other joins in that there is no join condition. A Cross Join will return the Cartesian product of rows from the two tables. In other words, every row from the first table will be combined with every row from the second table and that will be returned. | QueryResult result = database:FindCrossJoin("Products", "Stores") |
| databaseObject:FindCrossJoin(text tableName, text joinedTable, text filter) | This action runs a Find query using a join. A Find query with a join will check the rows of multiple tables and the type of join determines what columns will appear in the final result set. A Cross Join is different from the other joins in that there is no join condition. A Cross Join will return the Cartesian product of rows from the two tables. In other words, every row from the first table will be combined with every row from the second table and that will be returned. This cross join also uses a filter to narrow down results for the new table. | QueryResult result = database:FindCrossJoin("Products", "Stores", "price >= 4.99") |
| databaseObject:FindLeftJoin(text tableName, text joinedTable, text joinCondition) | This action runs a Find query using a join. A Find query with a join will check the rows of multiple tables and the type of join determines what columns will appear in the final result set. A Left Join compares every row from the first table to every row on the second table. If the values from the rows satisfy the condition then a new row is added containing the combined columns from both tables. But if the condition fails then a new row is still added containing all the rows from the first table. | QueryResult result = database:FindLeftJoin("Orders", "Customers", "Orders:CustomerID = Customers:CustomerID") |
| databaseObject:FindLeftJoin(text tableName, text joinedTable, text joinCondition, text filter) | This action runs a Find query using a join. A Find query with a join will check the rows of multiple tables and the type of join determines what columns will appear in the final result set. A Left Join compares every row from the first table to every row on the second table. If the values from the rows satisfy the condition then a new row is added containing the combined columns from both tables. But if the condition fails then a new row is still added containing all the rows from the first table. This left join also uses a filter to narrow down results for the new table. | QueryResult result = database:FindLeftJoin("Orders", "Customers", "Orders:CustomerID = Customers:CustomerID", "OrderNumber = 55") |
| databaseObject:FindRightJoin(text tableName, text joinedTable, text joinCondition) | This action runs a Find query using a join. A Find query with a join will check the rows of multiple tables and the type of join determines what columns will appear in the final result set. A Right Join compares every row from the first table to every row on the second table. If the values from the rows satisfy the condition then a new row is added containing the combined columns from both tables. But if the condition fails then a new row is still added containing all the rows from the second table. | QueryResult result = database:FindRightJoin("Orders", "Customers", "Orders:CustomerID = Customers:CustomerID") |
| databaseObject:FindRightJoin(text tableName, text joinedTable, text joinCondition, text filter) | This action runs a Find query using a join. A Find query with a join will check the rows of multiple tables and the type of join determines what columns will appear in the final result set. A Right Join compares every row from the first table to every row on the second table. If the values from the rows satisfy the condition then a new row is added containing the combined columns from both tables. But if the condition fails then a new row is still added containing all the rows from the second table. This right join also uses a filter to narrow down results for the new table. | QueryResult result = database:FindRightJoin("Orders", "Customers", "Orders:CustomerID = Customers:CustomerID", "OrderNumber = 55") |
[1]: Geeks4Geeks, SQL / Join (Inner, Left, Right and Full Joins) https://www.geeksforgeeks.org/sql-join-set-1-inner-left-right-and-full-joins/ .
Next Tutorial
In the next tutorial, we will discuss Left and Right Joins, which describes how to create a left and right join on a database table.