Join is to query data from more than one table. Normally, there are five different joining schemes.
The scheme 2 to 4 are described in details in the following.
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;
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 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.
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;