Search This Blog

Sunday, May 7, 2017

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 ;

No comments:

Post a Comment

Split A String By Coma (,)

Step 1.  create or replace type split_tbl as table of varchar2(32767); Step 2. create or replace function split (     p_list varchar2,...