MySQL - Inner Join

MySQL Inner Join

MySQL Inner Join is a type of join that is used to combine records from two related tables, based on common columns from both the tables. These tables are joined together on a specific condition. If the records in both tables satisfy the condition specified, they are combined.

This is a default join; that is, even if the JOIN keyword is used instead of INNER JOIN, tables are joined using matching records of common columns. They are also referred to as an Equijoin.

Syntax

Following is the basic syntax of MySQL Inner Join −

SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name = table_name2.column_name

Example

Creating a table named CUSTOMERS, which contains the personal details of customers including their name, age, address and salary etc.

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),       
   PRIMARY KEY (ID)
);

Now insert values into this table using the INSERT statement as follows −

INSERT INTO CUSTOMERS VALUES 
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00),
(2, 'Khilan', 25, 'Delhi', 1500.00),
(3, 'Kaushik', 23, 'Kota', 2000.00),
(4, 'Chaitali', 25, 'Mumbai', 6500.00),
(5, 'Hardik', 27, 'Bhopal', 8500.00),
(6, 'Komal', 22, 'Hyderabad', 4500.00),
(7, 'Muffy', 24, 'Indore', 10000.00);

The table will be created as −

ORDERS Table −

Let us create another table ORDERS, containing the details of orders made and the date they are made on.

CREATE TABLE ORDERS (
   OID INT NOT NULL,
   DATE VARCHAR (20) NOT NULL,
   CUSTOMER_ID INT NOT NULL,
   AMOUNT DECIMAL (18, 2)
);

Using the INSERT statement, insert values into this table as follows −

INSERT INTO ORDERS VALUES 
(102, '2009-10-08 00:00:00', 3, 3000.00),
(100, '2009-10-08 00:00:00', 3, 1500.00),
(101, '2009-11-20 00:00:00', 2, 1560.00),
(103, '2008-05-20 00:00:00', 4, 2060.00);

The table is displayed as follows −

Inner Join Query −

Let us now combine these two tables using the Inner Join query as shown below −

SELECT ID, NAME, AMOUNT, DATE
FROM CUSTOMERS
INNER JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

Output

The table is displayed as follows −

Joining Multiple Tables Using Inner Join

Using the Inner Join query, we can also join as many tables as possible.

However, only two tables can be joined together on a single condition. This process is done sequentially until all the tables are combined.

Syntax

Following is the syntax to join more than two tables using Inner Join −

SELECT column_name1, column_name2...
FROM table_name1
INNER JOIN 
table_name2
ON condition_1
INNER JOIN 
table_name3
ON condition_2
.
.
.

Example

In this example, let us join three tables including CUSTOMERS and ORDERS along with a new table EMPLOYEE. We will first create the EMPLOYEE table using the query below −

CREATE TABLE EMPLOYEE (
   EID INT NOT NULL,
   EMPLOYEE_NAME VARCHAR (30) NOT NULL,
   SALES_MADE DECIMAL (20)
);

Now, we can insert values into this empty tables using the INSERT statement as follows −

INSERT INTO EMPLOYEE VALUES 
(102, 'SARIKA', 4500),
(100, 'ALEKHYA', 3623),
(101, 'REVATHI', 1291),
(103, 'VIVEK', 3426);

The details of EMPLOYEE table are seen below.

Using the following query, we are combining three tables CUSTOMERS, ORDERS and EMPLOYEE.

SELECT OID, DATE, AMOUNT, EMPLOYEE_NAME FROM CUSTOMERS
INNER JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
INNER JOIN EMPLOYEE
ON ORDERS.OID = EMPLOYEE.EID;

Output

The output is obtained as follows −

Inner Join with WHERE Clause

Inner Join uses WHERE clause to apply constraints on the records to be retrieved from a table.

Syntax

The syntax of Inner Join when used with WHERE clause is given below −

SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name = table_name2.column_name
WHERE condition

