Join: Querying Data from Multiple Tables

Overview

Equal-Join

Non-equal-Join

Outer-Join

Self-Join

Overview

Join is to query data from more than one table.  Normally, there are five different joining schemes.

1. Cartesian product. If no joining condition is specified, every row in the first table with be matched to the every row of the second tables and so on for the second table and the third joining table. This should be avoided.
2. Equal-Join. Tables are joined based on some column equality condition.
3. Non-equal Join. Tables are joined based on some column inequality condition.
4. Outer-Join. Tables are joined and rows that do NOT meet the join condition are also returned.
5. Self-Join. Joining a table with itself.

The scheme 2 to 4 are described in details in the following.

Equal-Join

Use table prefix to qualify column names to remove column ambiguity.

For example,

SQL> SELECT empno, employee.name, employee.dept_no, dept.name

FROM employee, dept

WHERE employee.dept_no = dept.dept_no;

Non-equal-Join

Use Between-And clause in non-equal join queries.

For example, to answer the query "find the students whose mark falls in Grade A range" (assuming the student mark table only stores the raw marks (with values ranging from 0 from 100, and the grade_class table stores the low-end and high-end marks for each grade.), the query is:

SQL> SELECT  a.name, a.matric_no, a.mark

    FROM student_marks a, grade_class b

    WHERE b.grade = 'A' AND

    a.mark BETWEEN b.low_end AND b.high_end;

Outer-Join

Outer-join operator is the plus sign (+). Out join queries return the normal joining results plus the rows in two tables that do not meet the join condition.

For example, assume we have a module_taken table which keeps the relationship on what modules taken  by which student,  and a module table which keeps the module details.

The following statement returns the student matric no, and module names taken that student, and in addition to this, it also list  the modules which are not taken by anyone.

SQL> SELECT a.matric_no,  a.module_no, b.module_name

    FROM module_taken a, module b

    WHERE a.module_no (+) = b.module_no;

Assume module CS9999 is not taken by any student but is listed in the module table, then this module will also appear in the result, with empty value  for matric_no.

Self-Join

To join the table with itself, a table must be given an alias to distinguish each other.

For example, the following statement retrieves all employees who have a manager (who don't have a boss :)).

SQL> SELECT worker.name || ' works for ' || mgr.name 

    FROM employee worker, employee  mgr

    WHERE worker.mgr_no = mgr.empno;