SQL INNER JOIN in DB2 for i SQL

SQL INNER JOIN in DB2 for i SQL

INNER JOIN

An INNER JOIN returns only those rows from each table in JOIN condition that have matching values in JOIN columns.

Rows that does not have a matching values in JOIN columns does not appear in the result list.

Create table STUDENT

Create table query

CREATE TABLE STUDENT(ROLLNO INTEGER NOT NULL WITH DEFAULT, NAME  
CHAR (20 ) NOT NULL WITH DEFAULT, GENDER CHAR (1 ) NOT NULL WITH 
DEFAULT, CONSTRAINT UNIQUEID UNIQUE (ROLLNO))                    

Insert data query

INSERT INTO EASYCLASS1/STUDENT (ROLLNO, NAME, GENDER)   VALUES 
  (1, 'AMIT', 'M'),                                            
  (2, 'AMAN', 'M'),                                            
  (3, 'HEMANT', 'M'),                                          
  (4, 'ANNAMALAI', 'M'),                                       
  (5, 'KESHAV', 'M')                                           

Display data of STUDENT table

SELECT * FROM STUDENT

ROLLNO   NAME                  GENDER
     1   AMIT                    M   
     2   AMAN                    M   
     3   HEMANT                  M   
     4   ANNAMALAI               M   
     5   KESHAV                  M   

Create table COURSE

Create table query

CREATE TABLE COURSE(ROLLNO INT NOT NULL WITH DEFAULT, COURSEID CHAR 
( 2) NOT NULL WITH DEFAULT, COURSENAME CHAR ( 20) NOT NULL WITH     
DEFAULT, CONSTRAINT UNIQUEID2 UNIQUE (ROLLNO, COURSEID))            

Insert data query

INSERT INTO EASYCLASS1/COURSE  (ROLLNO, COURSEID,COURSENAME) 
   VALUES                                                    
  (1, 'C1', 'IT'),                                           
  (2, 'C2', 'EC'),
  (6, 'C3', 'EL')

Display data of COURSE table

SELECT * FROM COURSE

ROLLNO   COURSEID  COURSENAME
     1      C1     IT        
     2      C2     EC
     6      C3     EL

Create table GRADE

Create table query

CREATE TABLE GRADE(ROLLNO INT NOT NULL WITH DEFAULT, COURSEID CHAR
( 2) NOT NULL WITH DEFAULT, MARKS INT NOT NULL WITH DEFAULT,      
CONSTRAINT UNIQUEID3 UNIQUE (ROLLNO, COURSEID))                   

Insert data query

INSERT INTO EASYCLASS1/GRADE   (ROLLNO, COURSEID,MARKS)
   VALUES                                              
  (1, 'C1', 80 )                                       

Display data of GRADE table

SELECT * FROM GRADE

ROLLNO   COURSEID          MARKS
     1      C1                80

INNER JOIN using the JOIN syntax

The INNER JOIN keyword selects records that have matching values in both tables based on JOIN columns.

INNER is the default join type for JOIN, so when you write JOIN the parser actually writes INNER JOIN. JOIN and INNER JOIN will return the same result.

Syntax using INNER JOIN

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name

To use the INNER JOIN syntax, both the join tables should be listed in the FROM clause, along with the JOIN conditions and is specified after the ON keyword.

The JOIN condition can be any comparison operator, it does not need to be the equal operator.

Multiple JOIN condition can be specified in the ON clause separated by the AND keyword.

Example using INNER JOIN keyword to JOIN two tables

JOIN table STUDENT and COURSE based upon ROLLNO

SELECT S.ROLLNO, S.NAME, S.GENDER, C.COURSEID, C.COURSENAME FROM
student s 
INNER JOIN course c ON s.rollno = c.rollno            

Result

ROLLNO   NAME                  GENDER  COURSEID  COURSENAME 
     1   AMIT                    M        C1     IT         
     2   AMAN                    M        C2     EC         

Example using INNER JOIN keyword to JOIN three tables

JOIN table STUDENT and COURSE and GRADE based upon ROLLNO

SELECT S.ROLLNO, S.NAME, S.GENDER, C.COURSEID, C.COURSENAME,G.MARKS
FROM student s 
INNER JOIN course c ON s.rollno = c.rollno 
INNER JOIN grade g on s.rollno = g.rollno                                

Result

ROLLNO   NAME                  GENDER  COURSEID  COURSENAME                    MARKS
     1   AMIT                    M        C1     IT                               80

INNER JOIN using the WHERE clause

We can use the WHERE clause to perform the same INNER JOIN. Just specify the JOIN condition in the WHERE clause.

The JOIN query can be written as below using WHERE clause for joining two tables based on rollno.

SELECT S.ROLLNO, S.NAME, S.GENDER, C.COURSEID, C.COURSENAME FROM 
student s, course c WHERE s.rollno = c.rollno                    

Result:

ROLLNO   NAME                  GENDER  COURSEID  COURSENAME
     1   AMIT                    M        C1     IT        
     2   AMAN                    M        C2     EC        

INNER JOIN with the USING clause

We can use the USING clause to perform INNER JOIN. Here, each column from the left table is compared to a column with the same name in the right table.

The JOIN query can be written as below with the USING clause for joining two tables based on rollno.

seLECT   ROLLNO, S.NAME, S.GENDER, C.COURSEID, C.COURSENAME FROM  
student s 
INNER JOIN course c USING(rollno)                       

Result:

ROLLNO   NAME                  GENDER  COURSEID  COURSENAME
     1   AMIT                    M        C1     IT        
     2   AMAN                    M        C2     EC        

Here if join on more than one table column, then specify the columns after rollno, within brackets of USING keyword.