MySQL Tutorial

This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

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 −
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
PHPNodeJSJavaPython
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);
﻿
Example
Following are the programs −
PHPNodeJSJavaPython
$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
﻿
﻿
﻿
﻿
﻿
﻿

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

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

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

MySQL Basics

MySQL Databases

MySQL Users

MySQL Tables

MySQL Queries

MySQL Views

MySQL Indexes

MySQL Operators and Clauses

MySQL Joins

MySQL Keys

MySQL Triggers

MySQL Data Types

MySQL Regular Expressions

MySQL Fulltext Search

MySQL Functions & Operators

MySQL Misc Concepts

MySQL Useful Resources

MySQL Tutorial

Inner Join

MySQL Inner Join

Syntax

Example

Output

Joining Multiple Tables Using Inner Join

Syntax

Example

Output

Inner Join with WHERE Clause

Syntax

Example

Output

Inner Join Using a Client Program

Syntax

Example

Output