DB2 INNER JOIN

An INNER JOIN is used to combine rows from two tables when they have matching values in specified columns. It retrieves only those rows that have a match in both tables based on the join condition. The join condition is usually specified with the ON keyword, which defines the column relationship between the tables. This type of join is commonly used to establish relationships between records in different tables.

For instance, if we want to fetch employee names and their corresponding department names, we would use an INNER JOIN between EMPLOYEE and DEPARTMENT tables. In the case of EMPLOYEE and DEPARTMENT tables, a matching DEPT_ID column in both tables serves as the relationship key.

Syntax -

SELECT columns
  FROM table1
       INNER JOIN table2 
	ON table1.column = table2.column
 WHERE condition;

• columns: Specifies the columns to retrieve from each table.
• table1: The first table in the INNER JOIN.
• table2: The second table in the INNER JOIN.
• ON: Defines the join condition by matching columns between the tables.
• WHERE: An optional condition to filter rows after joining.

Examples -

Scenario - Using an INNER JOIN, we can combine the EMPLOYEE and DEPARTMENT tables to create a result showing each employee's department name.

EMPLOYEE Table

EMP_ID	EMP_NAME	DEPT_ID
101	Alice	1
102	Bob	2
103	Charlie	3
104	David	1
105	Eva	2

DEPARTMENT Table

DEPT_ID	DEPT_NAME
1	Human Resources
2	Finance
3	IT

Query -

SELECT EMPLOYEE.EMP_ID, 
       EMPLOYEE.EMP_NAME, 
	   DEPARTMENT.DEPT_NAME
  FROM EMPLOYEE
        INNER JOIN DEPARTMENT 
	ON EMPLOYEE.DEPT_ID = DEPARTMENT.DEPT_ID;

Result Table

EMP_ID	EMP_NAME	DEPT_NAME
101	Alice	Human Resources
102	Bob	Finance
103	Charlie	IT
104	David	Human Resources
105	Eva	Finance

---