Deepan Garnaik

• Aug 11th, 2006

 

Inner Join will return rows that have matching records in both table, but in case of full outer join it will return all records from left table and from right table.

In Left-Outer join all the record from the left-most table will be fetched and if records found in the 2nd table then field values will be displayed else it will display Null in the 2nd table fields.

Ex.
Create table EMPNAME (ID int, EMPNAME varchar(20))
CREATE table EMPADDRESS (ID int, ADDRESS varchar(20))

INSERT INTO EMPNAME VALUES(1, 'DEEPAN')
INSERT INTO EMPNAME VALUES(2, 'RAMESH')
INSERT INTO EMPNAME VALUES(3, 'PRADEEP')
INSERT INTO EMPADDRESS  VALUES(1, 'BANGALORE')
INSERT INTO EMPADDRESS  VALUES(2, 'DELHI')
INSERT INTO EMPADDRESS  VALUES(4, 'DELHI')

SELECT * FROM EMPNAME
ID          EMPNAME             
----------- --------------------
1           DEEPAN
2           RAMESH
3           PRADEEP

SELECT * FROM EMPADDRESS
ID          ADDRESS             
----------- --------------------
1           BANGALORE
2           DELHI
4           DELHI

Example : INNER JOIN
===================================================================
SELECT EMPNAME.ID, EMPNAME.EMPNAME, EMPADDRESS.ADDRESS FROM EMPNAME
JOIN EMPADDRESS ON EMPADDRESS.ID = EMPNAME.ID

Output of the above JOIN Query (i.e Inner Query)
-----------------------------------------------------
ID          EMPNAME              ADDRESS             
----------- -------------------- --------------------
1           DEEPAN               BANGALORE
2           RAMESH               DELHI

Example : FULL OUTER JOIN
===================================================================
SELECT EMPNAME.ID, EMPNAME.EMPNAME, EMPADDRESS.ADDRESS FROM EMPNAME
FULL OUTER JOIN EMPADDRESS ON EMPADDRESS.ID = EMPNAME.ID

Output of the above FULL OUTER JOIN Query
-----------------------------------------------------
ID          EMPNAME              ADDRESS             
----------- -------------------- --------------------
1           DEEPAN               BANGALORE
2           RAMESH               DELHI
3           PRADEEP              NULL
NULL        NULL                 DELHI

Example : LEFT OUTER JOIN
===================================================================
SELECT EMPNAME.ID, EMPNAME.EMPNAME, EMPADDRESS.ADDRESS FROM EMPNAME
LEFT OUTER JOIN EMPADDRESS ON EMPADDRESS.ID = EMPNAME.ID

Output of the above LEFT OUTER JOIN Query
-----------------------------------------------------
ID          EMPNAME              ADDRESS             
----------- -------------------- --------------------
1           DEEPAN               BANGALORE
2           RAMESH               DELHI
3           PRADEEP              NULL

Example : RIGHT OUTER JOIN
===================================================================
SELECT EMPNAME.ID, EMPNAME.EMPNAME, EMPADDRESS.ADDRESS FROM EMPNAME
RIGHT OUTER JOIN EMPADDRESS ON EMPADDRESS.ID = EMPNAME.ID

Output of the above RIGHT OUTER JOIN Query
-----------------------------------------------------
ID          EMPNAME              ADDRESS             
----------- -------------------- --------------------
1           DEEPAN               BANGALORE
2           RAMESH               DELHI
NULL        NULL                 DELHI

vijay

• Aug 12th, 2006

 

Hi,

Thank U very much. Now i got it very clear and i got cleared all my doubts regarding joins concept.

Thank U once again.

regards,

vijay

Deepek Bhandari

• Mar 30th, 2007

 

In inner join it returns only the matching rows of joining table but in full outer join it returns matching rows and include all the unmatched row from both table look as following exemple if we are joining two table one have 5 rows and other have 10 rows and three rows in each tables are maching the condition then result according to no. of rows will be in inner join: only three rows, in full outer join: 3+2+7 (3 matched rows+2 unmatched rows from first table + 7 unmatched rows from second table).

sa_dub

 Profile Answers by sa_dub 

• May 4th, 2009

 

Inner Join is used to join two coloumns of the same table which have some same attributes but Outer Join used for join two coloumns of different tables which have unmached attributes.

zeekhan

 Profile Answers by zeekhan 

• Aug 12th, 2009

 

Inner Join returns matching records only

A full outer join returns all rows in both the left and right tables