Example

Consider the previous two tables CUSTOMERS and ORDERS; and join them using the inner join query by applying some constraints using the WHERE clause.

SELECT ID, NAME, DATE, AMOUNT FROM CUSTOMERS
INNER JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
WHERE ORDERS.AMOUNT > 2000.00;

Output

The table is displayed as follows −

Inner Join Using a Client Program

We can also join two or more than two tables by executing Inner Join operation, using a client program.

Syntax

$sql = 'SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count FROM tutorials_tbl a INNER JOIN tcount_tbl b
ON a.tutorial_author = b.tutorial_author';
$mysqli->query($sql);

sql = "SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count  FROM tutorials_tbl a 
INNER JOIN tcount_tbl b  ON a.tutorial_author = b.tutorial_author";
con.query(sql);  

String sql = "SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count FROM tutorials_tbl a 
INNER JOIN tcount_tbl b ON a.tutorial_author = b.tutorial_author";
st.executeQuery(sql);

using_inner_join_query = "SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS INNER JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUST_ID"
cursorObj.execute(using_inner_join_query)

Example

Following are the programs −

$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbname = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
// printf('Connected successfully.
');
$sql = 'SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count FROM tutorials_tbl a INNER JOIN tcount_tbl b
ON a.tutorial_author = b.tutorial_author';
$result = $mysqli->query($sql);
if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        printf(
            "Id: %s, Author: %s, Count: %d 
",
            $row["tutorial_id"],
            $row["tutorial_author"],
            $row["tutorial_count"]
        );
    }
} else {
    printf('No record found.
');
}
mysqli_free_result($result);
$mysqli->close();

Id: 3, Author: Sanjay, Count: 1

var mysql = require("mysql2");
var con = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "password",
}); //Connecting to MySQL

con.connect(function (err) {
  if (err) throw err;
  //   console.log("Connected successfully...!");
  //   console.log("--------------------------");
  sql = "USE TUTORIALS";
  con.query(sql);
  //inner join
  sql = "SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count  FROM tutorials_tbl a INNER JOIN tcount_tbl b  ON a.tutorial_author = b.tutorial_author";
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result);
  });
});        

[ { tutorial_id: 1, tutorial_author: 'John Poul', tutorial_count: 2 } ]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class InnerJoin {
   public static void main(String[] args) {
      String url = "jdbc:mysql://localhost:3306/TUTORIALS";
      String username = "root";
      String password = "password";
      try {
         Class.forName("com.mysql.cj.jdbc.Driver");
         Connection connection = DriverManager.getConnection(url, username, password);
         Statement statement = connection.createStatement();
         System.out.println("Connected successfully...!");

         //Mysql INNER JOIN...!;
         String sql = "SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count FROM tutorials_tbl a INNER JOIN tcount_tbl b ON a.tutorial_author = b.tutorial_author";
         ResultSet resultSet = statement.executeQuery(sql);
         System.out.println("Table records after INNER join...!");
         while (resultSet.next()){
            System.out.println(resultSet.getString(1)+ " "+ resultSet.getString(2)+" "+resultSet.getString(3));
         }
         connection.close();
      } catch (Exception e) {
         System.out.println(e);
      }
   }
}    

Table records after INNER join...!
1 John Paul 1
3 Sanjay 1

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
cursorObj = connection.cursor()
inner_join_query = f"""SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS INNER JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUST_ID """
cursorObj.execute(inner_join_query)
# Fetching all the rows that meet the criteria
filtered_rows = cursorObj.fetchall()
for row in filtered_rows:
    print(row)
cursorObj.close()
connection.close()    

(3, 'kaushik', 3000, '2009-10-08 00:00:00')
(3, 'kaushik', 1500, '2009-10-08 00:00:00')
(2, 'Khilan', 1560, '2009-11-20 00:00:00')
(4, 'Chaital', 2060, '2008-05-20 00:00:00')