SQL Table Joins- Quick Reference

EQUI-JOIN -------------- select * from emp INNER JOIN dept ON emp.deptId = dept.id; ============================================================================ INNER JOIN ---------------- select e.empId, j.salary from jobs j INNER JOIN emp e ON e.salary BETWEEN j.salary ---OR--------------------------- select e.empId, j.salary from jobs j INNER JOIN USING(Salary) **Equi Join only use to retrieve data base on '='. But Inner Join can use to retrieve data base on '=', '<', '>' **NATURAL JOIN is same to the INNER JOIN but use NATURAL JOIN keyword and which avoids duplicate columns. ============================================================================= SELF JOIN -------------- This also use INNER JOIN key word but used to retrive data from the same table. SELECT e.first_name AS 'Employee FN', e.last_name FROM employees AS e LEFT OUTER JOIN employees AS m ON e.manager =m.id ============================================================================= REFER:- http://www.codinghorror.com/blog/archives/000976.html http://blog.sqlauthority.com/2009/04/13/sql-server-introduction-to-joins-basic-of-joins/ LEFT OUTER JOIN ---------------------------- Select * From emp LEFT OUTER JOIN dept ON emp.deptId = dept.Id RIGHT OUTER JOIN --------------------------- Select * From emp RIGHT OUTER JOIN dept ON emp.deptId = dept.Id FULL OUTER JOIN --------------------------- select * From emp FULL OUTER JOIN dept ON emp.eid = dept.id **Outer joins will display all columns in both tables filled with null values for culumns which doesnt retrieve the through query. More Info:- http://en.wikipedia.org/wiki/Join_(SQL)