JOINS in SQL with examples

JOINS
As it name indicates joins helps you to retrieve or fetch data from two or more tables as per the join condition.
There are mainly 3 types of joins.
1.Inner Join
2.Outer Join
3.Cross Join

So here we are going to describe joins with examples.
so consider we have two tables.
Table Employe and Department
Table 1:: Employee

Table 2: Department

So we are going to insert some dummy values into both tables
so here we go.

1) Inner Join
Inner join just take the row's which is present in both tables

select * from employee e
inner join department d
on e.depid=d.deptid

so here we have taken the inner join between employee table and department table
in both table department id is related.
so you can see who is having department id (1,2,3) is listing in our result set
so it will give you the result like this.
result:

if we don't want to search for all values we can modify our query like this.

select e.empname,d.deptname from employee e
inner join department d
on e.depid=d.deptid

It will return only employee  name and department names

If you just mention join by default it means Inner Join


2.OUTER JOIN
Outer join are of 3 Types
a). Left Outer Join
b). Right Outer Join
c). Full Outer Join

we can write the queries without outer keyword also
a). Left Outer Join/Left Join
 In Left Outer Join It will return all the values present in left table and matched value present in right table.

so first of all we are going to change the dummy value like this the we can distinguish these joins easily.

So we can write a left join  query and check the values

select * from employee e
left Outer join department d
on e.depid=d.deptid
result

empid	empname	depid	deptid	deptname
1	tony	1	1	manager
2	tom	2	2	hr
3	syam	1	1	manager
4	deep	3	3	employee
5	sind	3	3	employee
6	mahesh	6	NULL	NULL

so we will get all values from left table and matching items from right table.

b). Right Outer Join/Right Join

In case of right outer join It will return all values from right table and matching data from the left table.

so we can check our query.

select * from employee e
right Outer join department d
on e.depid=d.deptid

result

empid	empname	depid	deptid	deptname
1	tony	1	1	manager
3	syam	1	1	manager
2	tom	2	2	hr
4	deep	3	3	employee
5	sind	3	3	employee
NULL	NULL	NULL	4	ceo

so here it will give all values from our right table (department) and matching values from left(employee) table.

 c). Full Outer Join/Full Join
 Its nothing but it will show all rows from both left and right tables if you have not given any conditions in the where clause

select * from employee e
Full Outer join department d
on e.depid=d.deptid

empid	empname	depid	deptid	deptname
1	tony	1	1	manager
2	tom	2	2	hr
3	syam	1	1	manager
4	deep	3	3	employee
5	sind	3	3	employee
6	mahesh	6	NULL	NULL
NULL	NULL	NULL	4	ceo

if you want to show only rows which is doesn't having any null entries so modifiy your query like this.


select * from employee e
Full Outer join department d
on e.depid=d.deptid
where e.depid is not null and d.deptid is not null


3.CROSS JOIN
Cross outer join it will help to take the Cartesian product between all rows of first table to second table.


select * from employee e
cross join department d

result set:

empid	empname	depid	deptid	deptname
1	tony	1	1	manager
2	tom	2	1	manager
3	syam	1	1	manager
4	deep	3	1	manager
5	sind	3	1	manager
6	mahesh	6	1	manager
1	tony	1	2	hr
2	tom	2	2	hr
3	syam	1	2	hr
4	deep	3	2	hr
5	sind	3	2	hr
6	mahesh	6	2	hr
1	tony	1	3	employee
2	tom	2	3	employee
3	syam	1	3	employee
4	deep	3	3	employee
5	sind	3	3	employee
6	mahesh	6	3	employee
1	tony	1	4	ceo
2	tom	2	4	ceo
3	syam	1	4	ceo
4	deep	3	4	ceo
5	sind	3	4	ceo
6	mahesh	6	4	ceo

 
  So as per your requirement you can use the joins.