How to retrive the data from two or more database tables with the help of alias and joins

Here i am retriving the data from 3 tables. there must be a one column which is comman in all table that is called as foreign key.

in our table "EMP_NO" is foreign key for "TABLE1 & TABLE2" and "EMP_DEPARTMENT_ID " is foreign key  for "TABLE2 & TABLE3"

TABLE1

--------------------------------------

EMP_NO       EMP_NAME

1            MUNNI

2            SHEELA

3            SHALU

4                  JILEBIBAI

-------------------------------------- 

TABLE2

-------------------------------------------------------------------

EMP_NO   EMP_DEPARTMENT_ID     EMP_DEPT

1        10                    IT

2      20                   COMP

3        30                   MECH

4                    50                  BOLLYWOOD

----------------------------------------------------------------------------------

TABLE3

-----------------------------------------------------------------------------------------------

EMP_DEPARTMENT_ID       EMP_ADDRESS

      10         DAHIWADI 1

      20         DAHIWADI 2

      30         DAHIWADI 3

      40                               DAHIWADI 4

----------------------------------------------------------------------------------------------

Query->

 SELECT      a.emp_name

           , b.emp_dept

                , c.emp_address

          

 FROM        table1 a

               , table2 b

               , table3 c

 WHERE  

                 a.emp_id=b.emp_id

     and      b.emp_departmet_id=c.emp_departmet_id;

----------------------------------------------------------------------------------------------    

In above query a, b, c are the alias.

and conditons given in where clause are the simple joins.

Actualy there are main 3 types of join in oracle.

1 LEFT Join.

2 RIGHT Join.

3 NATURAL Join

In oracle joins are declared using (+) sign.

for ex--

Left Join---

 WHERE  

                 a.emp_id (+)=b.emp_id

     and      b.emp_departmet_id(+)=c.emp_departmet_id;

RIGHT Join---

 WHERE  

                 a.emp_id=b.emp_id(+)

     and      b.emp_departmet_id=c.emp_departmet_id(+);

***When u use (+) sign it means that query retrive all records from that table and only saimilar records from other  table**

NOTE---Joins syntax in SQL and ORACLE are diffrent.

to see JOINS of SQL u can VISIT THIS site....

http://www.w3schools.com/sql/sql_join_left.asp

I hope u will get more info from there.

i also refer that site.

Thank you............................