DB2 SQL JOINS

SQL JOINS allows 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 for more conditions. If you have worked on DB2 JOIN LOGICAL FILE, it has almost the 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

• Left Outer join
• Right Outer join
• FULL Outer join

3. Exception Join

• Left Exception join
• Right Exception join

4. Cross Join

Sample Data

To understand joins, we are going to use straightforward 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')

SQLTEST1

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')

SQLTEST2

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')

SQLTEST2

INNER JOIN

INNER Join returns records that are common in all tables based on the given join condition. There are two ways to write INNER Join.

1. With JOIN keyword

SELECT * FROM SQLTEST1 a JOIN SQLTEST2 b ON a.keyf= b.keyf

You can JOIN more than two tables:

SELECT * FROM SQLTEST1 a JOIN SQLTEST2 b ON a.keyf = b.keyf JOIN SQLTEST3 c ON b.keyf =c.keyf

2. Without JOIN keyword

SELECT * FROM SQLTEST1 AS a , SQLTEST2 AS b WHERE a.keyf= b.keyf

This is the same as:

SELECT * FROM SQLTEST1 a JOIN SQLTEST2 b ON a.keyf= b.keyf

1. JOIN keyword is replaced with ","
2. ON keyword is replaced with WHERE clause.
3. ON must be used instead of “WHERE” to specify the join condition when the JOIN keyword is used.

OUTER JOIN

SQL OUTER Join gives

• All data from the PRIMARY table with matching date from the SECONDARY table(s) appended to it.
• If no matching data found in the SECONDARY table(s) for the join condition, a NULL value will be used to fill that space.

There are 3 types of OUTER JOIN.

1. LEFT OUTER JOIN or LEFT JOIN (Yes, we can omit word OUTER)

• The “LEFT OUTER JOIN” table on the leftmost side of the “JOIN keyword” is considered as the “PRIMARY table.”
• table(s) on the JOIN keyword's right side is considered the SECONDARY table(s).

SELECT * FROM SQLTEST1 a LEFT JOIN SQLTEST2 b ON a.keyf= b.keyf

1. ON keyword specify the join condition.
2. All data from SQLTEST1(LEFT Table) with matching date from SQLTEST2(RIGHT table) appended to it.
3. If no matching data found in the SQLTEST2 table for the join condition, a NULL value will be used to fill that space.

LEFT OUTER JOIN or LEFT JOIN

When there are more than two tables in OUTER JOIN, the ON keyword defines which table is primary to which table.

SELECT * FROM SQLTEST1 a    
	LEFT JOIN SQLTEST2 b ON a.keyf = b.keyf                   
        LEFT JOIN SQLTEST3 c ON a.keyf = c.keyf

There are 2 LEFT JOINS in this query.

1. Join 1 > Primary Table : SQLTEST1 Secondary Table : SQLTEST2 based on “ON a.keyf = b.keyf”
2. Join 2 > Primary Table : SQLTEST1 Secondary Table : SQLTEST3 based on “ON a.keyf = c.keyf”

So the output will be

LEFT OUTER JOIN or LEFT JOIN

Now change last ON condition from “ON a.keyf = c.keyf” to “ON b.keyf = c.keyf”

SELECT * FROM SQLTEST1 a    
	LEFT JOIN SQLTEST2 b ON a.keyf = b.keyf                   
        LEFT JOIN SQLTEST3 c ON b.keyf = c.keyf

Again there are 2 LEFT JOINS in this query.

1. Join 1 > Primary Table : SQLTEST1 Secondary Table : SQLTEST2 based on “ON a.keyf = b.keyf”
2. Join 2 > Primary Table : SQLTEST2 Secondary Table : SQLTEST3 based on “ON b.keyf = c.keyf”. Now SQL will try to match table SQLTEST2(not table SQLTEST1) and table SQLTEST3.

Now the output will be changed to

LEFT OUTER JOIN or LEFT JOIN

2. RIGHT OUTER JOIN or RIGHT JOIN

Get Sumit goyal’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

The concept of the RIGHT OUTER JOIN is the same as of LEFT OUTER JOIN. In the case of RIGHT OUTER JOIN right-most table of JOIN keyword, the only difference is PRIMARY TABLE.

SELECT * FROM SQLTEST1 a RIGHT JOIN SQLTEST2 b ON a.keyf= b.keyf

• ON keyword specify the join condition.
• All data from SQLTEST2 (RIGHT Table) with matching date from SQLTEST1 (LEFT table) appended to it.
• If no matching data found in the SQLTEST1 table for the join condition, a NULL value will be used to fill that space.

3. FULL OUTER JOIN or FULL JOIN

FULL JOIN is a combination of LEFT JOIN and RIGHT JOIN. It will get you all records from all tables based on the join conditions.

SELECT * FROM SQLTEST1  a FULL JOIN SQLTEST2 b on a.keyf= b.keyf

FULL OUTER JOIN or FULL JOIN

EXCEPTION JOIN

Here is how SQL EXCEPTION JOIN works.

• It returns rows from the PRIMARY table that don’t have a match in any of the SECONDARY tables.
• Columns that come from the SECONDARY table(s) will always have a null value.

DB2 SQL supports 2 types of EXCEPTION JOINs:

1. Left Exception Join

• The table on the left side of the JOIN keyword will be considered as the PRIMARY table.
• Table(s) on the JOIN keyword's right side will be considered the SECONDARY table.

SELECT* FROM SQLTEST1  a LEFT EXCEPTION JOIN SQLTEST2 b on a.keyf= b.keyf

In this case:

• SQLTEST1 is the PRIMARY table.
• SQLTEST2 is the SECONDARY table.

Left Exception Join

2. Right Exception Join

• The table on the right side of the JOIN keyword will be considered as the PRIMARY table.
• Table(s) on the JOIN keyword's left side will be considered as the SECONDARY table.

SELECT* FROM SQLTEST1  a RIGHT EXCEPTION JOIN SQLTEST2 b on a.keyf= b.keyf

In this case:

• SQLTEST2 is the PRIMARY table.
• SQLTEST1 is the SECONDARY table.

CROSS JOIN

SQL CROSS JOIN returns every possible combination of rows from every table being joined(This is also called Cartesian product).

SELECT * FROM SQLTEST1 a CROSS JOIN SQLTEST2 b

Point to note: There is no "ON" keyword that means no join condition.

Cross join