We use table join to link two tables together.
Example:
CREATE TABLE departments
(department_id number(10) not null,
department_name varchar2(50) not null,
CONSTRAINT departments_pk PRIMARY KEY (department_id)
);
insert into departments ( department_id, department_name )values(1,'Data Group' );
insert into departments ( department_id, department_name )values(2,'Purchasing' );
insert into departments ( department_id, department_name )values(3,'Call Center' );
insert into departments ( department_id, department_name )values(4,'Communication' );
CREATE TABLE employees
( employee_id number(10) not null,
last_name varchar2(50) not null,
job_id varchar2(30),
department_id number(10),
salary number(6),
manager_id number(6),
CONSTRAINT employees_pk PRIMARY KEY (employee_id),
CONSTRAINT fk_departments FOREIGN KEY (department_id)
REFERENCES departments(department_id)
);
insert into employees( employee_id, last_name, job_id, salary,department_id ,manager_id)
values( 1001, 'Lawson', 'MGR', 30000, 1, 1004);
insert into employees( employee_id, last_name, job_id, salary, department_id ,manager_id)
values( 1002, 'Wells', 'DBA', 20000, 2, 1005 );
insert into employees( employee_id, last_name, job_id, salary, department_id ,manager_id)
values( 1003, 'Bliss', 'PROG', 24000, 3, 1004);
insert into employees( employee_id, last_name, job_id, salary, department_id, manager_id)
values( 1004, 'Kyte', 'MGR', 25000 ,4, 1005);
insert into employees( employee_id, last_name, job_id, salary, department_id, manager_id)
values( 1005, 'Viper', 'PROG', 20000, 1, 1006);
insert into employees( employee_id, last_name, job_id, salary, department_id,manager_id)
values( 1006, 'Beck', 'PROG', 20000, 2, null);
insert into employees( employee_id, last_name, job_id, salary, department_id, manager_id)
values( 1007, 'Java', 'PROG', 20000, 3, 1006);
insert into employees( employee_id, last_name, job_id, salary, department_id, manager_id)
values( 1008, 'Oracle', 'DBA', 20000, 4, 1006);
SQL> select employee_id, last_name, department_name
2 from employees, departments
3 where employees.department_id = departments.department_id
4 and employees.department_id = 1
5 ;
EMPLOYEE_ID LAST_NAME DEPARTMENT_NAME
----------- --------------------------------------------------
1001 Lawson Data Group
1005 Viper Data Group
SQL>
In the example above, the join is the first condition in the
WHERE clause:employees.department_id = departments.department_id
Typically, the columns used in the join are a primary key from one table and a foreign key from the other table.
Because the equality operator (=) is used in the join condition, the join is known as an equijoin.
The second condition in the WHERE clause (employees.department_id = 1) gets employees.department_id #1.