JOIN Statement


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.

  1. INNER JOIN
  2. OUTER JOIN

Syntax -


SELECT Table1-column-names,
	       Table2-column-names
	  FROM Table1-name JOIN Table2-name
	 WHERE search-condition(s)
	    ON joining-column(s)  

INNER JOIN -


INNER JOIN will join the columns which satisfies the condition from the tables provided in JOIN. In other words, INNER JOIN combines the row from left table to right table for the matching column(s) rows.

Let us say, A and B table are joining. A has the matching row for a specific value in matching columns and B also should have the row with the matching value. Then only the row will appear in result table. INNER JOIN explained in detail here .

OUTER JOIN -


OUTER JOIN will join the all columns from left table and right table based on the condition. If the matching rows missed in other table, the columns will be filled with NULLs of missed table in result table.

Let us say, A and B table are joining. A has the matching row for a specific value in matching columns and B also should have the row with the matching value. Then only the row will appear in result table. OUTER JOIN explained in detail here .