Srinimf

DB2 SQL: How to Write Self-join example

January 23, 2016

Srini

Why Do you need it? To refer to fields within the same table you can go for self-join. Below is the example SQL query for self-join.

Let us first create two copies of the same table MATCH, M1 and M2:

CREATE TABLE M1 AS SELECT * FROM MATCH;

CREATE TABLE M2 AS SELECT * FROM MATCH;

Then, we could join M1 and M2:

SELECT M1.PAPER FROM M1, M2 
WHERE M1.PAPER = M2.PAPER  
AND M1.CODE = 601 AND M2.CODE = 602;

The self-join allows us to perform this sort of operation without actually having to copy the table. We can just act as if we had two copies.