{"id":290,"date":"2019-04-05T21:47:33","date_gmt":"2019-04-06T05:47:33","guid":{"rendered":"https:\/\/db2tutorial.com\/?page_id=290"},"modified":"2020-04-11T06:59:40","modified_gmt":"2020-04-11T14:59:40","slug":"db2-inner-join","status":"publish","type":"page","link":"https:\/\/www.db2tutorial.com\/db2-basics\/db2-inner-join\/","title":{"rendered":"Db2 INNER JOIN"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the Db2 INNER JOIN<\/code> clause to query data from two or more related tables.<\/p>\n\n\n\n

Introduction to Db2 INNER JOIN clause<\/h2>\n\n\n\n

The INNER JOIN<\/code> is one of the join<\/a> clauses that allow you to query data from two or more related tables. The INNER JOIN<\/code> clause combines each row from the first table with every row from the second table, keeping only the rows in which the join condition evaluates to true.<\/p>\n\n\n\n

The following shows the syntax of joining two tables using the INNER JOIN<\/code> clause:<\/p>\n\n\n

SELECT<\/span> \n    select_list\nFROM<\/span>\n    T1\nINNER<\/span> JOIN<\/span> 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
In this syntax, the join_condition<\/code> is a Boolean expression that evaluates to true, false, and unknown. Typically, it matches the values of the columns in the table T1<\/code> with the values of the columns in the table T2<\/code> using the equality operator (=<\/code>).<\/p>\n\n\n\n
The following Venn diagram illustrates the inner join of two tables:<\/p>\n\n\n\n
\"db2<\/figure>\n\n\n\n
Note that if T1<\/code> and T2<\/code> tables have the same column names, you must fully qualify these column names in the query e.g., T1.id<\/code> and T2.id<\/code> or you will get an error. In case the table names are long, you can use the table aliases to save some typing.<\/p>\n\n\n\n
To inner join more than two tables, you use multiple INNER JOIN<\/code> clauses as shown in the following query:<\/p>\n\n\n
SELECT<\/span> \n    select_list\nFROM<\/span>\n    T1\nINNER<\/span> JOIN<\/span> T2 ON<\/span> join_condition2\nINNER<\/span> JOIN<\/span> T3 on<\/span> join_condition3\n...;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Db2 INNER JOIN examples<\/h2>\n\n\n\n
Let’s take some examples of using the INNER JOIN<\/code> clause.<\/p>\n\n\n\n
1) Using DB2 INNER JOIN to join two tables example<\/h3>\n\n\n\n
The following diagram shows the books<\/code> and publishers<\/code> tables:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
In this model, one publisher may have zero or many books while each book belongs to zero or one publisher. The relationship between the books<\/code> table and the publishers<\/code> table is zero-to-many.<\/p>\n\n\n\n
The publisher_id<\/code> column of the books<\/code> table links to the publisher_id<\/code> column of the publishers<\/code> table to establish this relationship.<\/p>\n\n\n\n
The following example uses the INNER JOIN<\/code> clause to join the books<\/code> table with the publishers<\/code> table:<\/p>\n\n\n
SELECT<\/span> \n    b.title, \n    p.name, \n    b.publisher_id, \n    p.publisher_id\nFROM<\/span> \n    books b\nINNER<\/span> JOIN<\/span> publishers p \n    ON<\/span> p.publisher_id = b.publisher_id\nORDER<\/span> BY<\/span> \n    b.title;\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
\"\"<\/figure>\n\n\n\n
In this example, the INNER JOIN<\/code> clause compares the value in the publisher_id<\/code> column of each row in the books<\/code> table with the value of the publisher_id<\/code> column of each row in the publishers<\/code> table. If they are equal, The INNER JOIN<\/code> combines columns of these two rows into a row and includes this row in the result set.<\/p>\n\n\n\n
2) Using DB2 INNER JOIN to join three tables example<\/h3>\n\n\n\n
See the following books<\/code>, authors<\/code>, and book_authors<\/code> tables:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
In this model, one book is written by one or many authors. And one author may write one or many books. The relationship between the books<\/code> table and the authors<\/code> table is many-to-many.<\/p>\n\n\n\n
To model this many-to-many relationship, we have an associated<\/em> table: book_authors<\/code>. Note that this associate table is also known as a junction table, a join table, or a cross-reference table.<\/p>\n\n\n\n
In order to get the book titles from the books<\/code> table and author’s names from the authors<\/code> table, we join three tables using the INNER JOIN<\/code> clause as follows:<\/p>\n\n\n
SELECT<\/span> \n    b.title, \n    a.first_name, \n    a.last_name\nFROM<\/span> \n    books b\nINNER<\/span> JOIN<\/span> book_authors ba \n    ON<\/span> ba.book_id = b.book_id\nINNER<\/span> JOIN<\/span> authors<\/span> a \n    ON<\/span> a.author_id = ba.author_id\nORDER<\/span> BY<\/span> \n    b.title;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the partial result set:<\/p>\n\n\n\n
\"db2<\/figure>\n\n\n\n
In this tutorial, you have learned how to use the Db2 INNER JOIN<\/code> clause to query data from two or more tables.<\/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