SQL Tutorial – Day – 06

Joins in Oracle:
================

Equi join – An equi join is a type of join that combines tables
based on matching values in specified columns. =

Non-Equi join – The nonequijoins is such a join which match column
values from different tables based on an inequality
(instead of the equal sign like >, <, >=, <= ) expression

4 Types of Equi join:
=====================

Inner Join
Left Outer join
Right Outer Join
Full Outer Join

2 methods to write join query:
==============================

1. Implicit method
2. ANSI Method

Table scripts for practice:
***************************

create table customer
(
cust_id number,
cust_name varchar2(50),
mob_no number(10),
email varchar2(50),
country_id number(3)
);

Insert into customer (CUST_ID,CUST_NAME,MOB_NO,EMAIL,COUNTRY_ID) values (1000,’Kannan’,8989898989,’kannan@gmail.com’,200);
Insert into customer (CUST_ID,CUST_NAME,MOB_NO,EMAIL,COUNTRY_ID) values (1001,’Arun’,8989898990,’arun@gmail.com’,204);
Insert into customer (CUST_ID,CUST_NAME,MOB_NO,EMAIL,COUNTRY_ID) values (1002,’Karthik’,8989898991,’Karthik@gmail.com’,202);
Insert into customer (CUST_ID,CUST_NAME,MOB_NO,EMAIL,COUNTRY_ID) values (1003,’Shankar’,8989898992,’shankar@gmail.com’,203);
Insert into customer (CUST_ID,CUST_NAME,MOB_NO,EMAIL,COUNTRY_ID) values (1004,’Sree’,8989898993,’Sree@gmail.com’,205);
Insert into customer (CUST_ID,CUST_NAME,MOB_NO,EMAIL,COUNTRY_ID) values (1005,’Babu’,8989898994,’Babu@gmail.com’,200);
Insert into customer (CUST_ID,CUST_NAME,MOB_NO,EMAIL,COUNTRY_ID) values (1006,’Radha’,8989898995,’Radha@gmail.com’,202);
Insert into customer (CUST_ID,CUST_NAME,MOB_NO,EMAIL,COUNTRY_ID) values (1007,’Senthil’,8989898996,’Senthil@gmail.com’,200);

commit;

create table country
(
Country_id number(3),
Country_name varchar2(50)
);

Insert into country (COUNTRY_ID,COUNTRY_NAME) values (200,’INDIA’);
Insert into country (COUNTRY_ID,COUNTRY_NAME) values (201,’CHINA’);
Insert into country (COUNTRY_ID,COUNTRY_NAME) values (202,’USA’);
Insert into country (COUNTRY_ID,COUNTRY_NAME) values (203,’SINGAPORE’);
Insert into country (COUNTRY_ID,COUNTRY_NAME) values (204,’UK’);

commit;

============================================

Inner Join:
————

— implicit method

select
c.cust_id,
c.cust_name,
c.mob_no,
c.email,
c.country_id,
r.country_name
from customer c, country r
where c.country_id=r.country_id;

Inner Join:
————
— ANSI Method

select
c.cust_id,
c.cust_name,
c.mob_no,
c.email,
c.country_id,
r.country_name
from customer c inner join country r
on c.country_id=r.country_id;

*******************************************************

Left Outer Join:
————
— implicit

select
c.cust_id,
c.cust_name,
c.mob_no,
c.email,
c.country_id,
r.country_name
from customer c, country r
where c.country_id=r.country_id(+);

Left Outer Join:
————
— ANSI

select
c.cust_id,
c.cust_name,
c.mob_no,
c.email,
c.country_id,
r.country_name
from customer c Left Outer join country r
on c.country_id=r.country_id;

***************************************************************

Right Outer Join:
————
— implicit

select
c.cust_id,
c.cust_name,
c.mob_no,
c.email,
c.country_id,
r.country_name
from customer c, country r
where c.country_id(+)=r.country_id;

Right Outer Join:
————
— ANSI

