JOINS SQL

--JOINS---


---These are the SQL Queries which help us to retreive data from either single or multiple tables with or without conditions.

----DIFFERENT TYPES OF JOINS--

1)CROSS JOINS(NO CONDITION):--

  ----Just combine the rows of first table with the rows of the second table.The output is the cartesian product of the rows  from first query with the other.


----2)INNER JOINS:--

----The output is based upon comparing the two columns data using comparison operators:--


select * from emp
select * from emp1


--query for the inner join--
select e.eno, e.salary, f.eno,f.salary from
emp e join emp1 f on e.eno=f.eno

select e.eno, e.salary, f.eno,f.salary from
emp e ,emp1 f where e.eno=f.eno


---outer joins--
select * from emp
select * from emp1

insert emp values(350,4000,'10-may-15')

insert emp1 values(360,8777,'15-may-15')

--outer joins--
---These are used to retrieve data from multiple tables by comparing two columns with non-similar values

----3 types:

----1)Left outer join:--

----The data is retreived by giving preferences to column data which is mentioned in  the table that is defined on the left side of the keyword left  outer join

 select e.eno, e.salary, f.eno,f.salary from
emp e left outer join  emp1 f on e.eno=f.eno


---2) Right  outer join
The data is retreived by giving preferences to column data which is mentioned in  the table that is defined on the right side of the keyword right outer join

select e.eno, e.salary, f.eno,f.salary from
emp e right  outer join  emp1 f on e.eno=f.eno

--3)  Full  outer join
The data is retrieved by giving no  preferences to column data
select e.eno, e.salary, f.eno,f.salary from
emp e  full  outer join  emp1 f on e.eno=f.eno


----self joins----

---Used for comparing data from 2 columns belonging to the same table:--

select * from emp

alter table emp add mgrno int

update emp set  mgrno=200 where eno=300

--query for self join---
select * from emp
select e.eno,e.salary, f.mgrno from  emp e inner join emp f on e.eno=f.mgrno