Inner join

An inner join shows data coming from two tables connected using a comparison operator in a condition.

The generic syntax for an inner join is:

SELECT columns
FROM table1
INNER JOIN table2
ON condition;

Let's add a new column to our contact table, a foreign key to a brand new table, defining the main job of our contact.

This is how we created the job table:

create table job (
  job_id number(*,0) not null,
  description varchar2(20 byte) not null,
  constraint job_pk primary key (job_id)
);

And here is the change we made in the contact table:

alter table contact add(job_id number(*,0));

alter table contact add constraint contact_job_fk foreign key(job_id)
    references job(job_id);

We add a few job titles in the job table, and assign a job to each contact. Now we can use an inner join to see name and job for contacts:

select c.last_name, j.description
from contact c
inner join job j
on c.job_id = j.job_id;

This is called equijoin, since is based on an equality check. Sometimes we could be interested in a non-equijoin, that means, getting all the "wrong" cases. In our case, all the possible job our contacts are not associated to:

select c.last_name, j.description
from contact c
inner join job j
on c.job_id != j.job_id;

You may have noticed I gave name job_id to the primary key for the job table, and I used the same name for it as foreign key in the contact table.

This gives us a way of simplify a bit the notation for the inner join, since it id considered just natural using the relation between primary and foreign key with the same name, so natural that we call it a natural join:

select c.last_name, j.description
from contact c
natural join job j;

A fun basic book on SQL: Head First SQL.