SQL Join Tutorial - How to use JOIN Multiple Tables in SQL query? MySQL Example

Hello guys, if you are wondering how to join multiple tables in SQL to produce a combine result which contains columns from all tables but not sure how to do it then you have come to the right place. SQL Join is one of the basic concepts while working in databases or tables but yet less understood and most confusing topic for beginners and even intermediate developers. I compare Joins in SQL with Recursion in programming in terms of confusion because I have found that these two topics are special in their nature and you can't get it right with casual reading until you understand the concept and its various well. Things get worse when the table locked due to such SQL Join queries which were fired without knowing how much time it would and how big the result set could be. 

This is the most common problem we face in our database environment, I would suggest not running any Join Queries in Production until you have tested that with the production-like environment with a similar amount of data.

I have read a lot of good articles explaining SQL joins on multiple tables with examples of joins but yet most of them don't focus on the importance of data, which I see as most important for understanding joins. 

In my opinion to understand SQL Joins you first need to understand when do you need Joins and then how part of Joins and we will follow the same approach, as I mentioned data is more important I suggest to familiar with example data provided in this SQL join example, once you get the data it would be easier to write SQL joins query and to understand the output of Query.

1. Why we need to Join Tables in SQL? 

Many times we need data from multiple tables or at least two tables for reporting purposes. In SQL, Join is used to fetch data from multiple tables. So it's simple if you need data from more than one table, use Joins.

2. Types of SQL Joins

There are three main categories of Joins in SQL called Inner Join, Outer Join, and Cross Join. Outer Join is further divided into three namely Left Outer Join, Right Outer Join, and Full Outer Join.

Inner Join: Only Matching data from two tables included in the result set.

Left Outer Join: All data from left tables and matching data from the right table. null will be used to populate rows that don't have a match on the right side.

Right Outer Join: All data from the Right table and matching data from the left table. again null will be used to fill cells that don't have matching data on the left side.

Full Outer Join: All data from Both Tables

Cross Join: This is multiplication which results in 8x8=64 rows if both tables have 8 columns and are very careful before running this even accidentally on a large table, which could potentially lock the table for a long time.

3. How SQL Joins Works?

To predict the result of SQL Join queries, you need to understand how Join works. My understanding is simple which might not be the way exact way the database will perform join. In SQL Join we have a common column between two tables and data of this column is used in the comparison. 

How I understood join is to take one row from table1 and compare it with all rows in the table2, based on the type of join I include the row in the result set. for example, if we are doing inner join then only rows with matching key values will be included in ResultSet. 

4. Implicit Join and Explicit Join 

These are two different ways we can write SQL Join Queries.

Explicit Join is joined with ON clause:

select a.*, b.* from table a inner join table b on a.id = b.id;

Implicit Join is Join without ON Clause, instead WHERE Clause is used as a condition.

select a.*, b.* from table a, table b where a.id = b.id;

Though their syntax slightly different performance-wise they are the same. I recommend using the "ON" clause or Explicit Join because once you see this "ON" clause you immediately know it's Join and we are joining two tables.

5. Equi Join Example in MySQL

Equi Join is not a different type of join but a term used to refer to queries that involve two instances of the same table for Join in SQL. One of the best examples of Equi Join which I remember is the following. we have the following table Employee which holds its Manager ID.

Now we need to print a report with Employee and their Manager Name. If you look at closely all the information we need like Employee Name and Manager Name in is just one table EMP, though we don't have a column for Manager name so it's not straightforward to produce the report. 

This problem can easily be solved by joining two instances of the same table and picking up the manager name from the second instance based on the condition specified in the ON Clause.

SQL Join Examples for Beginners

Now, let's see examples of different types of join in SQL class. For all those examples, we'll use the following combination of Employee and Department tables. 

mysql> select  from Employee;
+--------+----------+---------+
| emp_id | emp_name | dept_id |
+--------+----------+---------+
| 1      | Ashwin   | 101     |
| 2      | James    | NULL    |
| 3      | Kathy    | NULL    |
| 4      | Harry    | 102     |
+--------+----------+---------+
4 rows in set (0.00 sec)

mysql> select  from department;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
| 101     | Sales     |
| 102     | Marketing |
| 103     | NULL|
| 104     | NULL|
+---------+-----------+
4 rows in set (0.00 sec)

1. INNER JOIN Example in MySQL

Here is an example of inner join in MySQL. You can see that only Employees who have corresponding records in Department tables are included. James and Kathy were not included because dept_id for him was NULL. Similarly, departments with id 103 and 104 were also not included because there were no employees for them. 

mysql> select e.emp_name, d.dept_name 
       from Employee e 
       INNER JOIN Department d ON e.dept_id=d.dept_id
    -> ;
+----------+-----------+
| emp_name | dept_name |
+----------+-----------+
| Ashwin   | Sales     |
| Harry    | Marketing |
+----------+-----------+
2 rows in set (0.00 sec)

2.  LEFT OUTER JOIN Example in MySQL

