Tutorial: Databases

Left and Right Joins

Left joins returns all records from the left table (table1), and the matching records from the right table (table2). Likewise, right join returns all records from the right table (table2), and the matching records from the left table (table1). From both joins if there are 0 records from the left side or right side (depending on what join we take), there is no match.

For example, say we have entries for ID's on one table labeled 1-6 and then another table for ID entries 4-10; the entries for 7-10, will not actually have a match if we call for a right join. What it will do is output all matching data with rows 4, 5, and 6 but within rows 7-10, we may see some NULL values because they did not exist in our first table.

In this tutorial we will be finding a left and right join between two tables. We are also creating and inserting data from scratch. In this tutorial, there will be a total of 5 separate programs that all lead into creating our two types of joins: creating our tables, adding data into table 1, adding data into table 2, and creating the left and right join. For the table creation, we will be referencing methods on a tutorial on how to create database tables. For the data insertion, we will be referring to methods on a tutorial on how to insert data. Regardless, for each tutorial, we will want to connect our Database into Quorum Studio before we proceed into any work. In the first part of this tutorial we will be creating our tables.

We will be wanting to create two tables: one table called "likes" and the other table called "users" and we will follow a very similar pattern to this website that visualizes left and right joins. We will first be creating two CreateTable objects: newTable and newTable1. We will first start to build our first table, "likes" using the actions SetTable(text table name), AddIntegerColum(text columnName), and AddTextColumn(text columnName, int charLimit). The columns and the data types will be as follows:

Once we have created all our columns, we will now want to pass in our newTable object into an action called CreateTable(CreateTable objectName) using our database object.

// create first table
    CreateTable newTable
    newTable:SetTable("likes")
    newTable:AddIntegerColumn("id")
    newTable:AddTextColumn("likes",500)
    database:CreateTable(newTable)   

We will want to follow this same process for the other table, "users" where the columns and data types will be as follows:

The full code will be below:

use Libraries.Data.Database.Database
    use Libraries.Data.Database.CreateTable

    Database database
    database:Connect("localhost", "myfirstdatabase", "root", "password")

    // create first table
    CreateTable newTable
    newTable:SetTable("likes")
    newTable:AddIntegerColumn("id")
    newTable:AddTextColumn("likes",500)
    database:CreateTable(newTable)

    CreateTable newTable1
    newTable1:SetTable("users")
    newTable1:AddIntegerColumn("id")
    newTable1:AddTextColumn("customerName",500)
    database:CreateTable(newTable1)

Next, we will want to enter data inside our tables. We will have two separate programs to insert data into our "likes" table and then our "users" table. Let us go ahead and define our Insert object named "insert." We will be using this object to access the actions SetTable(), ToText(), Empty() and Add().

To insert data, we will need to make sure we select our table using SetTable(text tableName); do this with "users" first and insert this string as our parameter. Then we will want to use the action Add(text columnName, dataType dataValue) with our insert object to enter in appropriate data on the columns. Recall that our columns are: id and customerName. For example, say that we want to add an entry for "likes," we will need to call the action Add() with our Insert object and insert "likes" in the first parameter and an activity or object people enjoy. Below is a code snippet of adding one complete row onto our table:

// add first entry
  insert:Add("id", 1)
  insert:Add("likes", "ducks")
  // will only insert 1 query at a time
  database:Insert(insert)
  rowsAffected = rowsAffected + 1
  output insert:ToText()

It is important that once we add one record, we must immediately call the action Empty() which will clear out our object so we can add multiple records inside of one program. Optionally, to confirm our insert, we can also call the action ToText() that outputs the SQL notation of insertion. Now that we know how to insert a row, we can populate the table for "likes" as well as "users." Below are the two sets of code for inserting both tables, likes and users.

use Libraries.Data.Database.Database
use Libraries.Data.Database.Insert

Database database
database:Connect("localhost", "myfirstdatabase", "root", "password")

  Insert insert
  insert:SetTable("likes")
  // keep a running sum on how many rows we add 
  integer rowsAffected = 0
   
  // add first entry
  insert:Add("id", 1)
  insert:Add("likes", "ducks")
  // will only insert 1 query at a time
  database:Insert(insert)
  rowsAffected = rowsAffected + 1
  output insert:ToText()

  insert:Empty()
  insert:Add("id", 2)
  insert:Add("likes", "computers")
  // will only insert 1 query at a time
  database:Insert(insert)
  rowsAffected = rowsAffected + 1
  output insert:ToText()

  insert:Empty()
  insert:Add("id", 3)
  insert:Add("likes", "anime")
  // will only insert 1 query at a time
  database:Insert(insert)
  rowsAffected = rowsAffected + 1
  output insert:ToText()

  insert:Empty()
  insert:Add("id", 4)
  insert:Add("likes", "butter")
  // will only insert 1 query at a time
  database:Insert(insert)
  rowsAffected = rowsAffected + 1
  output insert:ToText()

  insert:Empty()
  insert:Add("id", 5)
  insert:Add("likes", "kpop")
  // will only insert 1 query at a time
  database:Insert(insert)
  rowsAffected = rowsAffected + 1
  output insert:ToText()

  insert:Empty()
  insert:Add("id", 6)
  insert:Add("likes", "hiking")
  // will only insert 1 query at a time
  database:Insert(insert)
  rowsAffected = rowsAffected + 1
  output insert:ToText()

  insert:Empty()
  insert:Add("id", 1)
  insert:Add("likes", "coding")
  // will only insert 1 query at a time
  database:Insert(insert)
  rowsAffected = rowsAffected + 1
  output insert:ToText()

   insert:Empty()
  insert:Add("id", 2)
  insert:Add("likes", "mayo")
  // will only insert 1 query at a time
  database:Insert(insert)
  rowsAffected = rowsAffected + 1
  output insert:ToText()

 

