mysql

Khóa học miễn phí MySQL – Inner Join nhận dự án làm có lương

Posted on by user

MySQL – Inner Join

Table of content

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.

Inner Join

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 −

ID NAME AGE ADDRESS SALARY
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

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 −

OID DATE CUSTOMER_ID AMOUNT
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

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 −

ID NAME DATE AMOUNT
3 Kaushik 2009-10-08 00:00:00 3000.00
3 Kaushik 2009-10-08 00:00:00 1500.00
2 Khilan 2009-11-20 00:00:00 1560.00
4 Chaitali 2008-05-20 00:00:00 2060.00

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.

EID EMPLOYEE_NAME SALES_MADE
102 SARIKA 4500
100 ALEKHYA 3623
101 REVATHI 1291
103 VIVEK 3426

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 −

OID DATE AMOUNT EMPLOYEE_NAME
102 2009-10-08 00:00:00 3000.00 SARIKA
100 2009-10-08 00:00:00 1500.00 ALEKHYA
101 2009-11-20 00:00:00 1560.00 REVATHI
103 2008-05-20 00:00:00 2060.00 VIVEK

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 −

ID NAME DATE AMOUNT
3 Kaushik 2009-10-08 00:00:00 3000.00
4 Chaitali 2008-05-20 00:00:00 2060.00

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

To join tables with common fields use Inner Join operation through a PHP program, we need to execute the JOIN clause using the mysqli function query() as follows −

$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);

To join tables with common fields use Inner Join operation through a JavaScript program, we need to execute the JOIN clause using the query() function of mysql2 library as follows −

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);

To join tables with common fields use Inner Join operation through a Java program, we need to execute the JOIN clause using the JDBC function executeQuery() as follows −

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);

To join tables with common fields use Inner Join operation through a Python program, we need to execute the JOIN clause using the execute() function of the MySQL Connector/Python as follows −

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();

Output

The output obtained is as follows −

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);
  });
});

Output

The output produced is as follows −

[ { 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);
      }
   }
}

Output

The output obtained is as shown below −

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()

Output

Following is the output of the above code −

(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'')

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

user