Databse Joins example!

CREATE TABLE employee
(employeeid int(10) default NULL,
employeefname varchar(12) default NULL,
employeelname varchar(12) default NULL,
CONSTRAINT employeeid PRIMARY KEY (employeeid));

Insert Into employee (employeeid, employeefname, employeelname) values('1001', 'john', 'david');
Insert Into employee (employeeid, employeefname, employeelname) values('1002', 'peter', 'lewis');
Insert Into employee (employeeid, employeefname, employeelname) values('1003', 'ravi', 'thapliyal');
Insert Into employee (employeeid, employeefname, employeelname) values('1004', 'iqbal', 'khan');
Insert Into employee (employeeid, employeefname, employeelname) values('1006', 'ram', 'nath');

CREATE TABLE executives
(employeeid int(10) default NULL,
employeefname varchar(12) default NULL,
employeelname varchar(12) default NULL, 
CONSTRAINT employeeid PRIMARY KEY (employeeid));
Insert Into executives (employeeid, employeefname, employeelname) values('1001', 'john', 'david');
Insert Into executives (employeeid, employeefname, employeelname) values('1005', 'peter', 'colardo');
Insert Into executives (employeeid, employeefname, employeelname) values('1004', 'robert', 'jain');

Inner Join:
SELECT emp.employeefname, exec.employeelname
FROM employee emp, executives exec
WHERE emp.employeeid = exec.employeeid;
------------------------------------------
employeefname    employeelname 
------------------------------------------
john                      david
iqbal                     jain
------------------------------------------

Left Outer Join:
select emp.employeefname, exec.employeelname from employee emp left join executives exec on emp.employeeid = exec.employeeid;

Or

select emp.employeefname, exec.employeelname from employee emp left outer join executives exec on emp.employeeid = exec.employeeid;

Or

SELECT emp.employeefname, exec.employeelname
FROM employee emp, executives exec where emp.employeeid = exec.employeeid(+);

------------------------------------------
employeefname    employeelname 
------------------------------------------
john                      david
peter   
ravi   
iqbal                      jain
ram
------------------------------------------ 

Right Outer join:
select emp.employeefname, exec.employeelname from employee emp right join executives exec on emp.employeeid = exec.employeeid;

Or

select emp.employeefname, exec.employeelname from employee emp right outer join executives exec on emp.employeeid = exec.employeeid;

Or

SELECT emp.employeefname, exec.employeelname
FROM employee emp, executives exec where emp.employeeid(+) = exec.employeeid;

------------------------------------------
employeefname    employeelname 
------------------------------------------
john                      david
                             colardo
iqbal                      jain
------------------------------------------

Full Outer join:
select emp.employeefname, exec.employeelname from employee emp full outer join executives exec on emp.employeeid = exec.employeeid;

NOTE: MySql does not support FULL JOINS. You can do the full outer join by using union, e.e

select emp.employeefname, exec.employeelname from employee emp left join executives exec on emp.employeeid = exec.employeeid
Union
select emp.employeefname, exec.employeelname from employee emp right join executives exec on emp.employeeid = exec.employeeid;

-K Himaanshu Shukla....