SQL Joins

View All Scripts Login to Run Script

Script Name SQL Joins
Description SQL Join query examples, used to select rows from multiple tables using a join key.
Area SQL General
Contributor Mike Hichwa (Oracle)
Created Monday October 05, 2015

Statement 1

Using Self Joins: Example The following query uses a self join to return the name of each employee along with the name of the employee's manager. A WHERE clause is added to shorten the output.

SELECT e1.last_name||' works for '||e2.last_name  
   "Employees and Their Managers" 
FROM hr.employees e1, hr.employees e2  
WHERE e1.manager_id = e2.employee_id 
      AND e1.last_name LIKE 'R%' 
ORDER BY e1.last_name

 
Employees and Their Managers
Rajs works for Mourgos
Raphaely works for King
Rogers works for Kaufling
Russell works for King

4 rows selected.

Employees and Their Managers
Rajs works for Mourgos
Raphaely works for King
Rogers works for Kaufling
Russell works for King

Statement 2

LEFT OUTER JOIN example. The following example shows how a partitioned outer join fills data gaps in rows to facilitate analytic function specification and reliable report formatting. The example first creates a small data table to be used in the join.

SELECT d.department_id, e.last_name 
FROM hr.departments d LEFT OUTER JOIN hr.employees e 
ON d.department_id = e.department_id 
ORDER BY d.department_id, e.last_name

 
DEPARTMENT_ID LAST_NAME
10 Whalen
20 Fay
20 Hartstein
30 Baida
30 Colmenares
30 Himuro
30 Khoo
30 Raphaely
30 Tobias
40 Mavris
50 Atkinson
50 Bell
50 Bissot
50 Bull
50 Cabrio
50 Chung
50 Davies
50 Dellinger
50 Dilly
50 Everett
50 Feeney
50 Fleaur
50 Fripp
50 Gates
50 Gee
50 Geoni
50 Grant
50 Jones
50 Kaufling
50 Ladwig
50 Landry
50 Mallin
50 Markle
50 Marlow
50 Matos
50 McCain
50 Mikkilineni
50 Mourgos
50 Nayer
50 OConnell
50 Olson
50 Patel
50 Perkins
50 Philtanker
50 Rajs
50 Rogers
50 Sarchand
50 Seo
50 Stiles
50 Sullivan
50 Taylor

Rows 1 - 50

DEPARTMENT_ID	LAST_NAME
10	Whalen
20	Fay
20	Hartstein
30	Baida
30	Colmenares
30	Himuro
30	Khoo
30	Raphaely
30	Tobias
40	Mavris
50	Atkinson
50	Bell
50	Bissot
50	Bull
50	Cabrio
50	Chung
50	Davies
50	Dellinger
50	Dilly
50	Everett
50	Feeney
50	Fleaur
50	Fripp
50	Gates
50	Gee
50	Geoni
50	Grant
50	Jones
50	Kaufling
50	Ladwig
50	Landry
50	Mallin
50	Markle
50	Marlow
50	Matos
50	McCain
50	Mikkilineni
50	Mourgos
50	Nayer
50	OConnell
50	Olson
50	Patel
50	Perkins
50	Philtanker
50	Rajs
50	Rogers
50	Sarchand
50	Seo
50	Stiles
50	Sullivan
50	Taylor

Statement 3

RIGHT OUTER JOIN example

SELECT d.department_id, e.last_name 
FROM hr.departments d RIGHT OUTER JOIN hr.employees e 
   ON d.department_id = e.department_id 
   ORDER BY d.department_id, e.last_name

 
DEPARTMENT_ID LAST_NAME
10 Whalen
20 Fay
20 Hartstein
30 Baida
30 Colmenares
30 Himuro
30 Khoo
30 Raphaely
30 Tobias
40 Mavris
50 Atkinson
50 Bell
50 Bissot
50 Bull
50 Cabrio
50 Chung
50 Davies
50 Dellinger
50 Dilly
50 Everett
50 Feeney
50 Fleaur
50 Fripp
50 Gates
50 Gee
50 Geoni
50 Grant
50 Jones
50 Kaufling
50 Ladwig
50 Landry
50 Mallin
50 Markle
50 Marlow
50 Matos
50 McCain
50 Mikkilineni
50 Mourgos
50 Nayer
50 OConnell
50 Olson
50 Patel
50 Perkins
50 Philtanker
50 Rajs
50 Rogers
50 Sarchand
50 Seo
50 Stiles
50 Sullivan
50 Taylor

Rows 1 - 50

DEPARTMENT_ID	LAST_NAME
10	Whalen
20	Fay
20	Hartstein
30	Baida
30	Colmenares
30	Himuro
30	Khoo
30	Raphaely
30	Tobias
40	Mavris
50	Atkinson
50	Bell
50	Bissot
50	Bull
50	Cabrio
50	Chung
50	Davies
50	Dellinger
50	Dilly
50	Everett
50	Feeney
50	Fleaur
50	Fripp
50	Gates
50	Gee
50	Geoni
50	Grant
50	Jones
50	Kaufling
50	Ladwig
50	Landry
50	Mallin
50	Markle
50	Marlow
50	Matos
50	McCain
50	Mikkilineni
50	Mourgos
50	Nayer
50	OConnell
50	Olson
50	Patel
50	Perkins
50	Philtanker
50	Rajs
50	Rogers
50	Sarchand
50	Seo
50	Stiles
50	Sullivan
50	Taylor

Statement 4

It is not clear from this result whether employees Grant and Zeuss have department_id NULL, or whether their department_id is not in the departments table. To determine this requires a full outer join.

SELECT d.department_id as d_dept_id, e.department_id as e_dept_id, 
      e.last_name 
