How to Use INNER Joins to Combine Data from Two Tables in MySQL

Using Inner Joins to Combine Data from Two Tables

In a normalized relational database, individual tables provide limited data set as related information is usually stored in other tables where foreign keys are used to link the data together. For more information about database normalization, refer to article How to Design Relational Database.

What is JOIN?

To combine data from multiple tables to provide useful data to applications and end users, we use JOIN operations.

A join operation combines data from two or more tables to produce a single result set that gives more meaningful information about a set of data. In other words, columns and rows from these tables are put together into one single result set.

There are three types of joins: INNER JOIN, OUTER (LEFT JOIN or RIGHT JOIN), and CROSS JOIN (note that in MySQL, CROSS JOIN is the same as inner join. In other Database Management Systems such as Microsoft SQL Server, cross joins display every combination of all rows in the joined tables. This can be achieved in MySQL by using a join without a common column.)

There are rules to follow when join tables.

When join two tables, the two tables are related by primary key column in one table and foreign key column in another table. In other words, the join conditions are based on primary key column in one table and foreign key column in another table.
If the join conditions are not based on primary key column and foreign key column, they must be based on columns with common data values of the same or similar data types.
The joined columns do not have to have the same column name.
When joining two tables on a composite primary key, all columns that make up the composite primary key must be used in the join. If not, duplicate records will be returned in the result set.
When two tables use the same column name(s), use table_name.column_name or table_alias.column_name format in SELECT clause to differentiate them in the result set.
Use INNER JOIN whenever possible because OUTER JOIN uses a lot more system resources and is much more slower.

Only standard SQLs

In the tutorials about joins, only standard SQLs are used. MySQL has quite a few extensions to ANSI (American National Standards Institute) standard SQLs in join operations. They will not be covered in these tutorials. The purpose is to make your SQL code reusable to other Database systems without too many modifications.

Using Inner Joins

INNER JOIN is the default join type. When you use the keyword JOIN in your query, it defaults to INNER JOIN. INNER JOIN only returns rows in the tables that have matches found on the join column.

Practice #1: Join two tables.

Products table in Northwind database only stores SupplierID which is a foreign key pointing back to SupplierID column in suppliers table. If we want to know the supplier's name for a product, we need to write a query to join with suppliers table to get this information. In this practice, a single result set is returned which displays product name and the supplier's name for each product.

In Firefox (not IE), copy and paste the following SQL to your SQLyog query window. To execute a query, move your cursor anywhere inside the query and then press F9. Note that the SQL needs to end with semi-colon.

/*
This query returns supplier's name for each product.
Note that the result is ordered by column alias SupplierName.
*/
SELECT p.ProductName, 
       s.CompanyName AS SupplierName
FROM products AS p
INNER JOIN suppliers AS s ON p.SupplierID=s.SupplierID
ORDER BY SupplierName;

Query result set - 77 rows returned:
Using inner join.

Practice #2: Join three tables.

/*
This query joins threes tables. It returns both 
supplier's name and category name for each product.
*/
SELECT p.ProductName, 
       s.CompanyName AS SupplierName,
       c.CategoryName
FROM products AS p
INNER JOIN suppliers AS s ON p.SupplierID=s.SupplierID
INNER JOIN categories AS c ON p.CategoryID=c.CategoryID
ORDER BY SupplierName;

Query result set - 77 rows returned:
Using inner join for three tables.

Practice #3: JOIN operation with WHERE clause.

/*
The following two queries return the same result set.
 
The first query displays which companies placed orders 
in between 1998-05-04 and 1998-05-06.
 
The second query displays which companies placed orders 
after 1998-05-03.
*/
 
-- Query 1
SELECT DISTINCT c.CompanyName, o.OrderDate
FROM orders AS o
INNER JOIN Customers AS c ON o.CustomerID=c.CustomerID
WHERE o.OrderDate BETWEEN '1998-05-04' AND '1998-05-06'
ORDER BY o.OrderDate;
 
-- Query 2
SELECT DISTINCT c.CompanyName, o.OrderDate
FROM orders AS o
INNER JOIN Customers AS c ON o.CustomerID=c.CustomerID
WHERE o.OrderDate > '1998-05-03'
ORDER BY o.OrderDate;

Query result set - 11 rows returned:
Using WHERE clause in JOIN query.

Practice #4: Using inner join to return every combination of all rows in the joined tables.

/*
This query returns all possible combinations of all rows in 
categories and employees table. There are 8 rows in 
categories table and 9 rows in employees table. So 
the total number of rows returned is 72 which is 8 x 9.
 
Note that there is no join column(s) used in this 
join operation. This type of join is called cross join
in other DBMS system such as MS SQL Server.
*/
SELECT c.CategoryID, e.EmployeeID 
FROM categories AS c
INNER JOIN employees AS e;

Query result set - 72 rows returned:
Using inner join to return every combination of all rows in the joined tables.

Practice #5: Using Non-standard SQL in a join operation and it's not recommended.

This query returns the same result as Practice #1. It uses non ANSI standard joins in the SQL and I don't recommend it.

/*
This query uses non-standard SQL in the join and it's not
recommended. It puts joined column into WHERE clause which
can be mixed with other possible criteria in the WHERE clause. Not a good 
way to do things.
*/
SELECT p.ProductName, 
       s.CompanyName AS SupplierName
FROM products AS p, suppliers AS s
WHERE p.SupplierID=s.SupplierID
ORDER BY SupplierName;

Query result set - 77 rows returned:
Results of non ansi standard join sql.

Next tutorial: Using Outer Joins
Back to Section Index: Querying Multiple Tables
Back to Tutorial Index: MySQL Database How-to Articles and Tutorials

	Custom Search