join two tables:

join two tables:
a relational operation that causes two tables with a common domain to be  combined into a single table or view.

Equi-join :
A type of join that is based on equalities is called equi-join.

Syntax:        select table1.column, table2.column
            From table1, table2
            Where table1.column1=table2.column2
Example:
Write  a querry that display empno, empname, deptno, deptname and location from employee and department table.
Select employee.empno, emp.empname, emp.deptno, department.deptname, dept.loc
Where emp.deptno=dept.deptno.


Natural join:
Same as equi join except one of the duplicate columns is eliminated in the table.

Non equi-join:
A non equi join specifies the relationship b/w columns of different tales by using relation
(<, >, <=, >=).
Example:
Select emp.ename, emp. Salarygrade,grade
From emp, sal between salgrade. Losal (means low salary) and salgrade.hisal;

Outerjoin:
a join in which rows that don’t hve matching values in common columns are also included in the result.
Three types:
Full outerjoin:

STUDENT

Name        Age    Class      
Ali              21    1ST      
Bilal           22    2nd      
Kamran     23    3rd      
Danish       24    4th    







CLASS   

Class    Day      
1st    Monday      
2nd    Tuesday      
4th    Thrusday      
Master    Friday      
m.phils    saturday      
       


Full outer join:
Select emp.empno. emp.ename, emp.deptno
Dept. dname, dept. loc

From emp full outerjoin dept
On emp.deptno=dept. deptno.



Left outer join:
Select emp.empno. emp.ename, emp.deptno
Dept. dname, dept. loc

From emp left outer join dept
On emp.deptno=dept. deptno.


right outer join:
Select emp.empno. emp.ename, emp.deptno
Dept. dname, dept. loc

From emp right outerjoin dept
On emp.deptno=dept. deptno.


Sub querry:
A querry within another query is called sub querry. It is normally a select statement inside select statement. It is also called nested select or inner select statement.

Select ename, job, sal
From emp
Where sal=(select min(sal) from emp);

Select ename,sal, deptno
From emp
Where sal in (select min(sal) from emp groupby deptno)
Select getdate( );    for date finding
Select datediff(month, ‘1/1/2, ‘12/31/02’);
Select datediff(year, ‘1/1/2, ‘12/31/02’);
Select datediff(day, ‘1/1/2, ‘12/31/02’);