SQL Joins

How to give alias name to table?

Ans: Select * from sailor S;        

Here S is given alias name to sailor table.

How to give alias name to column?

Ans: Select Sid as id from sailor;

Here id is given as alias name to sid column of sailor table.

Types of Join:

(1)   Self Join

(2)   Simple Join

(3)   Outer Join

(1) Self Join:

            When table join it self then it is known as self join.

Examples:

(1) Select r1.day from reserves r1, reserves r2 where r1.sid = r2.bid;

(2) Select e1.ename as employee, e2.ename as manager from emp e1, emp e2

where e1.empno = e2.mgrno;

(2) Simple Join:

            When two table joins to each other for extracting data it is known as simple join.

Examples:

(1) Select S.Sname from sailor S, Reserves R Where S.sid = R.sid and R.bid=1;

(2) Select B.Color from Boat B, Sailor S, Reserves R Where B.Bid = R.Bid and

R.Sid = S.Sid and S.Sname = 'abc';

(3) Outer Join:

            When + operator is use with join it is known as outer join.

Examples:

(1) Select S.Sname from Sailor S, Reserves R where S.sid = R.sid (+);

// This Query display only those records whose does not belong to reserves table but belongs to sailor table.

(2) Select S.Sname from Sailor S, Reserves R where S.sid (+) = R.sid;

// This Query display only those records whose does not belong to sailor table but belongs to reserves table.

Note: We can not put + at both side of condition.