MySQL Joins Tutorial | MySQL 5 Join Clauses, Syntax & Practical Examples

MySQL is a Relational Database Management System. This Open-source tool is one of the best RDBMS available in the market that is being used to develop web-based software applications. MySQL is scalable, intuitive, and swift when compared to its contemporaries. It houses a Client-Server architecture. At the core of the MySQL Database lies the MySQL Server. This server is availed as a separate program and handles all the database instructions, commands, and statements.

This blog talks about the different types of MySQL Join clauses that can be leveraged for operations. This includes its importance, description, MySQL Update Joins, and their limitations. It also gives a brief introduction to MySQL and its key features before diving into the nuances of MySQL Joins. You can also read our article about MySQL Check.

What is MySQL?

MySQL was used by more than 39% of developers in the world back in 2019, making it the world’s most popular Database. It certainly lacks the extensive features that are provided by PostgreSQL, but it is still useful for a large number of use cases like web applications. 

Since it comes standard in the LAMP stack, where LAMP stack is an Open-Source suite of web applications that consists of Linux, Apache HTTP Server, MySQL, PHP; MySQL is the go-to choice for scalable web applications. Let’s talk about a few salient features that make MySQL such a great catch. Also, see how you can integrate from MySQL to PostgreSQL. 

Key Features of MySQL

• Maintained by Oracle: Oracle owns and maintains MySQL. It also offers premium versions of MySQL with additional services, proprietary plugins, user support, and extensions. 
• Long History: MySQL has been around for over 20 years since its first release in 1995.
• Frequent Updates: MySQL is made more robust with frequent updates with new features and security improvements. The latest release is Version 8.0.23 released on 18 January 2021.
• MVCC Features: MySQL recently started offering MVCC (Multi-Version Concurrency Control) features. 
• A Supportive Community: A devoted community of developers is available to help with troubleshooting as and when needed.
• Open-Source: MySQL is also a free and Open-Source Relational Database Management System (RDBMS).
• Users: MySQL is used widely by Google, NASA, Flickr, GitHub, Netflix, Tesla, Twitter, Uber, Wikipedia, YouTube, Zendesk to name a few.

MySQL is a lightweight database that can be installed and used by developers on production application servers with large multi-tier applications. Leveraging MySQL as a part of your workflow has several advantages:  

• Features like Master-Slave Replication, and Scale-Out are supported by MySQL.
• Offload Reporting, Geographic Data Distribution is also supported by MySQL.
• There’s a very low overhead with the MyISAM storage engine when used for read-only applications.
• For frequently used tables, MySQL provides support for the Memory Storage Engine.
• For repeatedly used statements there exists a Query Cache. 
• MySQL is easy to learn and troubleshoot given a wide number of helpful sources like blogs, white papers, and books on the subject. 
• MySQL is a highly flexible and scalable Database Management System.

What is an SQL Join?

MySQL databases generally store a large amount of data. To analyze this data effectively, DBAs and Analysts need to pull records from two or more tables based on specific conditions. This is where MySQL Joins come in handy. You can use Joins to extract data from various tables in a single query. For SQL Joins to function, the tables have to be related to each other with a common key value.

You can leverage JOIN Clauses in the UPDATE, SELECT, and DELETE statements in MySQL. A common join pattern is leveraged to join the primary key of one table to its foreign key.

What is the Need for using SQL Joins?

SQL Joins are one of the most widely used MySQL Server clauses to extract and collate data from two or more tables. In a real-world database, data is structured in a vast array of tables, which warrants the need to join these multiple tables based on logical relationships between them.

By stitching the database tables together, you can make the database much easier to use and read since a normalized database cannot be read well by humans. Joins in MySQL only allow you to utilize a single JOIN query instead of running multiple simple queries. So, MySQL Joins helps you decrease server overhead, achieve better performance, and reduce the number of data transfers between MySQL and your application.   

What are MySQL 5 Join Clauses?

Here are the 5 Key types of MySQL Join clauses that will be explored in this section:

• MySQL Inner Join
• MySQL Outer Join
  • MySQL Left Join
  • MySQL Right Join
• MySQL Cross Join
• MySQL Full Join
• MySQL Union

1. MySQL Inner Join

MySQL Inner Join is a clause that can be used to return all rows from various tables where the join condition is met. This MySQL Join clause is the most common type of MySQL Join. Here is the syntax for MySQL Inner Join:

