{"id":321,"date":"2019-04-06T19:45:38","date_gmt":"2019-04-07T03:45:38","guid":{"rendered":"https:\/\/db2tutorial.com\/?page_id=321"},"modified":"2020-04-11T06:59:37","modified_gmt":"2020-04-11T14:59:37","slug":"db2-self-join","status":"publish","type":"page","link":"https:\/\/www.db2tutorial.com\/db2-basics\/db2-self-join\/","title":{"rendered":"Db2 Self-Join"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use Db2 self-join to join a table to itself to query hierarchical query or comparing rows within the same table.<\/p>\n\n\n\n

Introduction to Db2 self-join<\/h2>\n\n\n\n

A self-join is a query in which a table is joined to itself using either INNER JOIN<\/a><\/code> or LEFT JOIN<\/a><\/code> clause. When you reference a table twice in a query, you have to use the table aliases<\/a>.<\/p>\n\n\n\n

The following illustrates the syntax of a self-join in Db2:<\/p>\n\n\n

SELECT<\/span>\n    select_list\nFROM<\/span>\n    table_name t1\n    {LEFT<\/span> | INNER<\/span>} JOIN<\/span> table_name t2\n        ON<\/span> join_condition;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The self-join is useful to query hierarchical data or compare rows within the same table.<\/p>\n\n\n\n
Db2 Self-Join examples<\/h2>\n\n\n\n
See the following genres<\/code> table from the sample database<\/a>:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
In this table, the parent_id<\/code> column references to the genre_id<\/code> column to establish a parent-child relationship.<\/p>\n\n\n\n
It means that for a genre you can use the value in the parent_id<\/code> column to find its corresponding parent genre. The top genre has NULL<\/code> in the parent_id<\/code> column.<\/p>\n\n\n\n
This query joins the genres<\/code> table to itself by comparing values in the genre_id<\/code> and parent_id<\/code> columns. It returns all genres and their corresponding parent genres in the hierarchy of the genres:<\/p>\n\n\n
SELECT<\/span> \n    p.genre parent_genre, \n    c.genre sub_genre\nFROM<\/span> \n    genres c\n    INNER<\/span> JOIN<\/span> genres p \n        ON<\/span> p.genre_id = c.parent_id\nORDER<\/span> BY<\/span> \n    parent_genre, \n    sub_genre;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the partial output:<\/p>\n\n\n\n
\"db2<\/figure>\n\n\n\n
In this example, the c<\/code> and p<\/code> are the table aliases for child and parent respectively.<\/p>\n\n\n\n
To include the top genre in the result set, you need to use the LEFT JOIN<\/code> instead of the INNER JOIN<\/code> as shown in the following query:<\/p>\n\n\n
SELECT<\/span> \n    p.genre parent_genre, \n    c.genre sub_genre\nFROM<\/span> \n    genres c\n    LEFT<\/span> JOIN<\/span> genres p \n        ON<\/span> p.genre_id = c.parent_id\nORDER<\/span> BY<\/span> \n    parent_genre NULLS<\/span> FIRST<\/span>, \n    sub_genre;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following picture shows the partial output:<\/p>\n\n\n\n
\"db2<\/figure>\n\n\n\n
In this tutorial, you have learned how to use Db2 self-join to join a table to itself using either INNER JOIN<\/code> or LEFT JOIN<\/code>.<\/p>\n
\n\t
\n\t\t
Was this tutorial helpful ?<\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t