MySQL INNER JOIN

MySQL inner join connects two tables by using their common columns. It is the basic join of the MySQL system. It is used to returns only those results from the tables that match the specified condition and hides other rows and columns. MySQL assumes it as a default Join, so it is optional to use the Inner Join keyword with the query.

Syntax

The syntax of the inner join shows below. This syntax works on two tables.

SELECT
Column1 data type constraint,
Column2 data type constraint,
column data type constraint,
FROM table name1
INNER JOIN table2 ON condition1;

The syntax of the inner join shows below. This syntax works on multiple tables.

SELECT
Column1 data type constraint,
Column2 data type constraint,
column data type constraint,
FROM table name1
INNER JOIN table2 ON condition1
INNER JOIN table3 ON condition2;

Examples of the MySQL inner join

Let us understand it with the help of an example. First, we will create two tables in the MySQL system with constraints and a common column.

1) Example: the primary inner join example shows below.

Execute the below query to join two tables. The below statement will return all columns using the "INNER JOIN" query.

mysql> select d.department_name, d.admissions, s.subject_name, s.students
    -> FROM department d
    -> INNER JOIN subject s;

OUTPUT

The above image shows all the columns of the two tables. The "department_name", & "admissions" of the department table and "subject_name", & "students" columns of the subject table.

2) Example: the inner join with multiple table examples shows below.

Execute the below query to join two tables. We must specify the condition to join two tables.

mysql> select d.department_name, d.department_id, s.subject_name, s.subject_id
    -> FROM department d
    -> INNER JOIN subject s
    -> ON d.department_id = s.subject_id;

OUTPUT

The above output image shows the combined result of the two tables. The "department_name" and "department_id" columns have in the department table. The "subject_name" and "students" columns have in the subject table.

3) Example: the inner join with multiple table examples shows below.

Execute the below query to join two tables.

mysql> select *
    -> FROM department d
    -> INNER JOIN subject s
    -> ON d.department_id = s.subject_id;

OUTPUT

The above output image shows the combined columns of the two tables. For example, this query shows complete columns of the department and student tables.

4) Example: the inner join with multiple table examples shows below.

Execute the below query to join two tables. For example, this "INNER JOIN" query uses "WHERE" with "AND" conditions.

mysql> select d.department_name, d.department_id,
    -> s.subject_name, s.subject_id
    -> FROM department d
    -> INNER JOIN subject s
    -> WHERE d.department_name = "Bachelor of Technology"
    -> AND  s.subject_name = "Electronics";

OUTPUT

This query shows complete columns of the department and student tables. The above image shows the combined columns of the two tables. The inner query works with the logical condition. Both conditions fulfill using "AND" logical condition.

5) Example: the inner join with multiple tables' example shows below.

Execute the below query to join two tables. Two tables join required common columns using conditions. For example, the "INNER JOIN" query uses "WHERE" with "OR" conditions.

mysql> select d.department_name, d.department_id,
    -> s.subject_name, s.subject_id
    -> FROM department d
    -> INNER JOIN subject s
    -> WHERE d.department_name = "Bachelor of Technology"
    -> OR  s.subject_name = "Electronics";

OUTPUT

The above image shows the combined columns of the two tables. This query shows complete columns of the department and student tables. The inner query works with the logical condition. The "electronics" column and "bachelor of technology" column display both data rows of the table.

6) Example: the inner join with the "USING" clause example shows below.

Execute the below query to join two tables. For example, the "INNER JOIN" query uses "USING" clause with the "subject_id" foreign key.

mysql> select d.department_name, d.department_id,
    -> s.subject_name, s.subject_id
    -> FROM department d
    -> INNER JOIN subject s
    -> USING (subject_id)

OUTPUT

The above output table displays combined columns of the department and subject tables. The "subject_id" column links two tables using a foreign key. The above table is the result of the inner join query with the "USING" clause.

7) Example: the inner join with the "GROUP BY" clause example shows below.

Execute the below query to join two tables. For example, the "INNER JOIN" query uses "GROUP BY" clause with the "subject_id" foreign key. It also uses AND logical operator.

mysql> select d.department_name, d.department_id,
    -> s.subject_name, s.subject_id
    -> FROM department d
    -> INNER JOIN subject s
    -> WHERE d.department_name = "Bachelor of Technology"
    -> AND s.subject_name = "Computer Science"
    -> GROUP BY subject_id;

OUTPUT

The above output image displays combined columns of the department and subject tables. The "subject_id" column connects two tables using a foreign key. The above table works inner join query with the "GROUP BY" clause.

8) Example: the inner join with the "ORDER BY" clause example shows below.

Execute the below query to join two tables. For example, the "INNER JOIN" query uses "ORDER BY" and "using" clauses with the "subject_id" foreign key. The inner join uses OR logical operator.

mysql> select d.department_name, d.department_id,
    -> s.subject_name, s.subject_id
    -> FROM department d
    -> INNER JOIN subject s
    -> USING (subject_id)
    -> WHERE d.department_name = "Bachelor of Technology"
    -> OR  s.subject_name = "Computer Science"
    -> ORDER BY subject_id DESC;

OUTPUT

The above output table displays combined columns of the department and subject tables. It is the result of the inner join query with the "USING" clause. Here, the table applies the inner join query with the "ORDER BY" clause. The table data display in descending order.