SELECT columns  
FROM table1   
INNER JOIN table2  
ON table1.column = table2.column;  

Here is a Venn Diagram to further drill the point home:

Say, you have two tables “students” and “officers” that have the following data:

Now, for this situation you can execute the following query to demonstrate MySQL Inner Join:

SELECT officers.officer_name, officers.address, students.course_name  
FROM officers   
INNER JOIN students  
ON officers.officer_id = students.student_id;   

On executing this query, you can obtain the result as follows:

2. MySQL Outer Join

MySQL Outer Joins shows all the records that are matching from both tables. This MySQL JOIN clause can even detect records that have no match in the joined table. It displays NULL values for the records of the joined table if no match is found. This clause has two subtypes:

• MySQL Outer Join
• MySQL Left Join

MySQL Left Join

For this example, let’s take the tables “members”:

And “movies”:

Say you want to get the titles of all the movies together along with the names of members who rented these movies. From the tables shown above, it is evident that a few movies are not being rented by any member. So, you can leverage MySQL Left Join for this purpose. Here’s the Venn Diagram that demonstrates pictorially what this MySQL Join clause looks like:

The LEFT JOIN clause will display all the rows from the table on the left even if no matching rows have been located in the table on the right. Wherever no matches have been found in the table on the right, NULL is displayed. Here is the syntax of this MySQL JOIN clause on “members” and “movies” tables:

SELECT A.`title` , B.`first_name` , B.`last_name`
FROM `movies` AS A
LEFT JOIN `members` AS B
ON B.`movie_id` = A.`id`

On executing the aforementioned MySQL Join query, you can see that in the result shown below for movies that weren’t rented, member name fields have NULL values. This means that no matching members were found from the members’ table for that specific movie.

MySQL Right Join

MySQL Right Join is the exact opposite of MySQL Left Join. The Right Join shows all the columns from the right even if no matching rows have been located in the table on the left. Wherever no matches have been found on the left in the table, NULL is displayed. For the aforementioned example, say you need to get the names of members and movies rented by them. This results in a new member who hasn’t rented any movie yet. Here’s the Venn Diagram to demonstrate this MySQL Join clause:

Here is what the MySQL Right Join query looks like:

SELECT  A.`first_name` , A.`last_name`, B.`title`
FROM `members` AS A
RIGHT JOIN `movies` AS B
ON B.`id` = A.`movie_id`

When you execute the above MySQL Join query in the MySQL Workbench, it gives the following results:

3. MySQL Cross Join

MySQL Cross Join is also called a Cartesian Join and helps extract all combinations of rows from each table. This means that unlike The Outer Right Join, Outer Left Join, and Inner Join, the MySQL Cross Join clause does not warrant a join condition. For this type of MySQL join, the result set is obtained by multiplying each row of table A with all rows in table B provided no additional condition is given. So, say the first table has n rows and the second table has m rows. This MySQL Join clause that joins tables A and B will return n*m rows.

For this example, say you have two tables, “members”:

And “committees”:

Here is the syntax for the MySQL Cross Join clause:

SELECT select_list
FROM table_1
CROSS JOIN table_2;

Here is a Venn Diagram to depict what Cross Join looks like for the example:

Here is the Cross Join query for the aforementioned example to join the members with the committee’s tables:

SELECT 
    m.member_id, 
    m.name AS member, 
    c.committee_id, 
    c.name AS committee
FROM
    members m
CROSS JOIN committees c;

This is what the result of the MySQL Cross Join looks like:

You can leverage the MySQL Cross Join for generating planning data. For instance, you can carry the Sales Planning by utilizing the Cross Join of customers, years, and products. 

4. MySQL Full Join

MySQL Joins allow you to utilize a single JOIN query as opposed to running multiple simple queries. Therefore, you can reduce server overhead, achieve better performance, and reduce the number of data transfers between MySQL and your application.

Unlike SQL Server, MySQL does not offer support for FULL OUTER JOIN as a separate JOIN type. However, to achieve the same results as FULL OUTER JOIN, you can combine RIGHT OUTER JOIN and LEFT OUTER JOIN:

SELECT * FROM tableA
LEFT JOIN tableB ON tableA.id = tableB.id
UNION
SELECT * FROM tableA
RIGHT JOIN tableB ON tableA.id = tableB.id

5. MySQL Union

