Synopsis: in this instructional exercise, you will figure out how to utilize the Db2 INNER JOIN proviso to inquiry information from at least two related tables.
Introduction to Db2
INNER JOIN clause
The INNER JOIN is one of the join provisos that permit you to inquiry information from at least two related tables. The INNER JOIN proviso consolidates each line from the primary table with each column from the subsequent table, keeping just the lines wherein the join condition assesses to valid.
The accompanying shows the linguistic structure of joining two tables utilizing the INNER JOIN statement:
SELECT select_list FROM T1 INNER JOIN T2 ON join_condition;
In this punctuation, the join_condition is a Boolean articulation that assesses to valid, bogus, and obscure. Ordinarily, it coordinates the estimations of the sections in the table T1 with the estimations of the segments in the table T2 utilizing the correspondence administrator (=).
The accompanying Venn graph outlines the inward join of two tables:
Note that if T1 and T2 tables have a similar section names, you should completely qualify these segment names in the inquiry e.g., T1.id and T2.id or you will get a blunder. On the off chance that the table names are long, you can utilize the table nom de plumes to spare some composing.
To internal join multiple tables, you utilize various INNER JOIN provisos as appeared in the accompanying inquiry:
SELECT select_list FROM T1 INNER JOIN T2 ON join_condition2 INNER JOIN T3 on join_condition3 ...;
INNER JOIN examples
We should take a few instances of utilizing the INNER JOIN provision.
1) Using DB2 INNER JOIN to join two tables model
In this model, one distributer may have zero or numerous books while each book has a place with zero or one distributer. The connection between the books table and the distributers table is zero-to-many.
The publisher_id segment of the books table connects to the publisher_id segment of the distributers table to set up this relationship.
The accompanying model uses the INNER JOIN statement to get the books table together with the distributers table:
SELECT b.title, p.name, b.publisher_id, p.publisher_id FROM books b INNER JOIN publishers p ON p.publisher_id = b.publisher_id ORDER BY b.title;
In this model, the INNER JOIN proviso looks at the incentive in the publisher_id section of each line in the books table with the estimation of the publisher_id segment of each column in the distributers table. On the off chance that they are equivalent, The INNER JOIN consolidates sections of these two columns into a line and remembers this line for the outcome set.
2) Using DB2 INNER JOIN to join three tables model
In this model, one book is composed by one or numerous writers. Also, one writer may keep in touch with one or numerous books. The connection between the books table and the writers table is many-to-many.
To demonstrate this many-to-numerous relationship, we have a related table: book_authors. Note that this partner table is otherwise called an intersection table, a join table, or a cross-reference table.
So as to get the book titles from the books table and writer's names from the writers table, we join three tables utilizing the INNER JOIN provision as follows:
SELECT b.title, a.first_name, a.last_name FROM books b INNER JOIN book_authors ba ON ba.book_id = b.book_id INNER JOIN authors a ON a.author_id = ba.author_id ORDER BY b.title;
Here is the halfway outcome set:
In this instructional exercise, you have figured out how to utilize the Db2 INNER JOIN statement to inquiry information from at least two tables.