Joins in SQL

Joins in Structured Query Language (S Q L) are used to Store and Retrieve data from two or more Tables . Joins Store and Retrieve data On the basis of given Join Condition. The Given Join condition tells how and which data to retrieve / store in and from Database Tables.





TYPES OF JOINS :-

* Inner Join
* Outer Join
--* Left Outer Join
--* Right Outer Join
* Self Join

INNER JOIN :- Inner Join is used to Retrieve data from one or more tables . Inner join only retrieves the data that satisfy the given condition. Mostly in Inner join if three tables are used then a common data-type field like id of students is treated as common in all tables and data of student id is retrieved from all tables where id in first table matches with id in second table and id in third table

 

Fig 1.2

Query For Inner Join :- 

Select c.cid, c.name, c.branch , h.roomnumber, l.no_of_books_issue

From Student_class c

inner join Student_hostel

on

c.cid = h.hid

inner join Student_library l

on

c.cid = l.lid

# Where 

   * Student_class

   * Student_hostel

   * Student_library  are 3 Tables

# c.cid, c.name, c.branch , h.roomnumber, l.no_of_books_issue are fields of these tables with common data-type field cid, hid, lid.

RESULT :-

 

Fig 1.2

OUTER JOIN :-  Outer Join is used to Retrieve data from one or more tables . Outer Join generally Retrieve data from tables that is common in both tables along with the data on left or right table depending on  which join is used Right or Left Join . If left join is used output must contain all common values of both tables and all values of table2

LEFT JOIN  :-  Left Join will Retrive or Store data that has same common data-type in both tables along with the left table data that is not common in both tables. In left join query table given first will be treated as left table and second given table will be treated as Right table

QUERY :- 

select * from Student_class
select * from student_hostel
select * from student_library
select c.cid, c.name, c.branch, h.roomnumber
From Student_class c
left join student_hostel h
on
c.cid = h.hid

RIGHT JOIN  :-  Right Join will Retrieve or Store data that has same common data-type in both tables along with the Right table data that is not common in both tables. The Right join matches the values of two tables and It first finds the equal column values in two tables and at output it will include all equal values in both tables and all values of Right table

 

QUERY  :-

1.

select * from Student_class

select * from student_hostel

select * from student_library

select c.cid, c.name, c.branch, h.roomnumber, i.no_of_books_issue

From Student_class c

right join student_hostel h

on

c.cid = h.hid

right join student_library l

on c.cid  = l.lid

2. 

select * from Student_class

select * from student_hostel

select * from student_library

select c.cid, c.name, c.branch, h.roomnumber

From Student_class c

right join student_hostel h

on

c.cid = h.hid

OUTPUTS :-

1.                                                                                                   2.

SELF JOIN :-  Self join is operated on table itself. It is used to join the table to itself . The main purpose of self join is if you want to rename that table with same table columns.

QUERY :-

select * from Student_class

select * from student_hostel

select * from student_library

select c.cid, c.name, c.branch

From Student_class c

join student_hostel h

on

c.cid = h.hid

OUTPUTS :-

 CROSS JOIN :-  Cross Join produced the cartesian product of all column-values of two tables .  In cross join all rows of Table1 is matched with all rows of Table2 .

QUERY :-

select * from Student_class

select * from student_hostel

select * from student_library

join student_hostel 

on

c.cid = h.hid

OUTPUTS :-