JOIN multiple tables , Foreign Key in SQL

 In this lesson ,

We need to get data by join multiple tables 

Using Oracle Free SQL  , by select HR schema to connect to all HR tables 

in past lesson we get data using a query like 

SELECT EMPLOYEE_ID , FIRST_NAME ||' '|| LAST_NAME   FULL_NAME , DEPARTMENT_ID

FROM HR.EMPLOYEES

where DEPARTMENT_ID = 30

which list data of employee and his/her department_id 

Let's we need to get department name instead of department_id , to get data with direct meaning

we can write query like this 👇

SELECT EMPLOYEE_ID , FIRST_NAME ||' '|| LAST_NAME   FULL_NAME ,

DEPARTMENTS.DEPARTMENT_NAME AS "Department Title"

FROM HR.EMPLOYEES , HR.DEPARTMENTS

where HR.EMPLOYEES.DEPARTMENT_ID =  HR.DEPARTMENTS.DEPARTMENT_ID

ORDER BY 2 

Meaning of the condition  

HR.EMPLOYEES.DEPARTMENT_ID = HR.DEPARTMENTS.DEPARTMENT_ID leads us to explain the foreign key between 2 tables ,  in EMPLOYEES table we see the column DEPARTMENT_ID , this column refer to the DEPARTMENT_ID in table DEPARTMENTS

and this relation is called Foreign Key which means primary key in one table can be called in another table

Important Role :

( any Foreign Key must be Primary Key in its table , as any ambassador is important in his country. )

this join condition is used to get only data matches the condition , and can be written as 👇 too

SELECT EMPLOYEE_ID , FIRST_NAME ||' '|| LAST_NAME   FULL_NAME ,

d.DEPARTMENT_NAME AS "Department Title"

FROM HR.EMPLOYEES e

INNER JOIN  HR.DEPARTMENTS d ON E.DEPARTMENT_ID =  D.DEPARTMENT_ID

ORDER BY 2

• to get all data from 2 tables even match condition or not , we can write this 👇 query

SELECT EMPLOYEE_ID , FIRST_NAME ||' '|| LAST_NAME   FULL_NAME ,

d.DEPARTMENT_NAME AS "Department Title"

FROM HR.EMPLOYEES e

FULL OUTER JOIN  HR.DEPARTMENTS d ON E.DEPARTMENT_ID =  D.DEPARTMENT_ID

ORDER BY 3 DESC

• to get all employees, even those who haven't been assigned to a department yet (like a new hire), we can write this 👇 query

SELECT EMPLOYEE_ID , FIRST_NAME ||' '|| LAST_NAME   FULL_NAME ,

d.DEPARTMENT_NAME AS "Department Title"

FROM HR.EMPLOYEES e

LEFT OUTER JOIN  HR.DEPARTMENTS d ON E.DEPARTMENT_ID =  D.DEPARTMENT_ID

ORDER BY 3 DESC

Hint 1 :

you must know the Old Oracle Syntax (+) , as you may say it in any SQL code

E.DEPARTMENT_ID =  D.DEPARTMENT_ID(+) was used instead of LEFT OUTER JOIN  HR.DEPARTMENTS d ON E.DEPARTMENT_ID =  D.DEPARTMENT_ID







• to get all departments, including those that currently have no employees assigned to them , we can write this 👇 query

SELECT EMPLOYEE_ID , FIRST_NAME ||' '|| LAST_NAME   FULL_NAME ,

d.DEPARTMENT_NAME AS "Department Title"

FROM HR.EMPLOYEES e

RIGHT OUTER JOIN  HR.DEPARTMENTS d ON E.DEPARTMENT_ID =  D.DEPARTMENT_ID

ORDER BY 3 DESC

Hint 2:

you must know the Old Oracle Syntax (+) , as you may say it in any SQL code

E.DEPARTMENT_ID(+)=  D.DEPARTMENT_ID was used instead of RIGHT OUTER JOIN  HR.DEPARTMENTS d ON E.DEPARTMENT_ID =  D.DEPARTMENT_ID

finally ,

as we list JOIN conditions , we must talk about the SELF-JOIN which is related to the Organizational Tree ,

what the mean of this is foreign key is in same table itself , like manger in table EMPLOYEES , is basically an employee

let's we need to get all employees and their manager's name , we can write query like this 👇

SELECT e.EMPLOYEE_ID , e.FIRST_NAME ||' '|| e.LAST_NAME   FULL_NAME ,

m.FIRST_NAME ||' '|| m.LAST_NAME  AS "Manager Name"

FROM HR.EMPLOYEES e

LEFT JOIN  HR.EMPLOYEES  m ON e.MANAGER_ID =  m.EMPLOYEE_ID

ORDER BY 1

for more information about JOIN , you can see this page

for more information about Foreign Key , you can see this page

in this lesson , we take a look at JOIN , Foreign Key in oracle SQL , and in next lesson we will use aggregating function to get more valuable information.