select
c.cust_id,
c.cust_name,
c.mob_no,
c.email,
c.country_id,
r.country_name
from customer c Right Outer join country r
on c.country_id=r.country_id;

*****************************************************

Full Outer Join:

— implicit method

select
c.cust_id,
c.cust_name,
c.mob_no,
c.email,
c.country_id,
r.country_name
from customer c, country r
where c.country_id=r.country_id(+)
union
select
c.cust_id,
c.cust_name,
c.mob_no,
c.email,
c.country_id,
r.country_name
from customer c, country r
where c.country_id(+)=r.country_id;

————
— ANSI

select
c.cust_id,
c.cust_name,
c.mob_no,
c.email,
c.country_id,
r.country_name
from customer c Full Outer join country r
on c.country_id=r.country_id;

*********************************************************

3 tables — customer city country

select
c1.cust_id,
c1.cust_name,
c1.mob_no,
c1.email,
c1.city_id,
c2.city_name,
c2.country_id country_id,
c3.country_name
from customer c1,city c2, country c3
where c1.city_id=c2.city_id
and c2.country_id=c3.country_id;

 

select
c1.cust_id,
c1.cust_name,
c1.mob_no,
c1.email,
c1.city_id,
c2.city_name,
c2.country_id country_id,
c3.country_name
from customer c1 inner join city c2
on c1.city_id=c2.city_id
inner join country c3
on c2.country_id=c3.country_id;

*************************************************************
— 2 tables join

select * from employees;

select * from departments;

select
e.employee_id,
e.first_name,
e.email,
e.SALARY,
e.department_id,
d.DEPARTMENT_NAME,
d.LOCATION_ID
from Employees e,departments d
where e.department_id=d.department_id;

select
e.employee_id,
e.first_name,
e.email,
e.SALARY,
e.department_id,
d.DEPARTMENT_NAME,
d.LOCATION_ID
from employees e inner join DEPARTMENTS d
on e.department_id=d.department_id;

— Left Outer Join

select
e.employee_id,
e.first_name,
e.job_id,
e.salary,
e.department_id,
d.DEPARTMENT_NAME
from employees e, departments d
where e.department_id=d.department_id(+);

select
e.employee_id,
e.first_name,
e.job_id,
e.salary,
e.department_id,
d.DEPARTMENT_NAME
from employees e left join departments d
on e.department_id=d.department_id;

—————————————————-

— Right outer join

select
e.employee_id,
e.first_name,
e.job_id,
e.salary,
d.department_id,
d.DEPARTMENT_NAME
from employees e, departments d
where e.department_id(+)=d.department_id;

select
e.employee_id,
e.first_name,
e.job_id,
e.salary,
e.department_id,
d.DEPARTMENT_NAME
from employees e right join departments d
on e.department_id=d.department_id;

— Full Outer Join

select
e.employee_id,
e.first_name,
e.job_id,
e.salary,
e.department_id,
d.DEPARTMENT_NAME
from employees e full join departments d
on e.department_id=d.department_id;

********************************************************************************

select the departmets where no employees are working

select
d.department_id,
d.DEPARTMENT_NAME
from employees e, departments d
where e.department_id(+)=d.department_id and e.employee_id is null;

================================================================================

3 tables join

select * from locations;

select
e.employee_id,
e.first_name,
e.job_id,
e.salary,
e.department_id,
d.DEPARTMENT_NAME,
d.location_id,
l.street_address,
l.city,
l.country_id
from employees e,departments d,locations l
where e.department_id=d.department_id
and d.location_id=l.location_id;

select
e.employee_id,
e.first_name,
e.job_id,
e.salary,
e.department_id,
d.DEPARTMENT_NAME,
d.location_id,
l.country_id
from employees e inner join departments d
on e.department_id=d.department_id
inner join locations l
on d.location_id=l.location_id;

 

