---
title: "JOIN Clause"
url: https://docs.catalyst.zoho.com/en/cloud-scale/help/zcql/joins/
description: "ZCQL is Catalyst's own query language modelled after familiar query languages that you can use to query your app's database efficiently."
last_updated: 2024-09-03T11:01:06Z
service: Cloud Scale
type: general
---

# JOIN Clause

### Introduction

The `JOIN` clause is used to combine rows from two or more tables in a `SELECT` query, based on a related column between them. The rows from all the tables are generated together with the common column merged.

Before we learn more about the `JOIN` clause, let's understand a few key terms related to joins:

1. **Primary Key:** A primary key is a unique identifier of a record in a table. A table can have one column as its primary key column, and the value for each record in this column must be unique, is mandatory, and cannot be duplicated.
2. **Foreign Key**: A foreign key column in one table uniquely identifies a row in another table or the same table. The foreign key of the second table refers to the primary key of the first table.
3. **Parent Table**: In the `JOIN` clause, the parent table is the base table that the join is performed based on.
4. **Join Table**: The join table is the secondary table or the child table which is being merged with the parent table in the `JOIN` clause.

> **Note:** Joins between two tables are only possible if there is a relationship between them. One of the tables must refer to the other table's primary key column, so either the join table must have a foreign key column of the parent table, or the parent table must have a foreign key column of the join table.

In our example, the _MovieID_ column is the primary key of the _Movies_ table as it holds the unique identification number of a movie.

**Example Database:**

Let's create a table named "Theaters" in the ticket booking application which specifies the locations of all the theaters that are registered with the application.

Sample records from the **Theaters** table are given below:
<table class="content-table">
<thead>
<tr>
<th><strong>TheaterID</strong></th>
<th><strong>TheaterName</strong></th>
<th><strong>Location</strong></th>
</tr>
</thead>
<tbody>
<tr>
<td>047</td>
<td>The Express Cinemas</td>
<td>New York City</td>
</tr>
<tr>
<td>048</td>
<td>ANC Cinemas</td>
<td>Rochester</td>
</tr>
<tr>
<td>052</td>
<td>Cosmos Theater</td>
<td>Albany</td>
</tr>
<tr>
<td>053</td>
<td>FunTime Cinemas</td>
<td>Buffalo</td>
</tr>
</tbody>
</table>

The _TheaterID_ column is the primary key of the _Theaters_ table as it holds the unique identification numbers of the theaters. The _TheaterID_ column in the _Movies_ table is the foreign key to the _TheaterID_ column in the _Theaters_ table.

> **Note:** When you use a `JOIN` clause, you must specify the column names and their table names for each column in the query as: **'_table\_name.column\_name_'**'. For example: '_Movies.MovieName_', '_Theaters.Location_'

If you don't specify the table name with the column name in a query, then by default it is considered to be a column in the base table.

### Types of Joins

There are two types of Joins that can be performed in ZCQL.

#### INNER JOIN

The `INNER JOIN` returns the records that have matching values in both the parent table and the join table. When you use an inner join on two tables, only the records whose values match for the specified columns in both the tables are produced as the output.
 The syntax for using an `INNER JOIN` is:

```sql
SELECT column_name(s)
FROM parent_table_name
INNER JOIN join_table_name
ON parent_table_name.column_name = join_table_name.column_name
```

**Example:**

To view a list of the movie names, show dates, and show times from the _Movies_ table along with the theater names from the _Theaters_ table, execute the following query:

```sql
SELECT Movies.MovieName, Theatres.TheaterName, Movies.ShowDate, Movies.ShowTime, 
FROM Movies INNER JOIN Theatres ON Movies.TheaterID = Theatres.TheaterID
```

This will generate the following output:

<table class="content-table">
<thead>
<tr>
<th><strong>MovieName</strong></th>
<th><strong>TheaterName</strong></th>
<th><strong>ShowDate</strong></th>
<th><strong>ShowTime</strong></th>
</tr>
</thead>
<tbody>
<tr>
<td>Ant-Man and the Wasp</td>
<td>The Express Cinemas</td>
<td>2018-07-13</td>
<td>13:30:00</td>
</tr>
<tr>
<td>Hotel Transylvania 3: Summer Vacation</td>
<td>Cosmos Theater</td>
<td>2018-07-13</td>
<td>14:20:00</td>
</tr>
<tr>
<td>The First Purge</td>
<td>Cosmos Theater</td>
<td>2018-07-14</td>
<td>17:00:00</td>
</tr>
<tr>
<td>The First Purge</td>
<td>FunTime Cinemas</td>
<td>2018-07-14</td>
<td>21:30:00</td>
</tr>
</tbody>
</table>

#### LEFT JOIN 

The `LEFT JOIN` returns all the records from the parent table and the matched records from the join table. When you use a left join on two tables, all records from the parent table are returned, even if they do not have matches from the join table. The result is displayed as 'NULL' for records that do not have a match in the join table.

The syntax for using a `LEFT JOIN` is:

```sql
SELECT column_name(s)
			FROM parent_table_name
			LEFT JOIN join_table_name
			ON parent_table_name.column_name = join_table_name.column_name
```

**Example:**

To view a list of theaters and their locations from the _Theaters_ table along with the movies that are being screened in the theaters from the _Movies_ table, execute the following query:

