How to Join Multiple Tables?

When data is stored in a single table, it is very easy to retrieve rows and apply filter. However, for real world applications data is stored in multiple tables. In order to retrieve data from multiple tables join SQL command can be used. DB2Rest builds on its RSQL core to make it very easy to join more than one table and fetch data.

Types of Join

SQL JOIN types include:

• INNER JOIN (also known as a ‘simple’ JOIN). This is the most common type of JOIN.
• LEFT JOIN (or LEFT OUTER JOIN)
• RIGHT JOIN (or RIGHT OUTER JOIN)
• FULL JOIN (or FULL OUTER JOIN) NOT SUPPORTED
• Self JOIN
• Cross JOIN.

tip

The JOIN query is specified as HTTP POST.

The key attributes of request body is described below:

Sl#	Attribute	Mandatory	Description
1	table	Y	Table to join with the root table.
2	fields	N	List of fields to retrieve from join tables
3	type	N	Type of join - allowed values - INNER, LEFT, RIGHT, OUTER, CROSS. Default value : INNER
4	on	N	Join columns.
5	withTable	N	Join with another table.

Inner Join

INNER JOIN selects records that have matching values in both tables.

The example below shows how to join review table with film to retrieve all the reviews for each film.

cURL

$ curl 'http://localhost:8080/review/_expand' -i -X POST \
-H 'Content-Type: application/json' \
-H 'Accept: application/json' \
-d '[{"table":"film","fields":["title","release_year"],"type":"INNER","on":["film_id==film_id"]}]'

HTTPie

$ echo '[{"table":"film","fields":["title","release_year"],"type":"INNER","on":["film_id==film_id"]}]' | http POST 'http://localhost:8080/review/_expand' \
'Content-Type:application/json' \
'Accept:application/json'

The response from this query is shown below.

HTTP/1.1 200 OK
Content-Type: application/json
Content-Length: 174

[
    {
        "review_id":"ABC123",
        "message":"Awesome movie",
        "rating":4,
        "film_id":1,
        "last_update":"2024-03-10T08:20:59.000+00:00",
        "title":"ACADEMY DINOSAUR",
        "release_year":"2006-01-01"
    }
]

Inner Join - Multiple Tables

The example below joins multiple tables to retrieve data. In this case the film table is joined with junction/join table film_actor and actor to retrieve the list of film with the actors involved.

cURL

curl --request POST \
--url http://localhost:8080/film/_expand \
--header 'Content-Type: application/json' \
--header 'User-Agent: insomnia/8.6.1' \
--data '[
    {
        "table": "film_actor",

        "fields" : ["film_id"],

        "on" : ["film_id==film_id"]
    },
    {
        "table": "actor",

        "fields" : ["actor_id","first_name", "last_name"],

        "on" : ["actor_id==actor_id"]
    }
]
'

HTTPie

echo '[
    {
        "table": "film_actor",

        "fields" : ["film_id"],

        "on" : ["film_id==film_id"]
    },
    {
        "table": "actor",

        "fields" : ["actor_id","first_name", "last_name"],

        "on" : ["actor_id==actor_id"]
    }
]
' |  \
http POST http://localhost:8080/film/_expand \
Content-Type:application/json \
User-Agent:insomnia/8.6.1

Left Join

The LEFT JOIN keyword returns all records from the left table (A), and the matching records from the right table (B). The result is 0 records from the right side, if there is no match.

cURL

$ curl 'http://localhost:8080/users/_expand' -i -X POST \
-H 'Content-Type: application/json' \
-H 'Accept: application/json' \
-d '[{"table":"userprofile","fields":["apid","auid","firstname","lastname","email","phone"],"type":"LEFT","on":["auid==auid"]}]'

HTTPie

$ echo '[{"table":"userprofile","fields":["apid","auid","firstname","lastname","email","phone"],"type":"LEFT","on":["auid==auid"]}]' | http POST 'http://localhost:8080/users/_expand' \
'Content-Type:application/json' \
'Accept:application/json'

This will return the following result:

HTTP/1.1 200 OK
Content-Type: application/json
Content-Length: 730


