---

Outline: in this instructional exercise, you will figure out how to inquiry information from two tables utilizing Db2 joins. 

At the point when you need to see the information from various tables, you can utilize the SELECT articulation with joins. The join relates the lines from one table with lines from another table dependent on a predetermined condition, normally of coordinating section esteems. 

Db2 bolsters different sorts of joins including inward join, left external join, right external join, and full external join. 

Allows arrangement some example tables for exhibit. 

Setting sample tables

Second, make two new tables named contacts and clients: 

CREATE TABLE contacts (
    contact_id INT NOT NULL PRIMARY KEY, 
    name       VARCHAR(100) NOT NULL
);

CREATE TABLE customers (
    customer_id INT NOT NULL PRIMARY KEY, 
    name        VARCHAR(100) NOT NULL
);

Second, embed a few columns into the contacts and clients tables: 

INSERT INTO contacts(contact_id, name)
VALUES(1,'Amelia'),
    (2,'Olivia'),
    (3,'Isla'),
    (4,'Emily');


INSERT INTO customers(customer_id, name)
VALUES(1,'Amelia'),
    (2,'Isla'),
    (3,'Jessica'),
    (4,'Lily');

Third, question information from the contacts and clients tables: 

SELECT 
    *
FROM 
    contacts;

SELECT 
    *
FROM 
    customers;

We should call the contacts table the left table and the clients table the correct table. 

Db2 Inner Join 

The internal join consolidates each line from the left table with columns of the correct table, it keeps just the lines where the join condition is valid. 

This model uses the INNER JOIN to get the lines from the contacts table that have the relating lines with similar qualities in the name section of the clients table: 

SELECT 
    co.contact_id, 
    co.name contact_name, 
    cu.customer_id, 
    cu.name customer_name
FROM 
    contacts co
    INNER JOIN customers cu 
        ON cu.name = co.name;

Here is the outcome set: 

In this model, the join condition is cu.name = co.name which coordinates the qualities in the name segment of the contacts table with the qualities in the name segment of the clients table. 

The accompanying Venn chart delineates the consequence of the internal join of two outcome sets: 

Db2 Left Join 

The left join chooses information beginning from the left table and matches pushes in the correct table. Like the internal join, the left join restores all lines from the left table and the coordinating columns from the correct table. What's more, if a line in the left table doesn't have a coordinating line in the correct table, the sections of the correct table will have nulls. 

Note that the left join is additionally called the left external join. The external catchphrase is discretionary. 

The accompanying explanation gets the contacts table together with the clients table utilizing left join: 

SELECT 
    co.contact_id, 
    co.name contact_name, 
    cu.customer_id, 
    cu.name customer_name
FROM 
    contacts co
    LEFT JOIN customers cu 
        ON cu.name = co.name;

 

Here is the outcome set: 

This Venn outline shows the left join of two outcome sets: 

To get the columns that accessible just in the left table yet not in the correct table, you add a WHERE statement to the above inquiry: 

SELECT 
    co.contact_id, 
    co.name contact_name, 
    cu.customer_id, 
    cu.name customer_name
FROM 
    contacts co
    LEFT JOIN customers cu 
        ON cu.name = co.name
WHERE
    cu.name IS NULL;

Also, the this Venn outline represents the left join that chooses lines accessible just in the left table: 

Db2 Right Join

The correct join or right external join, which is a turned around form of the left join, chooses information beginning from the correct table and matches with the lines in the left table. 

The correct join restores an outcome set that incorporates all the columns from the correct table and the coordinating lines in the left table. On the off chance that a line in the correct table doesn't have a coordinating line in the left table, all segments in the left table will contain nulls. 

The accompanying model uses the correct join to inquiry columns from contacts and clients tables: 

SELECT 
    co.contact_id, 
    co.name contact_name, 
    cu.customer_id, 
    cu.name customer_name
FROM 
    contacts co
    RIGHT JOIN customers cu 
        ON cu.name = co.name;

Notice that all the lines from the correct table (clients) are remembered for the outcome set. 

Here is the Venn outline of the correct join: 

So as to get columns that are accessible just in the correct table, you add a WHERE condition to the above question: 

SELECT 
    co.contact_id, 
    co.name contact_name, 
    cu.customer_id, 
    cu.name customer_name
FROM 
    contacts co
    RIGHT JOIN customers cu 
        ON cu.name = co.name
WHERE
    co.name IS NULL;

Also, the accompanying Venn chart delineates the activity: 

db2 get right consolidate with a where statement 

Db2 full join 

The full join restores an outcome set that incorporates all the lines from both left and right tables, with the coordinating lines from the two sides where accessible. On the off chance that there is no match, the missing side will have nulls. 

Note that full join and full external join are equivalents. The external catchphrase is discretionary. 

This model plays out a full join between the contacts and clients tables: 

SELECT 
    co.contact_id, 
    co.name contact_name, 
    cu.customer_id, 
    cu.name customer_name
FROM 
    contacts co
    FULL JOIN customers cu 
        ON cu.name = co.name;

The Venn graph that outlines the full external join: 

To choose columns that are accessible in either left or right table, you bar pushes that are normal to the two tables by adding a WHERE proviso to the above question: 

SELECT 
    co.contact_id, 
    co.name contact_name, 
    cu.customer_id, 
    cu.name customer_name
FROM 
    contacts co
    FULL OUTER JOIN customers cu 
        ON cu.name = co.name
WHERE
    co.name IS NULL OR
    cu.name IS NULL;

In this instructional exercise, you have learned Db2 joins including internal join, left external join, right external join, and full external join to consolidate columns from two tables.