```sql
SELECT Theaters.TheaterName, Theaters.Location, Movies.MovieName
FROM Theaters
LEFT JOIN Movies
ON Theaters.TheaterID = Movies.TheaterID
```

This will generate the following output:

<table class="content-table">
<thead>
<tr>
<th><strong>TheaterName</strong></th>
<th><strong>Location</strong></th>
<th><strong>MovieName</strong></th>
</tr>
</thead>
<tbody>
<tr>
<td>The Express Cinemas</td>
<td>New York City</td>
<td>The First Purge</td>
</tr>
<tr>
<td>ANC Cinemas</td>
<td>Rochester</td>
<td><strong>NULL</strong></td>
</tr>
<tr>
<td>Cosmos Theater</td>
<td>Albany</td>
<td>Hotel Transylvania 3: Summer Vacation</td>
</tr>
<tr>
<td>FunTime Cinemas</td>
<td>Buffalo</td>
<td>Skyscraper</td>
</tr>
</tbody>
</table>

Since there are no matching records for ANC Cinemas in the Movies table, the result is displayed as 'NULL'.

### Multiple Joins

You can combine a maximum of **four** joins in a single ZCQL query. However, you can only write **one** `JOIN` condition for each join clause. This will display the results from four different tables, where one is linked to another using an `INNER JOIN` or a `LEFT JOIN`.

**Example Database**:

Let's create a table called 'Pricing' in the ticket booking application which displays the ticket price in dollars of a single ticket for a particular movie and theater. This table references _TheaterID_ from the _Theaters_ table and _MovieID_ from the _Movies_ table as the foreign keys.

Sample records from the **Pricing** table are given below:

<table class="content-table">
<thead>
<tr>
<th><strong>TheaterID</strong></th>
<th><strong>MovieID</strong></th>
<th><strong>Price</strong></th>
</tr>
</thead>
<tbody>
<tr>
<td>047</td>
<td>2056</td>
<td>9.20</td>
</tr>
<tr>
<td>048</td>
<td>NULL</td>
<td><strong>NULL</strong></td>
</tr>
<tr>
<td>052</td>
<td>2057</td>
<td>8.64</td>
</tr>
<tr>
<td>052</td>
<td>2058</td>
<td>11.50</td>
</tr>
<tr>
<td>053</td>
<td>2059</td>
<td>7.44</td>
</tr>
</tbody>
</table>

You can execute a query to display the _TheaterName_ from the _Theaters_ table, _MovieName_, _ShowDate_, and _ShowTime_ from the _Movies_ table, and _Price_ from the _Pricing_ table, by executing the following query:

```sql
SELECT Theaters.TheaterName,Movies.MovieName, Movies.ShowDate, 
Movies.ShowTime, Pricing.Price
FROM Pricing
LEFT JOIN Movies
ON Theaters.TheaterID = Movies.TheaterID
INNER JOIN Theaters
ON Movies.MovieID = Pricing.MovieID
```

This will generate the following output:

<table class="content-table">
<thead>
<tr>
<th><strong>TheaterName</strong></th>
<th><strong>MovieName</strong></th>
<th><strong>ShowDate</strong></th>
<th><strong>ShowTime</strong></th>
<th><strong>Price</strong></th>
</tr>
</thead>
<tbody>
<tr>
<td>The Express Cinemas</td>
<td>The First Purge</td>
<td>2018-07-13</td>
<td>13:00:00</td>
<td>9.20</td>
</tr>
<tr>
<td>ANC Cinemas</td>
<td>NULL</td>
<td>NULL</td>
<td>NULL</td>
<td>NULL</td>
</tr>
<tr>
<td>Cosmos Theater</td>
<td>Ant-Man and the Wasp</td>
<td>2018-07-13</td>
<td>14:20:00</td>
<td>8.64</td>
</tr>
<tr>
<td>Cosmos Theater</td>
<td>Hotel Transylvania 3: Summer Vacation</td>
<td>2018-07-14</td>
<td>17:00:00</td>
<td>11.50</td>
</tr>
<tr>
<td>FunTime Cinemas</td>
<td>Skyscraper</td>
<td>2018-07-14</td>
<td>21:30:00</td>
<td>7.44</td>
</tr>
</tbody>
</table>

> **Note:** 

* To utilize all the features of the `JOIN` clause, we recommend you migrate your codebase from **ZCQL V1** to **ZCQL V2**.

* From **December 01st, 2024**, all your current projects in all your [Orgs](https://docs.catalyst.zoho.com/en/getting-started/catalyst-organizations/) present in the [Development Environment](https://docs.catalyst.zoho.com/en/deployment-and-billing/environments/development-environment/) will be automatically mapped to **ZCQL V2 Parser**.

* From **April 01st, 2025**, all the projects present in all *Orgs* that have already been mapped to **ZCQL V2 Parser** in *Development Environment* will be automatically mapped to **ZCQL V2 Parser** in the [Production Environment](https://docs.catalyst.zoho.com/en/deployment-and-billing/environments/production-environment/), if and when production is enabled for the project.

* You can learn more on the enhancements and features of **ZCQL V2** from this [help document](https://docs.catalyst.zoho.com/en/cloud-scale/help/zcql/syntax-exceptions/).