{"id":267,"date":"2019-03-31T03:46:34","date_gmt":"2019-03-31T11:46:34","guid":{"rendered":"https:\/\/db2tutorial.com\/?page_id=267"},"modified":"2020-04-11T06:59:49","modified_gmt":"2020-04-11T14:59:49","slug":"db2-join","status":"publish","type":"page","link":"https:\/\/www.db2tutorial.com\/db2-basics\/db2-join\/","title":{"rendered":"Db2 Join"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to query data from two tables using Db2 joins.<\/p>\n\n\n\n

When you want to view the data from multiple tables, you can use the SELECT<\/a><\/code> statement with joins. The join associates the rows from one table with rows from another table based on a specified condition, typically of matching column values.<\/p>\n\n\n\n

Db2 supports various kinds of joins including inner join<\/a>, left outer join<\/a>, right outer join<\/a>, and full outer join<\/a>.<\/p>\n\n\n\n

Let’s setup some sample tables for demonstration.<\/p>\n\n\n\n

Setting sample tables<\/h2>\n\n\n\n

Second, create two new tables named contacts<\/code> and customers<\/code>:<\/p>\n\n\n

CREATE<\/span> TABLE<\/span> contacts (\n    contact_id INT<\/span> NOT<\/span> NULL<\/span> PRIMARY KEY<\/span>, \n    name<\/span>       VARCHAR<\/span>(100<\/span>) NOT<\/span> NULL<\/span>\n);\n\nCREATE<\/span> TABLE<\/span> customers (\n    customer_id INT<\/span> NOT<\/span> NULL<\/span> PRIMARY KEY<\/span>, \n    name<\/span>        VARCHAR<\/span>(100<\/span>) NOT<\/span> NULL<\/span>\n);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, insert some rows into the contacts<\/code> and customers<\/code> tables:<\/p>\n\n\n
INSERT<\/span> INTO<\/span> contacts(contact_id, name<\/span>)\nVALUES<\/span>(1<\/span>,'Amelia'<\/span>),\n    (2<\/span>,'Olivia'<\/span>),\n    (3<\/span>,'Isla'<\/span>),\n    (4<\/span>,'Emily'<\/span>);\n\n\nINSERT<\/span> INTO<\/span> customers(customer_id, name<\/span>)\nVALUES<\/span>(1<\/span>,'Amelia'<\/span>),\n    (2<\/span>,'Isla'<\/span>),\n    (3<\/span>,'Jessica'<\/span>),\n    (4<\/span>,'Lily'<\/span>);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, query data from the contacts<\/code> and customers<\/code> tables:<\/p>\n\n\n
SELECT<\/span> \n    *\nFROM<\/span> \n    contacts;\n\nSELECT<\/span> \n    *\nFROM<\/span> \n    customers;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"db2<\/figure>\n\n\n\n
\"db2<\/figure>\n\n\n\n
Let’s call the contacts<\/code> table the left table and the customers<\/code> table the right table.<\/p>\n\n\n\n
Db2 Inner Join<\/h2>\n\n\n\n
The inner join combines each row from the left table with rows of the right table, it keeps only the rows in which the join condition is true.<\/p>\n\n\n\n
This example uses the INNER JOIN<\/a><\/code> to get the rows from the contacts<\/code> table that have the corresponding rows with the same values in the name<\/code> column of the customers<\/code> table:<\/p>\n\n\n
SELECT<\/span> \n    co.contact_id, \n    co.name contact_name, \n    cu.customer_id, \n    cu.name customer_name\nFROM<\/span> \n    contacts co\n    INNER<\/span> JOIN<\/span> customers cu \n        ON<\/span> cu.name = co.name;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the result set:<\/p>\n\n\n\n
\"db2<\/figure>\n\n\n\n
In this example, the join condition is cu.name = co.name<\/code> which matches the values in the name<\/code> column of the contacts<\/code> table with the values in the name<\/code> column of the customers<\/code> table.<\/p>\n\n\n\n
The following Venn diagram illustrates the result of the inner join of two result sets:<\/p>\n\n\n\n
\"db2<\/figure>\n\n\n\n
Db2 Left Join<\/h2>\n\n\n\n
The left join<\/a> selects data starting from the left table and matches rows in the right table. Similar to the inner join, the left join returns all rows from the left table and the matching rows from the right table. In addition, if a row in the left table does not have a matching row in the right table, the columns of the right table will have nulls.<\/p>\n\n\n\n
Note that the left join is also called the left outer join. The outer keyword is optional.<\/p>\n\n\n\n
The following statement joins the contacts<\/code> table with the customers<\/code> table using left join:<\/p>\n\n\n
SELECT<\/span> \n    co.contact_id, \n    co.name contact_name, \n    cu.customer_id, \n    cu.name customer_name\nFROM<\/span> \n    contacts co\n    LEFT<\/span> JOIN<\/span> customers cu \n        ON<\/span> cu.name = co.name;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the result set:<\/p>\n\n\n\n
\"db2<\/figure>\n\n\n\n
This Venn diagram illustrates the left join of two result sets:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
To get the rows that available only in the left table but not in the right table, you add a WHERE<\/a><\/code> clause to the above query:<\/p>\n\n\n
SELECT<\/span> \n    co.contact_id, \n    co.name contact_name, \n    cu.customer_id, \n    cu.name customer_name\nFROM<\/span> \n    contacts co\n    LEFT<\/span> JOIN<\/span> customers cu \n        ON<\/span> cu.name = co.name\nWHERE<\/span>\n    cu.name IS<\/span> NULL<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"db2<\/figure>\n\n\n\n
And the this Venn diagram illustrates the left join that selects rows available only in the left table:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Db2 Right Join<\/h2>\n\n\n\n
The right join<\/a> or right outer join<\/a>, which is a reversed version of the left join, selects data starting from the right table and matches with the rows in the left table.<\/p>\n\n\n\n
The right join returns a result set that includes all the rows from the right table and the matching rows in the left table. If a row in the right table does not have a matching row in the left table, all columns in the left table will contain nulls.<\/p>\n\n\n\n
The following example uses the right join to query rows from contacts<\/code> and customers<\/code> tables:<\/p>\n\n\n
SELECT<\/span> \n    co.contact_id, \n    co.name contact_name, \n    cu.customer_id, \n    cu.name customer_name\nFROM<\/span> \n    contacts co\n    RIGHT<\/span> JOIN<\/span> customers cu \n        ON<\/span> cu.name = co.name;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"db2<\/figure>\n\n\n\n
Notice that all the rows from the right table (customers<\/code>) are included in the result set.<\/p>\n\n\n\n
Here is the Venn diagram of the right join:<\/p>\n\n\n\n
\"db2<\/figure>\n\n\n\n
In order to get rows that are available only in the right table, you add a WHERE<\/a><\/code> clause to the above query:<\/p>\n\n\n
SELECT<\/span> \n    co.contact_id, \n    co.name contact_name, \n    cu.customer_id, \n    cu.name customer_name\nFROM<\/span> \n    contacts co\n    RIGHT<\/span> JOIN<\/span> customers cu \n        ON<\/span> cu.name = co.name\nWHERE<\/span>\n    co.name IS<\/span> NULL<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"db2<\/figure>\n\n\n\n
And the following Venn diagram illustrates the operation:<\/p>\n\n\n\n
\"db2<\/figure>\n\n\n\n
Db2 full join<\/h2>\n\n\n\n
The full join<\/a> returns a result set that includes all the rows from both left and right tables, with the matching rows from both sides where available. In case there is no match, the missing side will have nulls.<\/p>\n\n\n\n
Note that full join and full outer join are synonyms. The outer keyword is optional.<\/p>\n\n\n\n
This example performs a full join between the contacts<\/code> and customers<\/code> tables:<\/p>\n\n\n
SELECT<\/span> \n    co.contact_id, \n    co.name contact_name, \n    cu.customer_id, \n    cu.name customer_name\nFROM<\/span> \n    contacts co\n    FULL<\/span> JOIN<\/span> customers cu \n        ON<\/span> cu.name = co.name;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"\"<\/figure>\n\n\n\n
The Venn diagram that illustrates the full outer join:<\/p>\n\n\n\n
\"db2<\/figure>\n\n\n\n
To select rows that are available in either left or right table, you exclude rows that are common to both tables by adding a WHERE<\/a><\/code> clause to the above query:<\/p>\n\n\n
SELECT<\/span> \n    co.contact_id, \n    co.name contact_name, \n    cu.customer_id, \n    cu.name customer_name\nFROM<\/span> \n    contacts co\n    FULL<\/span> OUTER<\/span> JOIN<\/span> customers cu \n        ON<\/span> cu.name = co.name\nWHERE<\/span>\n    co.name IS<\/span> NULL<\/span> OR<\/span>\n    cu.name IS<\/span> NULL<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"db2<\/figure>\n\n\n\n
The following Venn diagram illustrates the above operation:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
In this tutorial, you have learned Db2 joins including inner join, left outer join, right outer join, and full outer join to combine rows from two 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