Database Joins/SQL Joins/All Joins

All Joins Database Joins SQL Joins


What is JOIN?
When we want to retrieve data from more then one tables, then we use JOIN to display data from different tables as a single table.

Most applications that work on database are dependent on Joins and its very important strategy to use.
There are different types of JOINS depending on data retrieval according to Tables and data(records) in them.

Tuple/Record correponds to a single row.
Field refers to a single Column.

Inner Joins
Inner Joins are joins that return a row only when columns in Join contain values that satisfy Join condition. This means that if a row contains NULL in one of the columns in Join condition then that row in not returned.

Outer Joins
Outer Joins are joins that return a row only when columns in Join contain values that satisfy Join condition.
Outer joins differs from Inner Joins in manner that Outer Joins also return rows that contain NULL in any column in Join condition. Outer Joins are usually used to see the rows that dont satisfy Join condition.

Consider tables..
Employee(Empid, empname,deptid(Foreign Key))
Departments(deptid(Primary Key),deptname )

a)Right Outer Join
If we want to return record from the right side column in Join condition, that contains NULL then we use Right Outer Join.

Select e.empid, e.deptid, d.deptid, d.deptname
From Employee e, Departments d
Where e.deptid = d.deptid(+)

b)Left Outer Join
If we want to return record from the left side column in Join condition, that contains NULL then we use Left Outer Join.


Select e.empid, e.deptid, d.deptid, d.deptname
From Employee e, Departments d
Where e.deptid(+) = d.deptid

c)Full Outer Join
Returns records of Join condition invloving data from both Right Outer and Left Outer Join.

EquiJoin
EquiJoin or Comparison based Join or Theta Join is join that use Equal sign(=) or equality comparison in Join predicate/condition.
In EquiJoin columns should be same in tables and should also contain same values.
EquiJoins can be considered as specific type of Join to connect Primary and Foreign key columns in different tables.

Consider tables..
Employee(Empid, empname,deptid(Foreign Key))
Departments(deptid(Primary Key),deptname )

Select e.empid, e.deptid, d.deptid, d.deptname
From Employee e, Departments d
Where e.deptid = d.deptid

Non EquiJoin
Non-EquiJoin can be considered opposite to EquiJoin as it uses operator other then the Equalty.
Means Non-Equijoin contains operators like leass(<) freater(>) or uses BETWEEN and AND operators in Join condition.

Consider tables..
Employee(Lastname, Salary)
Job Grades(Grades,Lowsalary,Highsalary)

Select e.lastname, e.sal, j.gradlevel
From Employee e, Job Grades j
Where e.salary BETWEEN j.lowsalary AND j.highsalary


Self Join
Self Join is a type of Join that is used to Join a table to itself.


Consider tables
Employee(Worker)[employeeid, lastname,managerid(Foreign Key)] note that managerid is Foreign Key here and referes to the Employeeid as Primary Key in Employee(Manager)table.

Employee(Manager)[Employeeid(Primary Key), lastname]


Select worker.lastname 'works for' manager.lastname
From Employee worker, Employee manager
Where worker.managerid = manager.employeeid

Cross Join
Cross Join works same as Cartesion Product which means that every record or row in first table is connected to every sigle row of second table.

Select lastname, deptname
From Employee
CROSS JOIN departments

Natural Join
Natural Join is based on retrieval of data of all columns in both tables that have same name. It selects row from both tables having Equal values in all matched columns.

Select deptid, deptname, locid, city
From departments
NATURAL JOIN locations