SQL Joins

SQL Joins

The following post gives easy explanation of types of SQL joins and its syntaces,

1. Inner Join
2. Left Join (or) Left Outer Join
3. Right Join (or) Right Outer Join
4. Self Join

1. Inner Join
Inner Join returns all the common resultset that matches the search conditions for the join

ex: SELECT e.ID, e.Name, j.salary
FROM Employee e
INNER JOIN Job j
ON e.Id = j.ID
WHERE j.Title = 'engineers'

2. Left Join or Left Outer Join:
LEFT JOIN, in a similar fashion retrives all records that match in the search condition same way and IN ADDITION with extra row for each unmatched record in the left table of the join

ex: SELECT e.ID, e.Name, j.salary
FROM Employee e
LEFT JOIN Job j (or - LEFT OUTER JOIN Job j)
ON e.Id = j.ID

3. Right Join or Right Outer Join:
RIGHT JOIN, in a similar fashion retrives all records that match in the search condition same way and IN ADDITION with extra row for each unmatched record in the right table of the join

ex: SELECT e.ID, e.Name, j.salary
FROM Employee e
RIGHT JOIN Job j (or - RIGHT OUTER JOIN Job j)
ON e.Id = j.ID

4. Self Join:
SELF JOIN is used to retrive records from the same table for a specific search condition.

The Manager column in Table Employee simply references the employee ID of another employee in the same table. And want to retrive resultset for Employee names and their Manager names, following sql query can be used,

ex:SELECT e.Name as 'EmployeeName', m.Name as 'ManagerName'
FROM employees AS e
LEFT OUTER JOIN employees AS m
ON e.manager = m.id