Welcome to the Online Session

on
Database Management System
by
Mrs. Vrushali A. Patil
 Contents of Session

• Introduction JOIN concept.

• Types of JOIN with examples.
 Learning Objectives
Students will be able to:
• Describe JOIN concept.
• Describe all JOIN operations.
operations
• Execute queries using JOIN.
JOIN
 JOIN in SQL
• JOIN is a statement that combines data or rows from two or more than
two tables.
• In order to access data from two tables it requires some common field-
field
attribute between them.
• JOIN keyword is used to in query to join more than one tables.
• There are following types of joins in SQL:
1. INNER JOIN/ EQUI JOIN
• NATURAL JOIN
2. NON EQUI JOIN
3. SELF JOIN
4. OUTER JOIN
• LEFT OUTER JOIN
• RIGHT OUTER JOIN
• FULL OUTER JOIN
 INNER JOIN or EQUI JOIN
• It generates result based on matched data from two table by
checking equality(=) condition.
• It returns rows/tuples from two tables which satisfy equality
condition specified in query.

Matched/Common data
 INNER JOIN or EQUI JOIN(contd.)
Cust_id cname Loan_id Balance Loan_no cid Branch Amount
1 Max L1 200000 L1 1 Mumbai 1000000
2 Merry L2 180000 L2 2 Bangalore 800000
3 Michle L3 250000 L3 3 Mumbai 1500000
4 Max NULL 300000 Fig.2 borrower Table
Fig.1 Customer Table
• Select cust_id, Loan_id,, Branch, Amount from customer INNER
JOIN borrower ON customer.cust_id=borrower.cid;
customer.cust_id
Cust_id Loan_id Branch Amount
1 L1 Mumbai 1000000
2 L2 Bangalore 8000000
3 L3 Mumbai 1500000
Fig.3 Result Table
 NATURAL JOIN
• It generates result based on same attribute name and datatypes.
• Resulting table contains all attributes from both table and only one
copy of a common attribute.
Doctor_id Specialization Cabin Doctor_id Patient Assign_date
D1 Child spec. First floor D1 ABC 2019-03-02
D2 Cardiologist Second floor D2 PQR 2020-05-06
D3 General Physician Ground floor D3 XYZ 2020-04-05
D4 Dentist First floor Fig.2 Patient Table
Fig.1 Doctor Table

• Select * from Doctor NATURAL JOIN Patient;

 NATURAL JOIN (contd.)
Doctor_id Specialization Cabin Patient Assign_date
D1 Child spec. First floor ABC 2019-03-02
D2 Cardiologist Second floor PQR 2020-05-06
D3 General Physician Ground floor XYZ 2020-04-05
Fig.1 NATURAL JOIN
NNER JOIN Query:
Select * from Doctor INNER JOIN Patient ON Doctor.Doctor_id= Patient.Doctor_id;

Doctor_id Specialization Cabin Doctor_id Patient Assign_date

D1 Child spec. First floor D1 ABC 2019-03-02
D2 Cardiologist Second floor D2 PQR 2020-05-06
D3 General Physician Ground floor D3 XYZ 2020-04-05
Fig.2 INNER JOIN
 NON EQUI JOIN
• NON EQUI JOIN uses comparison operators(IN,BETWEEN,!=,>,<,>=,<=)
other than equal(=) in join condition.
Roll_no Sname Per Lper Hper Grade
1 Max 62 40 60 Second Class
2 Mili 82 61 80 First Class
3 Michle 45 81 99 Distinction
4 Jack 88 Fig.2 Grade Table
5 Jones 55
6 Jimmy 66
Fig.1 Student Table

Select Roll_no,, Per, Grade from Student, Grade where Per between Lper and Hper;
Select Roll_no,, Per, Grade from Student, Grade where Per>=Lper
Per>= and Per<=Hper;
NON EQUI JOIN(contd.)
JOIN
Roll_no Per Grade
1 62 First Class
2 82 Distinction
3 45 Second Class
4 88 Distinction
5 55 Second Class
6 66 First Class

Fig.1 Result Table

 SELF JOIN
