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.

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:

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.

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:

    

        
copy
        

            

        
    
    

         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:

    

        
copy
        

            

        
    
    

         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:

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:

    

        
copy
        

            

        
    
    

         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:

    

        
copy
        

            

        
    
    

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

This will generate the following output:

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:

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:

    

        
copy
        

            

        
    
    

         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: