join the same table sql code example

Example 1: how to join tables in sql

JOINING 2 Tables in sql

SELECT X.Column_Name , Y.Column_Name2
FROM TABLES1_NAME X 
INNER JOIN TABLES2_NAME Y ON X.Primary_key = Y.Foreign_key;


--FOR EXAMPLE
--GET THE FIRST_NAME AND JOB_TITLE
--USE EMPLOYEES AND JOBS TABLE
--THE RELATIONSHIP IS JOB_ID

SELECT E.FIRST_NAME , J.JOB_TITLE
FROM EMPLOYEES E
INNER JOIN JOBS J ON J.JOB_ID = E.JOB_ID;

Example 2: join three tables sql

Suppose we are having three table named as 
Student_details
Attendance_details
Batch_details
And we have to apply join these three tables for fetching records

Example query:
select column_names
from Student_detail as s join Attendance_details as a on
s.s_id = a.s_id join Batch_details as b on 
s.s_id = b.s_id;

Here in the above example we implemented simple join but you change it with own join requirements.

Example 3: self join

SELECT column_name(s)
FROM table1 T1, table1 T2
WHERE condition;

Example 4: self join in sql

Self is joining a table to itself.

-- assume employee table as 2 different table using different alias 
-- as  manager and worker 
-- we want to join these 2 virtual manager and worker table 
-- to get manager's first name and worker's first name 
-- our condition is worker's manager_id match managers employee id

SELECT  manager.FIRST_NAME AS MANAGER_NAME , 
        worker.FIRST_NAME AS WORKER_NAME 
FROM EMPLOYEES manager 
INNER JOIN EMPLOYEES worker on worker.MANAGER_ID = manager.EMPLOYEE_ID  

order by 1 
;