Querying Multiples Tables

Joins are used to combine columns from different tables. With joins, the information from any number of tables can be related. In a join, the tables are listed in the FROM clause, separeated by commas.The condition of the query can refer to any column of any table joined. The connection between tables is established through the WHERE clause. Based on the condition specified in WHERE clause, the required rows are retrived.

Following are the different types of joins
Equi Joins, Cartesian Joins, Outer Joins, Self Joins

Equi Joins

When two tables are joined together using equality of values in one or more columns, they make and equi join. Table prefixes are utilized to prevent ambiguity and the WHERE clause specifies the columns being joined.

Example
List the employee number, employee name, department number and department name.

See the information we want in this example. We can get Employee number, Employee name, Department information from employee table but department name exists in department table, so to get all the information in one Select we should join two tables and join with a common column between two tables(where clause), here deptno column is the common column between emp and dept tables.

Select empno, ename, emp.deptno, dname
From emp, dept
Where emp.deptno = dept.deptno

Cartesian Joins

If you are selecting information from more than on table and if you did not specify the where clause, each row of one table matches every row of the other table ie Cartesian Join.

If you have a table TAB1 which has 25 rows, TAB2 which has 10 rows then, if you join these two tables with out where cluase then you get 25 * 10 ( 250 ) rows as the result set.

Cartesian products is useful in finding out all the possible combination of columns from different tables.

Outer Joins

If there are any values in one table that do not have corresponding values in the other, in an equijoin that row will not be selected. Such rows can be forcefully selected by using the outer join symbol (+). The corresponding columns for that row will have NULLs.

Where you will use the Outer Join. For example we have employee and department tables. In department table deptno is the primary key, in employee table deptno exists and its a foreign key. By rule you cannot have a deptno in employee table if it does not exists in dept table, ie the primary and foreign key concept. So we can have a department record and there is no employee in the related department.

In the emp table, no record of the employees belonging to the department 40 is present. Therefore, in the example above for equi join, the row of department 40 from the dept table will not be displayed

Display the list of employees working in each department. Display department information even if no employee exists in that department.

Select empno, ename, dept.deptno, dname, loc
from emp, dept
where emp.deptno( + ) = dept.deptno

The outer join symbol (+) can not be used both the sides

Self Join

To join a table to itself means that each row of the tables is combined with itself and every other row of the table. The self join can be viewed as a join of two copies of same table. The table is not actually copied, but SQL performs the command as though it were.

Select a.ename, b.name
from emp a, emp b
where a.mgr = b.empno