KR editions 78 to 79

KR-78 (DB2 Joins)

A little info on databases (DB2).

There are different types of joins (a join is basically a combination of 2/more tables - it's used when we cannot get the data we want from a single table alone). 
Did you know that:

SELECT * FROM emp_t A, sal_t B 
WHERE A.grade = B.grade;

is actually equivalent to:

SELECT * FROM emp_t A JOIN sal_t B 
ON A.grade = B.grade;

• Be careful with the 2 syntaxes. The second one is generally not used (you'll have to use the keyword JOIN and ON instead of WHERE). 
• INNER JOIN: A join produced when we join two tables and only select the rows satisfying the condition from both tables (rows not satisfying the WHERE condition are left out). 
• In our example the table emp_t has employee details and a column denoting the grade. 
• The sal_t table has corresponding salary details for each grade. 
• Thus when we join the 2 tables based on grades present in both tables, only the rows satisfying the condition A.grade = B.grade will be shown in our result (if an employee has a grade not present in the sal_t table then that employee record will not appear in the output).

KR-79 (DB2 Joins - II)

Continuing with joins.....

• Frequently we encounter only INNER JOINS (i.e. we choose rows from both tables satisfying the specified criteria). 
• The default (when we don't specify JOIN in an SQL) is inner join. 
• LEFT OUTER JOIN: In this case the rows listed for an inner join will be selected along with unmatched rows from the LEFT hand side table (i.e. the table we specify on the left side of the join keyword). 
• For Ex: 

SELECT * FROM 
dep_t LEFT OUTER JOIN emp_t 
ON head_num = emp_num;

• In this SQL: head_num is the employee number of the Head of Dept (in dep_t table) and emp_t table contains the list of employees. 
• If this were an inner join, the SQL would only have listed out departments having a head (it would have given the entire dept detail and that particular employee detail). 
• But by specifying an outer join we obtain a list of all departments (since this is the table on the left side of the join; for example the field head_num might not be populated in some departments). 
• Similarly we have a RIGHT OUTER JOIN and a FULL OUTER JOIN (FULL OUTER JOIN = LEFT + RIGHT OUTER JOIN). 

Go back to the main contents page