DB2 Questions   «Prev  Next»

DB2 Outer Joins, Subselect, and Group By Statements

  1. Explain an outer join.

    Answer: An outer join includes rows from tables when there are no matching values in the tables.
  2. What is a subselect? Is it different from a nested select?

    Answer: A subselect is a select which works in conjunction with another select. A nested select is a kind of subselect where the inner select passes to the where criteria for the outer select.
  3. What is the difference between group by and order by?

    Answer: Group by controls the presentation of the rows, order by controls the presentation of the columns for the results of the SELECT statement.
  4. What is the EXPLAIN statement in DB2?

    Answer: The explain statement provides information about the optimizer's choice of access path of the SQL.
  5. What is tablespace?

    Answer: Tables are stored in tablespaces (hence the name). There are three types of tablespaces:
    1. simple,
    2. segmented and
    3. partitioned.
  6. What is a cursor and what is its function?

    Answer: An embedded sql statement may return a number of rows while the programming language can only access one row at a time. The programming device called a cursor controls the position of the row.
  7. What is Referential Integrity?

    Answer: Referential integrity refers to the consistency that must be maintained between primary and foreign keys. For example, every foreign key value must have a corresponding primary key value.
  8. Usually, which is more important for DB2 system performance: 1) CPU processing or 2) I/O access?

    Answer: I/O operations are usually most critical for DB2 performance (or any other database for that matter).
  9. Is there any advantage to denormalizing DB2 tables?

    Answer: Denormalizing DB2 tables reduces the need for processing intensive relational joins and reduces the number of foreign keys.
  10. What is the database descriptor?

    Answer: The database descriptor, DBD is the DB2 component that limits access to the database whenever objects are created, altered or dropped.

Using a Nested Table Expression as the right operand of a join

Example of using a nested table expression as the right operand of a join:
The following query contains a fullselect (in bold) as the right operand of a left outer join with the PROJECTS table. The correlation name is TEMP. In this case the unmatched rows from the PROJECTS table are included, but the unmatched rows from the nested table expression are not. 

SELECT PROJECT, COALESCE(PROJECTS.PROD#, PRODNUM) AS PRODNUM,
PRODUCT, PART, UNITS
FROM PROJECTS LEFT JOIN
  (SELECT PART,
    COALESCE(PARTS.PROD#, PRODUCTS.PROD#) AS PRODNUM,
    PRODUCTS.PRODUCT
  FROM PARTS FULL OUTER JOIN PRODUCTS
    ON PARTS.PROD# = PRODUCTS.PROD#) AS TEMP 
    ON PROJECTS.PROD# = PRODNUM;

Nested Table Expression as the left operand of a Join

Example of using a nested table expression as the left operand of a join:
The following query contains a fullselect as the left operand of a left outer join with the PRODUCTS table. The correlation name is PARTX. In this case the unmatched rows from the nested table expression are included, but the unmatched rows from the PRODUCTS table are not. 
SELECT PART, SUPPLIER, PRODNUM, PRODUCT
  FROM (SELECT PART, PROD# AS PRODNUM, SUPPLIER
    FROM PARTS
    WHERE PROD# < '200') AS PARTX
  LEFT OUTER JOIN PRODUCTS
    ON PRODNUM = PROD#;
The result table looks similar to the following output: 
PART     SUPPLIER     PRODNUM   PRODUCT
======= ============  =======  ==========
WIRE    ACWF          10       GENERATOR
MAGNETS BATEMAN       10       GENERATOR
OIL     WESTERN_CHEM  160      ----------

Left Join

A left join returns all the rows from the left table (table1) and the matching rows from the right table (table2). If there are no matching rows in the right table, the result set will contain NULL values for the right table columns. Let's look at an example to understand it better:
Suppose we have the same employees and departments tables as before. To get the name of all employees and their respective departments, including those who don't belong to any department, we can use a left join as follows: 

SELECT employees.name, departments.name
FROM employees
LEFT JOIN departments
ON employees.department = departments.name;

This query returns the following result: 
+--------+-----------+
| name   | name      |
+--------+-----------+
| John   | HR        |
| Alice  | IT        |
| Bob    | HR        |
| Sarah  | Sales     |
+--------+-----------+