Oracle EQUIJOINS

What is an Equijoin in Oracle?

An equijoin is such a join which performs against a join condition containing an equality operator. It combines rows of one table associated with one or more rows in another table based on the equality of column values or expressions.

Syntax:

SELECT column_list 
FROM table1, table2
WHERE table1.column_name =
table2.column_name; 

Pictorial presentation

Example-1:

Sample table: employees

Sample table: departments

The following examples return the first name and job of each employee and the number and name of the department in which the employee works.

SELECT first_name, job_id, departments.department_id, department_name
FROM employees, departments
WHERE employees.department_id = departments.department_id
ORDER BY last_name, job_id; 

Sample Output:

FIRST_NAME           JOB_ID     DEPARTMENT_ID DEPARTMENT_NAME
-------------------- ---------- ------------- --------------------
Ellen                SA_REP                80 Sales
Sundar               SA_REP                80 Sales
Mozhe                ST_CLERK              50 Shipping
David                IT_PROG               60 IT
Hermann              PR_REP                70 Public Relations
Shelli               PU_CLERK              30 Purchasing
Amit                 SA_REP                80 Sales
Elizabeth            SA_REP                80 Sales
Sarah                SH_CLERK              50 Shipping
David                SA_REP                80 Sales
Laura                ST_CLERK              50 Shipping
Harrison             SA_REP                80 Sales
Alexis               SH_CLERK              50 Shipping
Anthony              SH_CLERK              50 Shipping
Gerald               SA_MAN                80 Sales
Nanette              SA_REP                80 Sales
.....................................

Example-2: Equijoins using AND , IN

The following SQL query returns the department no, department name and city of each location in which the department no containing the value 40 or 50.

Sample table: locations

Sample table: departments

SELECT a.department_id, a.department_name, b.city
FROM departments a, locations b
WHERE a.location_id = b.location_id
AND a.department_id IN (40, 50); 

Sample Output:

DEPARTMENT_ID DEPARTMENT_NAME                CITY
------------- ------------------------------ ---------------------
           40 Human Resources                London
           50 Shipping                       South San Francisco

Example-3: Equijoins using more than two tables

The following SQL query returns the first name, department name, and city of each employee.

Sample table: employees

Sample table: locations

Sample table: departments

SELECT a.first_name, b.department_name, c.city
FROM employees a, departments b, locations c
WHERE a.department_id = b.department_id
AND b.location_id = c.location_id; 

Sample Output:

FIRST_NAME           DEPARTMENT_NAME                CITY
-------------------- ------------------------------ ----------------------
Ellen                Sales                          Oxford
Sundar               Sales                          Oxford
Mozhe                Shipping                       South San Francisco
David                IT                             Southlake
Hermann              Public Relations               Munich
Shelli               Purchasing                     Seattle
Amit                 Sales                          Oxford
Elizabeth            Sales                          Oxford
Sarah                Shipping                       South San Francisco
David                Sales                          Oxford
Laura                Shipping                       South San Francisco
Harrison             Sales                          Oxford
Alexis               Shipping                       South San Francisco
Anthony              Shipping                       South San Francisco
..........

Previous: Oracle JOINS
Next: NON-EQUIJOINS