• In SELF JOIN table is joined with itself.
eid Ename Supervisor Salary
E001 John NULL 45000
E002 Jack E003 25000
E003 Jones E001 50000
E004 james E003 28000
E005 Johnson E003 25000
E006 Jerry E001 35000
Fig.1 Customer Table

• Select a.eid as “empoyee id”, a.ename

ename “employee name”, b.eid as “supervisor
id”, b.ename as “supervisor name” from employee a, employee b where
a.supervisor= b.eid;
 Self JOIN (contd.)
Emplo Employee Supervisor id Supervisor
yee_id name name
E003 Jones E001 John
E006 Jerry E001 John
E002 Jack E003 Jones
E004 james E003 Jones
E005 Johnson E003 Jones
Fig.1 SELF JOIN
 LEFT OUTER JOIN
• It returns all rows/tuples from left table and matching rows from right
table.
• It displays all rows from left table even if there are no matching values in
right table.
• When right table does not have matching rows for the records in left
table the columns in right table contains NULL values.

All rows from left table

 LEFT OUTER JOIN(contd.)
Cust_id cname Loan_id Balance Loan_no cid Branch Amount
1 Max L1 200000 L1 1 Mumbai 1000000
2 Merry L2 180000 L2 2 Bangalore 800000
3 Michle L3 250000 L3 3 Mumbai 1500000
4 Max NULL 300000 Fig.2 borrower Table
Fig.1 Customer Table
• Select cust_id, Loan_id,, Branch, Amount from Customer LEFT
OUTER JOIN borrower ON customer.cust_id=borrower.cid;
customer.cust_id
Cust_id Loan_id Branch Amount
1 L1 Mumbai 1000000
2 L2 Bangalore 8000000
3 L3 Mumbai 1500000
4 NULL NULL NULL
Fig.3 Result Table
 RIGHT OUTER JOIN
• It returns all rows/tuples from right table and matching rows from left
table.
• It displays all rows from right table even if there are no matching values
in left table.
• When left table does not have matching rows for the records in right
table the columns of left table contains NULL values.

All rows from right table

 RIGHT OUTER JOIN(contd.)
Cust_id cname Prod_id Order_date Pid Pname Price Brand
1 Max P1 2019-03-21 P1 Printer 25000 HP
2 Mili P4 2019-05-06 P2 Scanner 20000 EPSON
3 Michle P4 2020-08-19 P3 Modem 15000 D-Link
Fig.1 Customer Table P4 Laptop 40000 Lenovo
Fig.2 Product Table
Select cust_id,cname, Pid, Pname, Price, Brand from Customer RIGHT OUTER JOIN
Product ON Customer.Prod_id=Product.PidPid;
Cust_id Cname Pid Pname Price Brand
1 Max P1 Printer 25000 HP
NULL NULL P2 Scanner 20000 EPSON
NULL NULL P3 Modem 15000 D-Link
2 Mili P4 Laptop 40000 Lenovo
3 Michle P4 Laptop 40000 Lenovo
Fig.3 Result Table
 FULL OUTER JOIN
• It returns all rows/tuples from both left and right tables even if
other does not have matching rows.
• For unmatched rows, resultant table contains NULL values.

All rows from both tables

 FULL OUTER JOIN(contd.)
Roll_no Sname Book Class Book_id Bname Author
1 Max B1 FY B1 Prog. In C E.Balguruswamy
2 Mili B2 FY B2 HTML & CSS Jon Duckett
3 Michle B3 SY B3 DBMS Concepts Henry Korth
4 Jack B1 FY B4 Head First Java Kathy Sierra
5 Jones B3 TY B5 MongoDB in Kyle Banker
6 Jimmy NULL TY Action
7 John NULL FY
Fig.2 Book Table
Fig.1 Student Table

Select Roll_no, class, Book_id, Bname from Student FULL OUTER JOIN Book ON
Student.Book=Book.Book_id;
 FULL OUTER JOIN(contd.)
Roll_no Class Book_id Bname
1 FY B1 Prog. In C
2 FY B2 HTML & CSS
3 SY B3 DBMS Concepts
4 FY B1 Prog. In C
5 TY B3 DBMS Concepts
6 TY NULL NULL
7 FY NULL NULL
NULL NULL B4 Head First Java
NULL NULL B5 MongoDB in Action
Fig.3 Result Table