Here is an example of Left outer join in MySQL. You can see that in the output, all rows of Employee tables are included and only matching rows of Department tables are included because the Employee table is on the left side of the join statement. 

mysql> select e.emp_name, d.dept_name 
       from Employee e 
       LEFT OUTER JOIN Department d ON e.dept_id=d.dept_id;
+----------+-----------+
| emp_name | dept_name |
+----------+-----------+
| Ashwin   | Sales     |
| James    | NULL      |
| Kathy    | NULL      |
| Harry    | Marketing |
+----------+-----------+
4 rows in set (0.00 sec)

3.  RIGHT OUTER JOIN Example in MySQL

This is an example of a right outer join, its very similar to the example of left outer join but this time we have all the department even if they are no employees on them. It doesn't matter if they have NULL values or not. 

mysql> select e.emp_name, d.dept_name 
       from Employee e 
       RIGHT OUTER JOIN Department d ON e.dept_id=d.dept_id;
+----------+-----------+
| emp_name | dept_name |
+----------+-----------+
| Ashwin   | Sales     |
| Harry    | Marketing |
| NULL     | NULL      |
| NULL     | NULL      |
+----------+-----------+
4 rows in set (0.00 sec)

4. CROSS JOIN Example in MySQL

And, here is the example of cross join, very risk and very simple. If you run this query involving tables with millions of rows, it might blow up your database, take ages to complete, and log, etc. In short, never run this kind of join queries in production, they are just for educational purposes or for someone who knows what they are doing and involves tables with just a few records. 

mysql> select e.emp_name, d.dept_name from Employee e, Department d;
+----------+-----------+
| emp_name | dept_name |
+----------+-----------+
| Ashwin   | Sales     |
| James    | Sales     |
| Kathy    | Sales     |
| Harry    | Sales     |
| Ashwin   | Marketing |
| James    | Marketing |
| Kathy    | Marketing |
| Harry    | Marketing |
| Ashwin   | NULL      |
| James    | NULL      |
| Kathy    | NULL      |
| Harry    | NULL      |
| Ashwin   | NULL      |
| James    | NULL      |
| Kathy    | NULL      |
| Harry    | NULL      |
+----------+-----------+
16 rows in set (0.00 sec)

Important points about SQL join worth remembering:

Here are some important points about the SQL Join concept which every programmer should remember:

1. Apart from = (equals to) in join which is called equijoin, you can also use other conditional operators like >=, <=, <>, <, >, etc.

2. In T-SQL and Oracle SQL and even in MySQL if you just specify "join" it will be interpreted as "inner join" but it's not guaranteed to all databases.

3. You can join on columns that are not included in the result set.

4. If both tables have a column with the same name then you must specify that column with a table name to avoid ambiguity.

5.You can join more than two tables in SQL as shown in this example. 

That's all about how to use JOIN in  MySQL. This is one of the most important concepts to learn for any developer and you should spend some time learning JOINs. At least you should be aware of different types of join like LEFT and RIGHT as well as INNER And OUTER JOIN. You should also know how to join more than 2 tablets in a single query to produce results. 

Other related SQL queries, Interview questions, and articles:

• How to join three tables in one single SQL query (solution)
• Write a SQL query to find all table names on a database in MySQL (solution)
• 5 Courses to learn Database and SQL Better (courses)
• The real difference between WHERE and HAVING clause in SQL? (answer)
• 5 Courses to learn Oracle and Microsoft SQL Server database (courses)
• Difference between Self and Equi Join in SQL? (answer)
• 4 Free Books to learn Microsoft SQL Server database (books)
• Top 5 Websites to learn SQL online for FREE? (websites)
• 5 Free Courses to learn Database and SQL (free courses)
• What is the difference between View and Materialized View in Database? (answer)
• Difference between clustered and non-clustered indexes in SQL? (answer)
• Difference between Primary and Candidate key in the table? (answer)
• 5 Free Courses to learn T-SQL and SQL Server for Beginners (Courses)
• Difference between the Unique and Primary keys in the table? (answer)
• 5 Free Courses to learn Oracle and SQL Server? (courses)
• Top 5 Courses to learn Microsoft SQL Server in-depth (courses)
• How to migrate SQL queries from Oracle to SQL Server? (answer)
• Top 5 Websites to learn SQL online for FREE? (resource)
• What is the difference between UNION and UNION ALL in SQL? (answer)
• Write a SQL query to copy or backup a table in MySQL (solution)
• How do you find the duplicate rows in a table on a database? (solution)
• 5 Advanced SQL books to level up your SQL skills (books)
• Top 5 Courses to learn MySQL Database for Beginners (Courses)
• Top 5 Courses to learn PostgreSQL in-depth (courses)
• 5 Free Courses to learn T-SQL and SQL Server for Beginners (Courses)
• Difference between Primary and Foreign keys in the table? (answer)

Thanks for reading this article, if you like this SQL Join Tutorial, then please share it with your friends and colleagues. If you have any questions or feedback, then please drop a note.