SQL - Inner Join

---

A Join is an operation performed on database tables to fetch data from related tables, based on common fields/columns.

There are two types of major joins. They are listed below −

• Inner Join
• Outer Join

Remaining joins like Cross Join, Left Join, Right Join, Full Join etc. are just subtypes of these joins. In this chapter, however, we will only learn about the Inner Join.

SQL Inner Join

SQL Inner Join is a type of join that is used to combine records from two related tables, based on the common columns

To elaborate, Inner Join query compares each row of the first table with each row of second table to find all pairs of rows which satisfy the join-predicate. When the join-predicate is satisfied, column values for each matched pair of rows of both tables are combined into a result row.

Typically, the join predicate involves a foreign key from one table and its associated key in the other table.

• The Inner Join is a default join; i.e., even if the “Join” keyword is used instead of “Inner Join”, tables are joined using matching records of common columns, by default.

• The most important and frequently used join in SQL is the Inner Join. It is also referred as Equijoin.

Let us look at an example scenario to have a better understanding.

Suppose we have the information of a person is scattered across two tables namely EmpDetails and Marital status −

Where,

• EmpDetails table holds details EmpID, Name and Salary.

• And the MaritalStatus table holds the details EmpID, Age, Status.

When we perform inner-join or, join operation on these two tables based on the join predicate EmpDetails.EmpID = MaritalStatus.EmpID the resultant records hold the following info: ID, Name, Salary, Age and, Status of the matched records.

Syntax

Following is the basic syntax of SQL Inner Join −

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

Let us break down the given syntax −

• Firstly, the data in two tables are joined together using the INNER JOIN query.
• The SELECT query selects certain columns from the joined table. Use Asterisk (*), if you want to display the entire joined table.

Example

Assume we have created a table named Customers, which contains the personal details of customers including their name, age, address and salary etc., using the following query −

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 (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, 'kaushik', 23, 'Kota', 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5, 'Hardik', 27, 'Bhopal', 8500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6, 'Komal', 22, 'MP', 4500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (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 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

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 (OID, DATE, CUSTOMER_ID, AMOUNT)
VALUES (102, '2009-10-08 00:00:00', 3, 3000.00);

INSERT INTO ORDERS (OID, DATE, CUSTOMER_ID, AMOUNT)
VALUES (100, '2009-10-08 00:00:00', 3, 1500.00);

INSERT INTO ORDERS (OID, DATE, CUSTOMER_ID, AMOUNT)
VALUES (101, '2009-11-20 00:00:00', 2, 1560.00);

INSERT INTO ORDERS (OID, DATE, CUSTOMER_ID, AMOUNT)
VALUES (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 |
+-----+---------------------+-------------+---------+

Join Query

Let us now try to 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 result of this query is obtained as follows −

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

Joining Multiple Tables Using Inner Join

Until now, we have only learnt how to join two tables using the Inner Join query. Using the Inner Join query, we can join as many tables as possible by specifying the condition (with which these tables are to be joined).

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
.
.
.

Note that, even in this case, only two tables can be joined together on a single condition. This process is done sequentially until all the tables are combined.

Example

Let us make use of the previous tables Customers and Orders along with a new table Employee. We will try to 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);
INSERT INTO EMPLOYEE VALUES (100, 'ALEKHYA', 3623);
INSERT INTO EMPLOYEE VALUES (101, 'REVATHI', 1291);
INSERT INTO EMPLOYEE VALUES (103, 'VIVEK', 3426);

The details of Employee table can be seen below.

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

Using the following query, we can combine 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 result of the inner join query above is shown 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

Clauses in SQL work same as the conditional statements in high-level programming languages. There are various clauses that SQL uses to constraint the data; such as WHERE clause, GROUP BY clause, ORDER BY clause, UNION clause etc.

The WHERE clause is used to filter the data from tables. This clause specifies a condition to retrieve only those records that satisfy it.

Inner Join uses WHERE clause to apply more constraints on the data to be retrieved. For instance, while retrieving the employee records of an organization, say we only want to check the data of employees that earn more than 25000 in a month. So we specify a WHERE condition (salary > 25000) to retrieve only those employee records.

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 try to join them using the inner join query by applying some constraints using the WHERE clause.

Here, we are retrieving the ID and Name from the Customers table and DATE and Amount from the ORDERS table where the amount paid is higher than 2000.

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

Output

The resultant table after applying the where clause with inner join contains the rows that has amount values greater than 2000.00 −

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