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.

note

DB2Rest can join tables and views across multiple databases as well, which is covered in Connect Multiple Databases

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.

warning

If your joining tables have the same column names, then you should use column aliases for those columns, otherwise the endpoint will only return 1 of the columns because of a column name key collision.

The key attributes of request body is described below:

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

Inner Join

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

Inner Join

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

cURL
HTTPie

$ curl 'http://localhost:8080/v1/rdbms/db/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"]}]'

$ echo '[{"table":"film","fields":["title","release_year"],"type":"INNER","on":["film_id==film_id"]}]' | http POST 'http://localhost:8080/v1/rdbms/db/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
HTTPie

curl --request POST \
--url http://localhost:8080/v1/rdbms/db/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"]
    }
]
'

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/v1/rdbms/db/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.

Left Join

cURL
HTTPie

$ curl 'http://localhost:8080/v1/rdbms/db/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"]}]'

$ echo '[{"table":"userprofile","fields":["apid","auid","firstname","lastname","email","phone"],"type":"LEFT","on":["auid==auid"]}]' | http POST 'http://localhost:8080/v1/rdbms/db/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.

Right Join

cURL
HTTPie

$ curl 'http://localhost:8080/v1/rdbms/db/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"]}]'

$ echo '[{"table":"userprofile","fields":["apid","auid","firstname","lastname","email","phone"],"type":"RIGHT","on":["auid==auid"]}]' | http POST 'http://localhost:8080/v1/rdbms/db/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
HTTPie

curl --request POST \
--url 'http://localhost:8080/v1/rdbms/db/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"]
    }
]
'

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

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

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

Self Join - with compound condition

cURL
HTTPie

curl --request POST \
--url 'http://localhost:8080/v1/rdbms/db/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"]
    }
]
'

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

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

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

Self Join - with compound condition and filter

cURL
HTTPie

curl --request POST \
--url 'http://localhost:8080/v1/rdbms/db/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"
}
]
'

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/v1/rdbms/db/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
HTTPie

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

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

Combine Inner and Outer Join

cURL
HTTPie

curl --request POST \
--url 'http://localhost:8080/v1/rdbms/db/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"]
        }
    ]
'

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/v1/rdbms/db/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
	},
    ......
]

Types of Join​

Inner Join​

Inner Join - Multiple Tables​

Left Join​

Right Join​

Self Join​

Self Join - with compound condition​

Self Join - with compound condition and filter​

Cross Join​

Combine Inner and Outer Join​

Types of Join

Inner Join

Inner Join - Multiple Tables

Left Join

Right Join

Self Join

Self Join - with compound condition

Self Join - with compound condition and filter

Cross Join

Combine Inner and Outer Join