Union in MySQL can be used to carry out a union between multiple columns from different tables into a single column. The structure of the UNION query for choosing unique values is as follows:

SELECT column_name(s) FROM table1

UNION

SELECT column_name(s) FROM table2;

To select repeated values from a column, the snippet you can use is:

SELECT column_name(s) FROM table1

UNION ALL

SELECT column_name(s) FROM table2;

You can fetch the IDs from the tables as follows:

$sql = "SELECT id FROM myguests 
UNION 
SELECT id FROM messages";

When you open index.php and replace $sql query with the one mentioned above, this is the result you get:

The query has fetched all the unique IDs which can be found in both tables.

Self Join in MySQL: A Special Use Case

The MySQL Self Join is primarily used to extract Hierarchical Data or to compare a row with other rows within the same table. To execute a Self Join, you need to use table aliases to not repeat the same table twice in a single query. Here is the table you can use for this example:

This table (employee) stores both the employee data and the organization structure data. The reportsTo column in this table can be used to ascertain the manager id of an employee. If you want to obtain the entire organization structure, you can join the employee table to itself through the employeeNumber and reportsTo columns. This table serves two purposes: one is that of Direct Reports and the other is Manager. Here is the code snippet that lets you execute a MySQL Self Join query:

SELECT 
    CONCAT(m.lastName, ', ', m.firstName) AS Manager,
    CONCAT(e.lastName, ', ', e.firstName) AS 'Direct report'
FROM
    employees e
INNER JOIN employees m ON 
    m.employeeNumber = e.reportsTo
ORDER BY 
    Manager;

This is what the output looks like:

This output depicts only the employees that have a manager. But, you don’t see the name of the president here since his name has been filtered due to the INNER JOIN clause leveraged here.

How to Join Multiple Tables using MySQL Joins?

Here is a general SQL query syntax that can allow you to join three or more tables. This SQL query should work in all the major relational databases such as Oracle, MySQL, Sybase, Microsoft SQL Server, and PostgreSQL:

SELECT t1.col, t3.col
FROM table1
JOIN table2 ON table1.primarykey = table2.foreignkey
JOIN table3 ON table2.primarykey = table3.foreignkey

First, you will join table 1 and table 2 which will produce a temporary table with combined data from table 1 and table 2, which can then be joined to table 3. This formula can also be easily extended to more than 3 tables to N tables. All you need to do is make sure that the SQL query should have N-1 join statements to join N tables.

How to Utilise MySQL Joins in Command Prompt?

Say, for this example, you have two tables tutorials_tbl and tcount_tbl, within the TUTORIALS database. Use the following query within the command prompt to get started:

root@host# mysql -u root -p password;
Enter password:*******
mysql> use GUIDES;
Database changed
mysql> SELECT * FROM Mcount_table1;
+-----------------+----------------+
| guide_name | guide_count |
+-----------------+----------------+
|      Girish     |       20       |     
|      John     |      NULL      |        
|       Jen       |      NULL      |          
|      Gill       |       20       |          
|    John Poul    |        1       |      
|     Rajeev      |        1       |        
+-----------------+----------------+
6 rows in set (0.01 sec)
mysql> SELECT * from guide_table1;
+-------------+----------------+-----------------+-----------------+
| guide_id | guide_title | guide_name | submission_date |
+-------------+----------------+-----------------+-----------------+
|      1      |  Learn Python     |     John Poul   |    2021-05-24   |   
|      2      |  Learn MySQL   |      Abdul S    |    2021-05-24   |   
|      3      | HTML Tutorial  |      Rajeev     |    2021-05-06   |   
+-------------+----------------+-----------------+-----------------+
3 rows in set (0.00 sec)
mysql>

Now that you have obtained the two tables, you can write a SQL query to join these two tables. This query will be choosing the authors from table guide_table1 and pulling the corresponding number of tutorials from table Mcount_table1. Here is the code snippet for the same:

mysql> SELECT a.guide_id, a.guide_author, b.guide_count
   -> FROM guide_table1 a, Mcount_table1 b
   -> WHERE a.guide_author = b.guide_author;
+-------------+-----------------+----------------+
| guide_id | guide_author | guide_count |
+-------------+-----------------+----------------+
|      1      |    John Poul    |        1       |
|      3      |     Rajeev      |        1       |
+-------------+-----------------+----------------+
2 rows in set (0.01 sec)
mysql>

