SQL JOINS

DB2 SQL JOINS : BASIC


SQL JOINS allow you to combine records for multiple tables based on some condition. This condition in most cases is “common value(s)” between tables needed to join. But there are options of more conditions. If you have worked on DB2 JOIN LOGICAL FILE it has almost same concept as SQL JOINS but SQL JOINS give you more options and are very easy to use.

Type of SQL JOINS:

  1. Inner Join
  2. Outer Join
    1. Left Outer join
    2. Right Outer join
    3. FULL Outer join
  3. Exception Join
    1. Left Exception join
    2. Right Exception join
  4. Cross Join

 

Sample Data

To understand joins we are going to use very simple data in 3 tables.


Table 1 : SQLTEST1

CREATE TABLE SQLTEST1 (file_key for keyf char(10),file_dob for dobf date)
INSERT INTO SQLTEST1 values('01','1999-10-10'),('02','2000-10-10'),('03','2001-11-11'),('06','2016-12-12')
FILE_KEY FILE_DOB
01 1999-10-10
02 2000-10-10
03 2001-11-11
06 2016-12-12

Table 2 : SQLTEST2

CREATE TABLE SQLTEST2 ( file_key for keyf char(10), file_name for namef char(20)) 
INSERT INTO SQLTEST2 values('01','SUMIT'),('02','AMIT'),('04','AMAN'),('05','RAM')
FILE_KEY FILE_NAME
01 SUMIT
02 AMIT
04 AMAN
05 RAM

Table 3 : SQLTEST3

CREATE TABLE SQLTEST3 (file_key for keyf char(10) ,  file_last for lastf char(20))
INSERT INTO SQLTEST3 values('01','Goyal'),('05','Kumar'),('06','Gupta')
FILE_KEY FILE_LAST
01 Goyal
05 Kumar
06 Gupta

IBM i developer.

View Comments
There are currently no comments.