JOINS IN SQL

JOINS

•  EQUI JOIN 
•  OUTER JOIN

1.     RIGHT OUTER JOIN
2.     LEFT OUTER JOIN
3.     FULL OUTER JOIN

• SELF JOIN 
• CROSS JOIN

EQUI joins

Matched Records from both table

select * from std;
select * from course;

select sname, cname  
from std, course
where std.cid=course.cid;

ALIAS NAME IN JOINS

select s.sname, c.cname  
from std s, course c
where s.cid=c.cid;

OUTER JOINS

LEFT OUTER JOIN

Its display unmatched records from left hand side.if mentionion left outer join using right 

hand side of the employees

select sname, cname  
from std, course
where std.cid=course.cid(+);

RIGHT OUTER JOIN

Its display unmatched records from right hand side.if mentionion left outer join using left 

hand side of the employees

select sname, cname  
from std, course
where std.cid(+)=course.cid;

FULL OUTER JOIN

Its display both matched and unmatched records from the table . In the full outer join the syntax is

Replacing where clause by on clause conditions are to be entered after the on clause Join is mentioned after the from clause 

Select col1,col2
from tab1 Full Join tab2 
on conditions 

select sname, cname  
from std full outer join course
on std.cid=course.cid;

Full Outer join with 3 tables

select e.first_name, e.department_id, d.location_id,l.city  
from employees e full outer join departments d  
on e.department_id=d.department_id 
full outer join locations l 
on d.location_id= l.location_id  

select sname,cname
from std, course;

SELF JOIN 

With in the table perform join conditions means its a self join

consider one example 

select employee_id, first_name,manager_id from employees;

select e1.first_name"Employee Name" ,e2.first_name "Manager Name"
from employees e1,employees e2
where e1.manager_id = e2.employee_id order by 1;

select * from std;
select * from course;
select * from faculty;

select s.sname,c.cname,f.fname
from std s, course c, faculty f
where s.cid=c.cid
and  c.cid=f.cid;

CROSS JOIN

Cross join is a cartesian product no of rows in the first table is joined no of rows in a second table.

Example :

With out mentining any join conditions in query is retrieve all the records with the 

combination of two or more tables

Select s.sname,c.cname 
from stud s,course c

NOTES: IF COMPARING N NUMBER OF TABLES AND PERFORMS JOINS MEANS IN WHERE CLAUSE N-1 

CONDITIONS.

n number of table 
n-1 of condition