How to Utilise MySQL Joins with PHP Scripts?

PHP leverages mysql_query() or mysqli query() function to obtain records from a MySQL table through Joins. This function requires two parameters and results in a TRUE on successful execution and FALSE on failure. Here is the syntax for MySQL Join using PHP Script:

$mysqli->query($sql,$resultmode)

Here, the first parameter is used to get records from multiple tables using Join. The second parameter is optional that returns either the MYSQLI_STORE_RESULT or MYSQLI_USE_RESULT based on the user behavior.

Now, you need to create a table and populate it with some data as follows:

create table Mcount_table1(
   guide_author VARCHAR(240) NOT NULL,
   guide_count int
);
insert into Mcount_table1 values('Rakesh', 3);
insert into tcount_tbl values('Harish', 1);

Use the following code snippet to get records from the two tables using the JOIN clause:

<html>
   <head>
      <title>Using joins on MySQL Tables</title>
   </head>
   <body>
      <?php
         $dbhost = 'localhost';
         $dbuser = 'root';
         $dbpass = 'root@123';
         $dbname = 'GUIDES';
         $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
         
         if($mysqli->connect_errno ) {
            printf("Connect failed: %s<br />", $mysqli->connect_error);
            exit();
         }
         printf('Connected successfully.<br />');
   
         $sql = 'SELECT a.guide_id, a.guide_author, b.guide_count
            FROM guide_tablel a, mcount_tablel b WHERE a.guide_author = b.guide_author';
		 
         $result = $mysqli->query($sql);
           
         if ($result->num_rows > 0) {
            while($row = $result->fetch_assoc()) {
               printf("Id: %s, Author: %s, Count: %d <br />", 
                  $row["guide_id"], 
                  $row["guide_author"], 
                  $row["guide_count"]);               
            }
         } else {
            printf('No record found.<br />');
         }
         mysqli_free_result($result);
         $mysqli->close();
      ?>
   </body>
</html>

Having executed this snippet, you can access the PHP Script deployed on Apache Web Server and verify the output to be the one mentioned below:

Connected successfully.
Id: 1, Author: Rakesh, Count: 3
Id: 2, Author: Rakesh, Count: 3
Id: 3, Author: Rakesh, Count: 3
Id: 5, Author: Harish, Count: 1

How to use the “ON” and “USING” Clause with MySQL Joins?

In the aforementioned MySQL JOIN query examples, you have leveraged the ON clause to match the records between tables. You can also utilize the USING clause for the same. The primary difference between the two clauses is that for the USING clause, you need to have identical names for matched columns in both tables.

In the “movies” table for one of the aforementioned examples, you have used its primary key with the name “id”. You referred to the same in the “members” table with the name “movie_id”. Say, you want to replace the “movies” tables “id” field to have the name “movie_id”. By doing this, you are ensuring that you have identical matched field names. Here’s the query for the same:

ALTER TABLE `movies` CHANGE `id` `movie_id` INT( 11 ) NOT NULL AUTO_INCREMENT;

Now instead of the ON statement used in the Left Join example, you can use the USING clause for Left Join as follows:

SELECT A.`title` , B.`first_name` , B.`last_name`
FROM `movies` AS A
LEFT JOIN `members` AS B
USING ( `movie_id` )

ON and USING aren’t the only clauses that you can use for MySQL JOINS, MySQL also allows you to use clauses like WHERE, GROUP BY, and even functions like AVG and SUM for the same.

What are the Limitations of MySQL Joins?

A common disadvantage of using MySQL Joins is that they are not as easy to read as subqueries. On top of this, it can be quite confusing as to which join is the appropriate type of join to use to produce the correct desired result. 

Conclusion

This blog discusses the salient aspects of MySQL Join clauses and their importance in great detail. It also gives a brief overview of the benefits and features of MySQL before diving into the MySQL Joins.

Extracting complex data from a diverse set of data sources to carry out an insightful analysis can be challenging, and this is where Hevo saves the day! Hevo offers a faster way to move data from Databases or SaaS applications like MySQL into your Data Warehouse to be visualized in a BI tool. Hevo is fully automated and hence does not require you to code.

VISIT OUR WEBSITE TO EXPLORE HEVO

Want to take Hevo for a spin?SIGN UP and experience the feature-rich Hevo suite first hand. You can also have a look at the unbeatable pricing that will help you choose the right plan for your business needs.