use Libraries.Data.Database.Database
use Libraries.Data.Database.Insert

Database database
database:Connect("localhost", "myfirstdatabase", "root", "password")

  Insert insert
  insert:SetTable("users")
  // keep a running sum on how many rows we add 
  integer rowsAffected = 0
   insert:Empty()
   
  // add first entry
  insert:Add("id", 1)
  insert:Add("customerName", "peter parker")
  // will only insert 1 query at a time
  database:Insert(insert)
  rowsAffected = rowsAffected + 1
  output insert:ToText()

  insert:Empty()
  insert:Add("id", 2)
  insert:Add("customerName", "tony stark")
  // will only insert 1 query at a time
  database:Insert(insert)
  rowsAffected = rowsAffected + 1
  output insert:ToText()

  insert:Empty()
   insert:Add("id", 3)
  insert:Add("customerName", "joe goldberg")
  // will only insert 1 query at a time
  database:Insert(insert)
  rowsAffected = rowsAffected + 1
  output insert:ToText()

  insert:Empty()
  insert:Add("id", 4)
  insert:Add("customerName", "barack obama")
  // will only insert 1 query at a time
  database:Insert(insert)
  rowsAffected = rowsAffected + 1
  output insert:ToText()

  insert:Empty()
  insert:Add("id", 5)
  insert:Add("customerName", "joe biden")
  // will only insert 1 query at a time
  database:Insert(insert)
  rowsAffected = rowsAffected + 1
  output insert:ToText()

  insert:Empty()
   insert:Add("id", 6)
  insert:Add("customerName", "pewdiepie")
  // will only insert 1 query at a time
  database:Insert(insert)
  rowsAffected = rowsAffected + 1
  output insert:ToText()

  insert:Empty()
   insert:Add("id", 7)
  insert:Add("customerName", "taylor swift")
  // will only insert 1 query at a time
  database:Insert(insert)
  rowsAffected = rowsAffected + 1
  output insert:ToText()

   insert:Empty()
  insert:Add("id", 8)
  insert:Add("customerName", "kanye west")
  // will only insert 1 query at a time
  database:Insert(insert)
  rowsAffected = rowsAffected + 1
  output insert:ToText()

Left Join

Lastly, we are going to create a left join of our two tables. To do this, we will be calling the action, FindLeftJoin(text tableName, text joinedTable, text jointCondition) and then saving our result into a QueryResult object. For the first two parameters, we will want to insert our two table names, "likes" and "users" but for our third parameter, we will need to find a corresponding column that will essentially map the two tables together.

As we made our tables in the beginning, notice that both tables contain the column "id"; we will be using this column in our jointCondition. Our condition is formatted to be table1:column = table2:column. Therefore, our actual joint condition will be: "likes:id = users:id" inside the 3rd parameter. Let's also go ahead and print the results of our QueryResult item inside a repeat while loop.

use Libraries.Data.Database.Database
use Libraries.Data.Database.CreateTable
use Libraries.Data.Database.QueryResult


    Database database
    database:Connect("localhost", "myfirstdatabase", "root", "")

    QueryResult result = database:FindLeftJoin("likes", "users", "likes:id = users:id")
    repeat while result:HasNext()
        result:Next()
        output "CustomerName: " + result:GetText("customerName")
        output "likes: " + result:GetText("likes")

        output " "
    end

Right Join

Also, we can create a right join of our two tables. To do this, we will be calling the action, FindRightJoin(text tableName, text joinedTable, text jointCondition) and then saving our result into a QueryResult object. For the first two parameters, we will want to insert our two table names, "likes" and "users" but for our third parameter, we will need to find a corresponding column that will essentially map the two tables together.

As we made our tables in the beginning, notice that both tables contain the column "id"; we will be using this column in our jointCondition. Our condition is formatted to be table1:column = table2:column. Therefore, our actual joint condition will be: "likes:id = users:id" inside the 3rd parameter. Let's also go ahead and print the results of our QueryResult item inside a repeat while loop.

use Libraries.Data.Database.Database
use Libraries.Data.Database.CreateTable
use Libraries.Data.Database.QueryResult


    Database database
    database:Connect("localhost", "myfirstdatabase", "root", "password")

    QueryResult result = database:FindRightJoin("likes", "users", "likes:id = users:id")
    repeat while result:HasNext()
        result:Next()
        output "CustomerName: " + result:GetText("customerName")
        output "likes: " + result:GetText("likes")

        output " "
    end

Next Tutorial

In the next tutorial, we will discuss InnerJoins, which describes how to create a inner join on a database table.