Multi-Table Analysis with MYSQL

Amrutha K 20 Oct, 2023 • 6 min read

Introduction

In this article, we will discover how to do Multi-Table Analysis with MySQL. SQL, a fundamental technology widely adopted by companies for data analysis, empowers us to query tables. Data analysis can involve a single table or, more often, multiple tables. Our journey through this exploration will take place in the MySQL editor, Workbench. You can download Workbench and the community server for this endeavor. Additionally, feel free to choose any MySQL editor that suits your preferences.

Let’s get started.

This article was published as a part of the Data Science Blogathon.

Database

For practical demonstration, we will be using the “mavenmovies” database, which comprises 16 relational tables. This database offers a comprehensive set of data, including customer information such as names and addresses, business-related data like staff and actors, rental details, and inventory records encompassing films and categories. It provides an engaging and diverse dataset for our queries and examples.

I uploaded the SQL file of the mavenmovies database to my Github repository. Please head into it and download the SQL file.

What is a database? - IT Strategy Techniques

Also Read: What is Database and Why Do We Need It?

Database Normalization

The process of structuring the tables and columns in a relational database in such a way as to minimize redundancy and also to preserve data integrity is called database Normalization.

This normalization includes:

  • Eliminating duplicate data
  • Reducing errors and anomalies

If you don’t do normalization to your database then you will end with lots of duplicate records.  In normalization,  the data from a single merged table are divided into multiple related tables.

Table Relationships and Cardinality

Cardinality refers to the uniqueness of values in a column of a table and is commonly used to describe
how two tables relate. Relationships between tables include one-to-one, one-to-many or many-to-many. In a table, there may be two types of keys namely a primary key and a foreign key.

  • Primary keys are always unique. The primary key uniquely identifies an instance in the table. No two records have the same primary key in a table.
  • Foreign keys are non-unique. Records may have the same foreign key. They can repeat. So there can be many instances with the same foreign key.

One-to-many relationship tables are created by connecting a foreign key in one table to a primary key in another table.

Using JOINS for Multiple-Table Querying!

We use joins for multi-table querying. First, we join the tables that e need, and then we query it from the resultant. The whole point of table relationships is to enable multi-table querying which is getting data from multiple tables at once. Mavenmovies database tables are related to each other as follows.

Multi-Table Analysis with MYSQL

Types of JOINs in Multi-Table Analysis with MYSQL

Basically, there are five types of JOINs. They are:

Join TypeDescriptionSyntax
INNER JOINReturns records that exist in both tables, excluding unmatched records.SELECT column(s) FROM table1 INNER JOIN table2 ON table1.column = table2.column
LEFT JOINReturns all records from the left table along with matching records from the right table.SELECT column(s) FROM leftTableName LEFT JOIN rightTableName ON leftTable.columnName = rightTable.columnName
RIGHT JOINReturns all records from the right table along with matching records from the left table.SELECT column(s) FROM leftTableName RIGHT JOIN rightTableName ON leftTable.columnName = rightTable.columnName
FULL OUTER JOINReturns all records from both tables, including non-matching records.SELECT column(s) FROM leftTableName FULL JOIN rightTableName ON leftTable.columnName = rightTable.columnName
UNIONReturns all data from one table stacked with all data from another table.SELECT sameColumnName FROM secondTableName UNION

In practice, INNER JOIN and LEFT JOIN are the most commonly used. They return records only when the specified ON condition is met. When writing queries with multiple tables, make sure to specify both the table and column names (e.g., inventory.inventory_id) to avoid ambiguity errors. Left joins are used to fetch all records from the left table and some from the right, while right joins fetch all records from the right table and some from the left.”

Bridging Unrelated Tables

There are some cases where you need to fetch data from two tables with have no columns in common. Here you have to search for the third table where it has a column that was in both table 1 and table 2. This third table serves as a bridge between the two tables.

Example:

Here in the mavenmovies database, we have no key to connect the customer table directly to the city, but we can join the customer to address (using
address_id), and address to city (using city_id). Here, the address table serves as our bridge between the customer table and the city table.

Multi-Table Analysis with MYSQL

Working with Workbench

First, fetch the records with the film’s title, description, and the store_id value associated with each item, and its inventory_id.

SELECT film.title,film.description,
      inventory.store_id,inventory.inventory_id 
FROM film
      INNER JOIN inventory
      ON film.film_id=inventory.film_id;

Now, fetch the list of all titles, and figure out how many actors are associated with each title.

select film.title, count(film_actor.actor_id) as number_of_actors
from film
       left join film_actor
        on film.film_id=film_actor.film_id
group by film.film_id;

Next step is to fetch the list of all actors, with each title that they appear in

select actor.first_name,actor.last_name,film.title
	from actor 
     inner join film_actor
          on film_actor.actor_id=actor.actor_id
    inner join film
          on film.film_id=film_actor.film_id;

Fetch the list of distinct titles and their descriptions, currently available in the inventory at store 2.

select film.title,film.description,inventory.store_id
from film
    inner join inventory
    on film.film_id=inventory.film_id and inventory.store_id=2;

Finally, fetch the list of actor names with their film titles whose last name was “GUINESS”.

select actor.first_name,actor.last_name,film.title
	from film 
		inner join film_actor
			on film.film_id=film_actor.film_id
		inner join actor
			on film_actor.actor_id=actor.actor_id
where last_name="GUINESS";

Create one list of all staff and advisor names, and include a column noting whether they are a staff member or advisor.

select "Advisor" as type, first_name,last_name from advisor 
union
select "Staff" as type,first_name,last_name from staff;

Create a list of all customer names and actor’s names including a column noting whether they are a customer or an actor

select 'customer' as type,first_name,last_name from customer
union 
select 'actor' as type,first_name,last_name from actor;

Fetch the list of customer names along with store_id, active status, and their address.

select customer.first_name,customer.last_name,
	customer.store_id,customer.active,
    address.address,city.city,country.country
from customer
left join address on customer.address_id=address.address_id
left join city on address.city_id=city.city_id
left join country on city.country_id=country.country_id;

Conclusion

This is all about querying multiple tables. In real scenarios, multi-table analysis with MySQL is more common than single-table analysis. MySQL simplifies the process of querying a database when you have a solid understanding of the underlying logic. In this MySQL article, we’ve covered:

  1. How to utilize joins for multi-table analysis
  2. The concepts of normalization and cardinality
  3. Various types of joins used in combining multiple tables
  4. Working with unrelated tables by employing a bridge table
  5. Practical examples of multi-table analysis

Frequently Asked Questions

Q1. How to get data from multi-table analysis with MySQL?

A. To fetch data from multiple tables in MySQL, you can use SQL JOIN clauses (e.g., INNER JOIN, LEFT JOIN) based on common columns to combine the data into a single result set.

Q2. Can you select from multiple tables in MySQL?

A. Yes, you can use the SELECT statement with JOINs to retrieve data from multiple tables in MySQL, merging the data based on specified conditions.

Q3. Can you do FROM multiple tables in SQL?

A. In SQL, you can certainly query data from multiple tables by using the FROM clause followed by JOIN conditions, effectively combining data from these tables into a single result.

Q4. How to get data from 2 different tables in SQL without JOIN?

A. If you want to retrieve data from two different tables in SQL without using JOIN, you can use subqueries or UNION to separately fetch data from each table and then combine the results. However, JOINs are typically more efficient for combining related data.

The media shown in this article is not owned by Analytics Vidhya and is used at the Author’s discretion.

Amrutha K 20 Oct 2023

Frequently Asked Questions

Lorem ipsum dolor sit amet, consectetur adipiscing elit,

Responses From Readers

Clear