select
e.employee_id,
e.first_name,
e.job_id,
e.salary,
e.department_id,
d.DEPARTMENT_NAME,
d.location_id,
l.country_id,
c.COUNTRY_NAME
from employees e inner join departments d
on e.department_id=d.department_id
inner join locations l
on d.location_id=l.location_id
inner join COUNTRIES c on l.COUNTRY_ID=c.COUNTRY_ID
where l.country_id=’CA’ and e.salary >10000;

——————————————–
4 tables join with sub query:
=============================
select
e.employee_id,
e.first_name,
e.job_id,
e.salary,
e.department_id,
d.DEPARTMENT_NAME,
d.location_id,
l.country_id,
c.country_name
from employees e inner join (select department_id,department_name,location_id from departments) d
on e.department_id=d.department_id
join locations l
on d.location_id=l.location_id
join countries c
on l.country_id=c.country_id where c.country_name=’United Kingdom’ and e.salary >10000;

— 5 tables Implicit joins

select
e.employee_id,
e.first_name,
e.job_id,
e.salary,
case when salary >10000 then ‘High salary’ else ‘Low Salary’ end salary_status,
e.department_id,
d.DEPARTMENT_NAME,
d.location_id,
l.country_id,
c.country_name,
j.job_title
from employees e, (select department_id,department_name,location_id from departments) d, locations l, countries c,jobs j
where e.department_id=d.department_id
and d.location_id=l.location_id
and l.country_id=c.country_id
and e.job_id=j.job_id and l.country_id in (‘UK’,’CA’) and e.job_id=’SA_REP’;

— 5 tables ANSI joins

select
e.employee_id,
e.first_name,
e.job_id,
e.salary,
case when salary >10000 then ‘High salary’ else ‘Low Salary’ end salary_status,
e.department_id,
d.DEPARTMENT_NAME,
d.location_id,
l.country_id,
c.country_name,
j.job_title
from employees e inner join (select department_id,department_name,location_id from departments) d
on e.department_id=d.department_id
join locations l
on d.location_id=l.location_id
join countries c
on l.country_id=c.country_id
join jobs j
on e.job_id=j.job_id
where l.country_id in (‘UK’,’CA’) and e.job_id=’SA_REP’;

select
e.employee_id,
e.first_name,
e.job_id,
e.salary,
case when salary >10000 then ‘High salary’ else ‘Low Salary’ end salary_status,
e.department_id,
d.DEPARTMENT_NAME,
d.location_id,
l.country_id,
c.country_name,
j.job_title
from employees e inner join (select department_id,department_name,location_id from departments) d
on e.department_id=d.department_id
join locations l
on d.location_id=l.location_id
join countries c
on l.country_id=c.country_id
join jobs j
on e.job_id=j.job_id
where e.job_id like ‘%CLERK’ and upper(c.country_name) like ‘%UNITED%’;

 

https://stackoverflow.com/questions/16263652/multiple-table-join-query-with-count-in-oracle-sql

——————————————————————————–

–Cross Join or Cartesian product

select * from departments;
select
e.employee_id,
e.first_name,
e.email,
e.SALARY,
e.department_id,
d.DEPARTMENT_NAME,
d.LOCATION_ID
from employees e, departments d
–where e.department_id=d.department_id;

107*27=2889

——————————————————————————–
self Join

select * from employees;

select
e1.employee_id,
e1.first_name,
‘reports to’,
e2.employee_id,
e2.first_name
from employees e1,employees e2
where e1.manager_id=e2.employee_id order by e1.employee_id;

——————————————————————–

–Non-Equi Join

select
e.employee_id,
e.first_name,
e.email,
e.SALARY,
e.department_id,
d.DEPARTMENT_NAME,
d.LOCATION_ID
from employees e, departments d
where e.department_id!=d.department_id;

 

——————————————————————–
Join with USING clause

SELECT employee_id,first_name,job_title,
department_name, city
FROM employees
INNER JOIN departments USING (department_id)
INNER JOIN locations USING (location_id)
INNER JOIN jobs USING (job_id)
ORDER BY 1;

——————————————————————-