{"id":307,"date":"2019-04-05T23:44:19","date_gmt":"2019-04-06T07:44:19","guid":{"rendered":"https:\/\/db2tutorial.com\/?page_id=307"},"modified":"2020-04-11T06:59:38","modified_gmt":"2020-04-11T14:59:38","slug":"db2-right-join","status":"publish","type":"page","link":"https:\/\/www.db2tutorial.com\/db2-basics\/db2-right-join\/","title":{"rendered":"Db2 RIGHT JOIN"},"content":{"rendered":"\n

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

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

The RIGHT JOIN<\/code> clause is a reversed version of the LEFT JOIN<\/a><\/code> clause. The RIGHT JOIN<\/code> clause allows you to query data from two or more tables.<\/p>\n\n\n\n

Suppose, you have two tables named T1<\/code> and T2<\/code>, which are called the left table and the right table respectively.<\/p>\n\n\n\n

The RIGHT JOIN<\/code> clause selects data starting from the right table (T2<\/code>). It compares each row in the right table (T2<\/code>) with every row in the left table (T1<\/code>). If two rows satisfy the join condition, the RIGHT JOIN<\/code> clause combines columns of these rows into a new row and includes this new row in the result.<\/p>\n\n\n\n

In case a row in the right table does not have a matching row in the left table, the RIGHT JOIN<\/code> clause still combines the columns of the row in the right table with the columns of the row in the left table. However, the columns in the left table will have NULL values.<\/p>\n\n\n\n

In other words, the RIGHT JOIN<\/code> clause returns all rows from the right table (T2<\/code>) and matching rows or NULL<\/code> values from the left table (T1<\/code>).<\/p>\n\n\n\n

This Venn diagram illustrates how to use the RIGHT JOIN<\/code> clause to join T1<\/code> and T2<\/code> tables:<\/p>\n\n\n\n

\"db2<\/figure>\n\n\n\n

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

SELECT<\/span> \n    select_list\nFROM<\/span>\n    T1\nRIGHT<\/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 T2<\/code> with the values of the columns in the table T1<\/code> using the equality operator (=<\/code>).<\/p>\n\n\n\n
Note that in case T1<\/code> and T2<\/code> tables have the same column names, you have to fully qualify these column names in the query like T1.id<\/code> and T2.id<\/code>. If the table names are long, you can use the table aliases to save some typing.<\/p>\n\n\n\n
The following Venn diagram illustrates the RIGHT JOIN<\/code> of two tables T1<\/code> and T2<\/code>:<\/p>\n\n\n\n
The following statement illustrates how to join more than two tables using the RIGHT JOIN<\/code> clauses:<\/p>\n\n\n
SELECT<\/span> \n    select_list\nFROM<\/span>\n    T1\nRIGHT<\/span> JOIN<\/span> T2 ON<\/span> join_condition2\nRIGHT<\/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 RIGHT JOIN examples<\/h2>\n\n\n\n
Let’s take some examples of using the RIGHT JOIN<\/code> clause.<\/p>\n\n\n\n
1) Using DB2 RIGHT JOIN to join two tables example<\/h3>\n\n\n\n
The following database diagram displays the books<\/code> and publishers<\/code> tables:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
In this data model, a publisher may have zero or many books while a book may belong 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
If a book does not associate with a publisher, maybe the publisher was not known at the time of recording, the value in the publisher_id<\/code> column of the books<\/code> table is NULL.<\/p>\n\n\n\n
This query uses the RIGHT 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\nFROM<\/span> \n    books b\nRIGHT<\/span> JOIN<\/span> publishers p \n    ON<\/span> p.publisher_id = b.publisher_id\nORDER<\/span> BY<\/span> \n    b.title NULLS<\/span> FIRST<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
This picture shows the partial result set:<\/p>\n\n\n\n
\"db2<\/figure>\n\n\n\n
In this example, the RIGHT JOIN<\/code> clause compares the value in the publisher_id<\/code> column of each row in the publishers<\/code> table with the value of the publisher_id<\/code> column of each row in the books<\/code> table. If they are equal, the RIGHT JOIN<\/code> combines columns of these two rows into a row and includes this row in the result set.<\/p>\n\n\n\n
If a row from the publishers<\/code> table that does not have a matching row in the books<\/code> table, the title<\/code> column in the result set has a NULL.<\/p>\n\n\n\n
2) Using DB2 RIGHT JOIN to find the missing rows in another table<\/h3>\n\n\n\n
The following query uses the RIGHT JOIN<\/code> clause to find publishers that do not have books recorded:<\/p>\n\n\n
SELECT<\/span> \n    b.title, \n    p.name as<\/span> publisher\nFROM<\/span> \n    books b\nRIGHT<\/span> JOIN<\/span> publishers p \n    ON<\/span> p.publisher_id = b.publisher_id\nWHERE<\/span> \n    title IS<\/span> NULL<\/span>\nORDER<\/span> BY<\/span> \n    b.title NULLS<\/span> FIRST<\/span>;\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
\"\"<\/figure>\n\n\n\n
In this tutorial, you have learned how to use the Db2 RIGHT JOIN<\/code> clause to query data 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