[
  {
    "auid": 1,
    "username": "admin",
    "password": "pswrd123",
    "createdate": "2024-03-10T00:00:00",
    "isActive": true,
    "apid": 1,
    "firstname": "Jack",
    "lastname": "Wolf",
    "email": "bettestroom@gmail.com",
    "phone": "600075764216"
  },
  {
    "auid": 2,
    "username": "admin1",
    "password": "pass506",
    "createdate": "2024-03-10T00:00:00",
    "isActive": true,
    "apid": null,
    "firstname": null,
    "lastname": null,
    "email": null,
    "phone": null
  },
  {
    "auid": 4,
    "username": "fox12",
    "password": "45@jgo0",
    "createdate": "2024-03-10T00:00:00",
    "isActive": true,
    "apid": null,
    "firstname": null,
    "lastname": null,
    "email": null,
    "phone": null
  },
  {
    "auid": 6,
    "username": "lexus1267",
    "password": "98hnfRT6",
    "createdate": "2024-03-10T00:00:00",
    "isActive": true,
    "apid": null,
    "firstname": null,
    "lastname": null,
    "email": null,
    "phone": null
  }
]

Right Join

The RIGHT JOIN keyword returns all records from the right table (B), and the matching records from the left table (A). The result is 0 records from the left side, if there is no match.

cURL

$ curl 'http://localhost:8080/users/_expand' -i -X POST \
-H 'Content-Type: application/json' \
-H 'Accept: application/json' \
-d '[{"table":"userprofile","fields":["apid","auid","firstname","lastname","email","phone"],"type":"RIGHT","on":["auid==auid"]}]'

HTTPie

$ echo '[{"table":"userprofile","fields":["apid","auid","firstname","lastname","email","phone"],"type":"RIGHT","on":["auid==auid"]}]' | http POST 'http://localhost:8080/users/_expand' \
'Content-Type:application/json' \
'Accept:application/json'

This will return the following result:

HTTP/1.1 200 OK
Content-Type: application/json
Content-Length: 741

    [
        {
            "auid":1,
            "username":"admin",
            "password":"pswrd123",
            "createdate":"2024-03-10T00:00:00",
            "isActive":true,
            "apid":1,
            "firstname":"Jack",
            "lastname":"Wolf",
            "email":"bettestroom@gmail.com",
            "phone":"600075764216"
        },
        {
            "auid":3,
            "username":null,
            "password":null,
            "createdate":null,
            "isActive":null,
            "apid":2,
            "firstname":"Tom",
            "lastname":"Collins",
            "email":"tnkc@outlook.com",
            "phone":"878511311054"
        },
        {
            "auid":5,
            "username":null,
            "password":null,
            "createdate":null,
            "isActive":null,
            "apid":4,
            "firstname":"Bill",
            "lastname":"Fonskin",
            "email":"bill_1290@gmail.com",
            "phone":"450985764216"
        },
        {
            "auid":7,
            "username":null,
            "password":null,
            "createdate":null,
            "isActive":null,
            "apid":7,
            "firstname":"Ivan",
            "lastname":"Levchenko",
            "email":"ivan_new@outlook.com",
            "phone":"878511311054"
        }
    ]

Self Join

A self join is a regular join, but the table is joined with itself.

cURL

curl --request POST \
--url 'http://localhost:8080/film/_expand?fields=title' \
--header 'Content-Type: application/json' \
--header 'User-Agent: insomnia/8.6.1' \
--data '[
    {
        "table": "film",

        "fields" : ["title", "release_year:releaseYear"],

        "on" : ["film_id==prequel_film_id"]
    }
]
'

HTTPie

echo '[
    {
        "table": "film",

        "fields" : ["title", "release_year:releaseYear"],

        "on" : ["film_id==prequel_film_id"]
    }
]
' |  \
http POST 'http://localhost:8080/film/_expand?fields=title' \
Content-Type:application/json \
User-Agent:insomnia/8.6.1

Self Join - with compound condition

cURL