FROM hr.departments d FULL OUTER JOIN hr.employees e 
   ON d.department_id = e.department_id 
ORDER BY d.department_id, e.last_name

 
D_DEPT_ID E_DEPT_ID LAST_NAME
10 10 Whalen
20 20 Fay
20 20 Hartstein
30 30 Baida
30 30 Colmenares
30 30 Himuro
30 30 Khoo
30 30 Raphaely
30 30 Tobias
40 40 Mavris
50 50 Atkinson
50 50 Bell
50 50 Bissot
50 50 Bull
50 50 Cabrio
50 50 Chung
50 50 Davies
50 50 Dellinger
50 50 Dilly
50 50 Everett
50 50 Feeney
50 50 Fleaur
50 50 Fripp
50 50 Gates
50 50 Gee
50 50 Geoni
50 50 Grant
50 50 Jones
50 50 Kaufling
50 50 Ladwig
50 50 Landry
50 50 Mallin
50 50 Markle
50 50 Marlow
50 50 Matos
50 50 McCain
50 50 Mikkilineni
50 50 Mourgos
50 50 Nayer
50 50 OConnell
50 50 Olson
50 50 Patel
50 50 Perkins
50 50 Philtanker
50 50 Rajs
50 50 Rogers
50 50 Sarchand
50 50 Seo
50 50 Stiles
50 50 Sullivan
50 50 Taylor

Rows 1 - 50

D_DEPT_ID	E_DEPT_ID	LAST_NAME
10	10	Whalen
20	20	Fay
20	20	Hartstein
30	30	Baida
30	30	Colmenares
30	30	Himuro
30	30	Khoo
30	30	Raphaely
30	30	Tobias
40	40	Mavris
50	50	Atkinson
50	50	Bell
50	50	Bissot
50	50	Bull
50	50	Cabrio
50	50	Chung
50	50	Davies
50	50	Dellinger
50	50	Dilly
50	50	Everett
50	50	Feeney
50	50	Fleaur
50	50	Fripp
50	50	Gates
50	50	Gee
50	50	Geoni
50	50	Grant
50	50	Jones
50	50	Kaufling
50	50	Ladwig
50	50	Landry
50	50	Mallin
50	50	Markle
50	50	Marlow
50	50	Matos
50	50	McCain
50	50	Mikkilineni
50	50	Mourgos
50	50	Nayer
50	50	OConnell
50	50	Olson
50	50	Patel
50	50	Perkins
50	50	Philtanker
50	50	Rajs
50	50	Rogers
50	50	Sarchand
50	50	Seo
50	50	Stiles
50	50	Sullivan
50	50	Taylor

Statement 5

Because the column names in this example are the same in both tables in the join, you can also use the common column feature by specifying the USING clause of the join syntax. The output is the same as for the preceding example except that the USING clause coalesces the two matching columns department_id into a single column output.

SELECT department_id AS d_e_dept_id, e.last_name 
FROM hr.departments d FULL OUTER JOIN hr.employees e 
USING (department_id) 
ORDER BY department_id, e.last_name

 
D_E_DEPT_ID LAST_NAME
10 Whalen
20 Fay
20 Hartstein
30 Baida
30 Colmenares
30 Himuro
30 Khoo
30 Raphaely
30 Tobias
40 Mavris
50 Atkinson
50 Bell
50 Bissot
50 Bull
50 Cabrio
50 Chung
50 Davies
50 Dellinger
50 Dilly
50 Everett
50 Feeney
50 Fleaur
50 Fripp
50 Gates
50 Gee
50 Geoni
50 Grant
50 Jones
50 Kaufling
50 Ladwig
50 Landry
50 Mallin
50 Markle
50 Marlow
50 Matos
50 McCain
50 Mikkilineni
50 Mourgos
50 Nayer
50 OConnell
50 Olson
50 Patel
50 Perkins
50 Philtanker
50 Rajs
50 Rogers
50 Sarchand
50 Seo
50 Stiles
50 Sullivan
50 Taylor

Rows 1 - 50

D_E_DEPT_ID	LAST_NAME
10	Whalen
20	Fay
20	Hartstein
30	Baida
30	Colmenares
30	Himuro
30	Khoo
30	Raphaely
30	Tobias
40	Mavris
50	Atkinson
50	Bell
50	Bissot
50	Bull
50	Cabrio
50	Chung
50	Davies
50	Dellinger
50	Dilly
50	Everett
50	Feeney
50	Fleaur
50	Fripp
50	Gates
50	Gee
50	Geoni
50	Grant
50	Jones
50	Kaufling
50	Ladwig
50	Landry
50	Mallin
50	Markle
50	Marlow
50	Matos
50	McCain
50	Mikkilineni
50	Mourgos
50	Nayer
50	OConnell
50	Olson
50	Patel
50	Perkins
50	Philtanker
50	Rajs
50	Rogers
50	Sarchand
50	Seo
50	Stiles
50	Sullivan
50	Taylor

Statement 6

Using Antijoins: Example The following example selects a list of employees who are not in a particular set of departments.

SELECT * FROM hr.employees 
   WHERE department_id NOT IN 
   (SELECT department_id FROM hr.departments 
       WHERE location_id = 1700)
   ORDER BY last_name

Statement 7
Using Semijoins: Example In the following example, only one row needs to be returned from the departments table, even though many rows in the employees table might match the subquery. If no index has been defined on the salary column in employees, then a semijoin can be used to improve query performance.
```
SELECT * FROM hr.departments 
   WHERE EXISTS 
   (SELECT * FROM hr.employees 
       WHERE departments.department_id = employees.department_id 
       AND employees.salary > 2500)
   ORDER BY department_name
```

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database