One of the most prevalent features of relational databases is joins. SQL joins refer to the process of combining the data from two or more tables into a single result set based on standard features or columns.
Joining the tables allows us to retrieve the data that is stored across multiple tables in a single query which makes it a powerful tool for data analysis and reporting.
In this tutorial, we will discover how to perform the SQL joins on multiple conditions. We will learn to use the “AND” and “OR” logical operators to join the data based on multiple conditions.
SQL Joins on Multiple Conditions
We can specify multiple conditions using the AND or OR operators in SQL. These operators allow us to define a set of Boolean expressions which are then evaluated and compared against the resulting set.
We use the AND operator to ensure that all the specified conditions are true. If even one of the conditions is not true, the entire expression is rendered false. This makes the AND operator an exceptional tool for extreme data filtering.
On the other hand, we use the OR operator when we need at least one of the conditions to be true. This makes it a more “loose” data filtering method as the resulting record must only fulfill at least one defined parameter.
The functionality of the AND and OR operators do not change even in the case of SQL joins.
SQL Multiple Joins Example
To understand how to work with joins on multiple conditions, it is best to work with an example.
For this demonstration, we use the Sakila database which was developed to explore the full capabilities of SQL.
Suppose we want to fetch the data from the film and the film_actor tables. First, we want to find all the actors who starred in the film that are either rated PG or PG-13 and having the length between 90 and 120.
In such as scenario, we need to perform a join with multiple conditions as shown in the following:
FROM actor
JOIN film_actor ON actor.actor_id = film_actor.actor_id
JOIN film ON film_actor.film_id = film.film_id
WHERE film.length BETWEEN 90 AND 120
AND film.rating IN ('PG', 'PG-13');
As you can see from the previous query, we tell SQL to perform a join between the actor and the film_actor tables based on the actor_id column. We also perform a join between the film_actor and the film tables using the film_id column. We also ensure to define two conditions using the WHERE clause to filter the result table based on the release year and the film length.
The resulting table is as follows:
We can also specify the multiple conditions based on the OR operator as shown in the following example query:
FROM film
JOIN film_category ON film.film_id = film_category.film_id
JOIN category ON film_category.category_id = category.category_id
WHERE category.name IN ('Action', 'Comedy')
AND film.rental_rate > 3.00;
The resulting table is as follows:
Conclusion
This tutorial explored how to work with SQL joins based on multiple conditions using the AND and the OR operators. This provides for more granular data filtering.
