When you want to see information from multiple tables, you can use a SELECT statement. SELECT statements can retrieve and join column values from two or more tables into a single row. The retrieval is based on a specified condition, typically of matching column values. The main ingredient of a join is, typically, matching column values in rows of each table that participates in the join. The result of a join associates rows from one table with rows from another table. Depending on the type of join operation, some rows might be formed that contain column values in one table that do not match column values in another table.
JOIN is used to get the combine two or more tables to get the complete information about the entities. JOIN can combines the table which had common columns in tables. If no matching column(s) is there, then SELECT statement is fine enough to get the information from more than one table, The results of JOIN have the associated rows from one table with another table(s). JOIN is intermediate table result from more than one table based on matching columns(s)/ attribute(s).
JOIN will get more Columns (Complete information) about the particular entity (Column). If the specified value matches in both tables, the rows will be matched and combined. DB2 supports the JOIN. There are two types of JOINs based on how the tables joined.
INNER JOIN
OUTER JOIN
An inner join finds and returns matching data from tables, while an outer join finds and returns matching data and some dissimilar data from tables.An inner join finds and returns matching data from tables, while an outer join finds and returns matching data and some dissimilar data from tables. For combining two sets of columns with/without removing duplicates refer UNION vs UNION ALL use in SQL Queries. For returning the first non-null value in a list of input expressions use COALESCE function in Join Queries.
DB2 Join : Inner join
Combines each row of the left table with each row of the right table, keeping only the rows in which the join condition is true.
DB2 Join : Outer join
Includes the rows that are produced by the inner join, plus the missing rows, depending on the type of outer join.
DB2 Join : Left outer join
Includes the rows from the left table that were missing from the inner join.
DB2 Join : Right outer join
Includes the rows from the right table that were missing from the inner join.
DB2 Join : Full outer join
Includes the rows from both tables that were missing from the inner join.
Sometimes the information you want to see is not in a single table. To form a row of the result table, you might want to retrieve some column values from one table and some column values from another table. You can use a SELECT statement to retrieve and join column values from two or more tables into a single row.
DB2 supports these types of joins: inner join, left outer join, right outer join, and full outer join. You can specify joins in the FROM clause of a query: Figure 8 below shows the ways to combine tables using outer join functions.
The result table contains data joined from all of the tables, for rows that satisfy the search conditions. The result columns of a join have names if the outermost SELECT list refers to base columns. But, if you use a function (such as COALESCE or VALUE) to build a column of the result, then that column does not have a name unless you use the AS clause in the SELECT list. To distinguish the different types of joins, the examples in this section use the following two tables -
The PARTS table The PRODUCTS table PART PROD# SUPPLIER PROD# PRODUCT PRICE ======= ===== ============ ===== =========== ===== WIRE 10 ACWF 505 SCREWDRIVER 3.70 OIL 160 WESTERN_CHEM 30 RELAY 7.55 MAGNETS 10 BATEMAN 205 SAW 18.90 PLASTIC 30 PLASTIK_CORP 10 GENERATOR 45.75 BLADES 205 ACE_STEEL
Inner Join
You can request an inner join in two ways. You can join the example tables on the PROD# column to get a table of parts with their suppliers and the products that use the parts.
Either one of these examples: SELECT PART, SUPPLIER, PARTS.PROD#, PRODUCT FROM PARTS, PRODUCTS WHERE PARTS.PROD# = PRODUCTS.PROD#; or SELECT PART, SUPPLIER, PARTS.PROD#, PRODUCT FROM PARTS INNER JOIN PRODUCTS ON PARTS.PROD# = PRODUCTS.PROD#;
Result
PART SUPPLIER PROD# PRODUCT ======= ============ ===== ========== WIRE ACWF 10 GENERATOR MAGNETS BATEMAN 10 GENERATOR PLASTIC PLASTIK_CORP 30 RELAY BLADES ACE_STEEL 205 SAW