curl --request POST \
--url 'http://localhost:8080/film/_expand?fields=title' \
--header 'Content-Type: application/json' \
--header 'User-Agent: insomnia/8.6.1' \
--data '[
    {
        "table": "film",

        "fields" : ["title", "release_year"],

        "on" : ["film_id==prequel_film_id","release_year=lt=release_year"]
    }
]
'

HTTPie

echo '[
{
    "table": "film",

    "fields" : ["title", "release_year"],

    "on" : ["film_id==prequel_film_id","release_year=lt=release_year"]
}
]
' |  \
http POST 'http://localhost:8080/film/_expand?fields=title' \
Content-Type:application/json \
User-Agent:insomnia/8.6.1

Self Join - with compound condition and filter

cURL

curl --request POST \
--url 'http://localhost:8080/film/_expand?fields=title:name&filter=rental_duration==4' \
--header 'Content-Type: application/json' \
--header 'User-Agent: insomnia/8.6.1' \
--data '[
{
    "table": "film",

    "fields" : ["title:prequelTitle", "release_year"],

    "on" : ["film_id==prequel_film_id","release_year=lt=release_year"],

    "filter" : "release_year=gt=2020"
}
]
'

HTTPie

echo '[
{
    "table": "film",

    "fields" : ["title:prequelTitle", "release_year"],

    "on" : ["film_id==prequel_film_id","release_year=lt=release_year"],

    "filter" : "release_year=gt=2020"
}
]
' |  \
http POST 'http://localhost:8080/film/_expand?fields=title:name&filter=rental_duration==4' \
Content-Type:application/json \
User-Agent:insomnia/8.6.1

Cross Join

A cross join returns the Cartesian product of rows from the tables included in the join. It does not require any join condition. In other words, it will combine each row from the first table with each row from the second table.

danger

This is potentially an expensive and dangerous operation since it can lead to a large data explosion.

cURL

$ curl 'http://localhost:8080/tops/_expand' -i -X POST \
-H 'Content-Type: application/json' \
-H 'Accept: application/json' \
-d '[{"table":"bottoms","fields":["bottom_item","color:botColor","size:botSize"]}]'

HTTPie

$ echo '[{"table":"bottoms","fields":["bottom_item","color:botColor","size:botSize"]}]' | http POST 'http://localhost:8080/tops/_expand' \
'Content-Type:application/json' \
'Accept:application/json'

Combine Inner and Outer Join

cURL

curl --request POST \
--url 'http://localhost:8080/film/_expand?fields=title:name' \
--header 'Content-Type: application/json' \
--header 'User-Agent: insomnia/8.6.1' \
--data '[
        {
            "table": "language",

            "fields" : ["language_id:langId", "name:langName"],

            "on" : ["language_id==language_id"]
        },
        {

            "table": "language",
            "withTable" : "film",
            "type" : "LEFT",
            "fields" : ["language_id:altLangId", "name:altLangName"],

            "on" : ["original_language_id==language_id"]
        }
    ]
'

HTTPie

echo '[
        {
            "table": "language",

            "fields" : ["language_id:langId", "name:langName"],

            "on" : ["language_id==language_id"]
        },
        {

            "table": "language",
            "withTable" : "film",
            "type" : "LEFT",
            "fields" : ["language_id:altLangId", "name:altLangName"],

            "on" : ["original_language_id==language_id"]
        }
    ]
' |  \
http POST 'http://localhost:8080/film/_expand?fields=title:name' \
Content-Type:application/json \
User-Agent:insomnia/8.6.1

This will return the following result: (truncated)

HTTP/1.1 200 OK
Content-Type: application/json
Content-Length: 730


[
	{
		"name": "ACADEMY DINOSAUR",
		"langId": 1,
		"langName": "English",
		"altLangId": null,
		"altLangName": null
	},
	{
		"name": "ACE GOLDFINGER",
		"langId": 1,
		"langName": "English",
		"altLangId": null,
		"altLangName": null
	},
	{
		"name": "ADAPTATION HOLES",
		"langId": 1,
		"langName": "English",
		"altLangId": null,
		"altLangName": null
	},
	{
		"name": "AFFAIR PREJUDICE",
		"langId": 1,
		"langName": "English",
		"altLangId": null,
		"altLangName": null
	},
    ......
]