SQL JOIN Understanding and Examples on HR schema .

SQL JOIN

Joining is combine between multiple table based on related column ( primary key or foreign key )

INNER JOIN 
Inner join is the keyword is if the keyword is match in both side then it return the values other ways not return the value.

SELECT employees.first_name, departments.department_name
FROM employees
INNER JOIN departments  ON employees.department_id= departments.department_id;

LEFT JOIN
Left join is return all records from left table and keyword matching records from right table.
SELECT employees.first_name, departments.department_name
FROM employees
LEFT JOIN departments  ON employees.department_id= departments.department_id ;

RIGHT JOIN
Right join is return all records from right table and keyword matching records from left table.
SELECT employees.first_name, departments.department_name
FROM employees
RIGHT JOIN departments  ON employees.department_id= departments.department_id ;

OUTER JOIN
Outer join is return all records from both table why there keyword matching or not.
select employees.first_name, departments.department_name
from employees
FULL OUTER JOIN departments  ON employees.department_id= departments.department_id ;

SELF JOIN
Self join is regular join but this time in same table.

SELECT A.first_name, B.first_name from employees A, employees B ;

UNION ALL
Union is for combine data which is return from different table.
Select first_name from employees
UNION ALL
select departments_name from departments ;