Inner Join and Outer Join ( Interbase Tutorial # 12 )

In Interbase you can join two or more tables. As a result of joining tables, Interbase will build a new table, which sometimes called ‘dynamic table’ or ‘virtual table’. Two kind of joins supported by Interbase : Inner Join and Outer Join.
Before we start to learn about Inner Join and Outer Join, execute these statement below :

    insert into employee (id, name, age, phonenumber) 
    values ('5',’Frank Borland','31','555-9999')

    insert into employee (id, name, age, phonenumber) 
    values ('6',’Max Planck’,'28','555-1199')

INNER JOIN

Inner join link rows in tables based on specified join conditions and return only those rows that match the join conditions. If a joined column contains a NULL value for a given row, that row is not included in the results table. Inner joins are the more common type because they restrict the data returned and show a clear relationship between two or more tables.

Example : You want to build a virtual table which contains two columns : id and total salary. The SQL statement is :

    select a.name, b.salary from employee a inner join salary b
    on a.id = b.employee_id

note :
a is correlation name or alias of ‘employee’ table
b is correlation name or alias of ‘salary’ table
you can make any alias name, ie : a, b, table1, table2, alias1, alias2....... etc

the output is :

NAME              SALARY 
======================== 
Isaac Newton        1000 
Blaise Pascal       2000 
Archimedes          3000 
George Boole        4000 

OUTER JOIN

Outer joins link rows in tables based on specified join conditions but return rows whether they match the join conditions or not. Outer joins are useful for viewing joined rows in the context of rows that do not meet the join conditions. 

Example : You also want to build a virtual table which contains two columns : id and total salary. But this time you use OUTER JOIN. The SQL statement is :

    select a.name, b.salary from employee a left outer join salary b
    on a.id = b.employee_id

the output is :

NAME                 SALARY 
============================ 
Isaac Newton           1000 
Blaise Pascal          2000 
Archimedes             3000 
George Boole           4000 
Frank Borland        <>
Max Planck           <> 

Form the output of both examples above, you can see the difference between INNER JOIN and OUTER JOIN. OUTER JOIN returns NULL value rows, but INNER JOIN doesn’t.

Go to Previous Tutorial or Next Tutorial