SQL Joins and Set Operations

--1) Inner/Equi join
--Inner/Equi join retrieves matching records
select fname,lname,deptname,depthead from employjoindepartment on employ.deptid=department.deptid
-- or
select * from employjoindepartment on employ.deptid=department.deptid
-- or
select fname,lname,deptname,depthead from employ e inner join department d on e.deptid=d.deptid

--old syntax for join
select fname,lname,deptname,depthead from employ e,department d where e.deptid=d.deptid
select e.deptid empdeptid,d.deptid deptdeptid,fname,deptname,depthead from employ e inner join department d on e.deptid=d.deptid

-- natural join in the other rdbms are equivalent to inner/equal join
-- Syntax of natural join
--select fname,lname,deptname,depthead from employ natural join department


-- 2) Outer joins
-- Left outer join: The result of a left outer join (or simply left join) for tables A and B always contains all records of the "left" table (A), even if the join-condition does not find any matching record in the "right" table (B).
-- Rigth outer join: A right outer join (or right join) closely resembles a left outer join, except with the treatment of the tables reversed. Every row from the "right" table (B) will appear in the joined table at least once. If no matching row from the "left" table (A) exists, NULL will appear in columns from A for those records that have no match in B.
-- Full outer join: a full outer join combines the effect of applying both left and right outer joins. Where records in the FULL OUTER JOINed tables do not match, the result set will have NULL values for every column of the table that lacks a matching row. For those records that do match, a single row will be produced in the result set (containing fields populated from both tables).

select d.deptid deptdeptid,e.deptid empdeptid,fname,deptname,depthead from department d left outer join employ e on d.deptid=e.deptid
select e.deptid empdeptid,d.deptid deptdeptid,fname,lname,deptname,depthead from employ e left outer join department d on e.deptid=d.deptid
select e.deptid empdeptid,d.deptid deptdeptid,fname,lname,deptname,depthead from employ e full outer join department d on e.deptid=d.deptid

--3) Non equi join
select e.deptid empdeptid,d.deptid deptdeptid,fname,lname,deptname,depthead from employ e join department d on e.deptid in (d.deptid)

--4) Cross join
select * from department cross join employ
select * from department , employ

--5) Self Join
create table employdetails(empid char(4),fname varchar(15),lname varchar(15),salary int,mgrid char(4))
insert into employdetails values('E01','Rahul','Raj',35000,null)
insert into employdetails values('E02','Anil','Babu',24000,'E01')
insert into employdetails values('E03','Samir','Thahir',23000,'E01')
insert into employdetails values('E04','Jaya','Krishnan',33000,'E02')
insert into employdetails values('E05','Suresh','Peters',34500,'E02')
insert into employdetails values('E06','Karthik','kumaraj',24500,'E03')
insert into employdetails values('E07','Bhawana','Aravind',28000,'E03')
select * from employdetails
select e.fname 'Employee',m.fname 'Manager' from employdetails m join employdetails e on e.mgrid=m.empid

-- Set Operations
-- Union
select deptid from employ union all select deptid from department
-- Intersect
select deptid from employ intersect deptid from department
-- Except
select deptid from department except deptid from employ
select deptid from employ except deptid from department

create table recruitmentagencies(agencycode char(4),agencyname varchar(5),percentagecharge tinyint)
insert into recruitmentagencies values('R1','Mafoi',25)
insert into recruitmentagencies values('R2','Star',20)
insert into recruitmentagencies values('R3','ABC',15)
select * from recruitmentagencies
create table contractrecruiter(contractercode char(4),contractername varchar(15),hirecharge tinyint)
insert into contractrecruiter values('C1','Anand',25)
insert into contractrecruiter values('C2','Subash',20)
insert into contractrecruiter values('C3','Karan',15)
select * from contractrecruiter

-- Structure of both the tables shud be same i.e. datatypes
select agencyname 'Recruiter', percentagecharge 'Charges' from recruitmentagencies union all select
contractername, hirecharge from contractrecruiter

-- Any/All
create table salaryrange(srange int)
insert into salaryrange values(27000)
insert into salaryrange values(28000)
insert into salaryrange values(32000)
select* from employ where salary<any (select * from salaryrange)
select* from employwhere salary>any (select * from salaryrange)
select* from employwhere salary<all (select * from salaryrange)
select* from employwhere salary>all (select * from salaryrange)

-- Identity
create table emp17(eid int identity,ename char(4))
insert into emp17 values('a')
insert into emp17 values('b')
insert into emp17 values('c')
select * from emp17
select @@identity

Cheers..!!
https://techiesss.blogspot.in/