Tuesday, 15 March 2011

SQL LEFT JOIN & RIGHT JOIN


The SQL JOIN clause is used whenever we have to select data from 2 or more tables.

To be able to use SQL JOIN clause to extract data from 2 (or more) tables, we need a relationship between certain columns in these tables

                                                                    Left Join


The LEFT JOIN keyword returns all rows from the left table (table_name1), even if there are no matches in the right table (table_name2).

Syntax :

SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name

The Pur table

OrderNo
custname
city
1
Siva
Srivilliputtur
2
Bala
Sivakasi
3
Kanna
Madurai

The PurDet table

sno
OrderNo
qty
rate
1
2
50
70
2
2
30
56
3
1
100
200
4
3
30
30
5
1
60
80

Example 
SELECT Pur.OrderNo as PurOrdNo,Pur.custname, Pur.City,PurDet.Orderno as PurDetOrdNo
FROM Pur
LEFT JOIN PurDet
ON Pur.Orderno=PurDet.OrderNo
ORDER BY Pur.CustName

PurOrdNo
custname
city
PurDetOrdNo
2
Bala
Sivakasi
2
2
Bala
Sivakasi
2
3
Kanna
Madurai
NULL
1
Siva
Srivilliputtur
1


                                                Right Join

The RIGHT JOIN keyword returns all the rows from the right table (table_name2), even if there are no matches in the left table (table_name1).

Syntax 
SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name=table_name2.column_name

Example
SELECT Pur.OrderNo as PurOrdNo,Pur.custname, Pur.City,PurDet.Orderno as PurDetOrdNo
FROM Pur
RIGHT JOIN Pur
ON Pur.Orderno=PurDet.OrderNo
ORDER BY Pur.CustName

PurOrdNo
custname
city
PurDetOrdNo
NULL
NULL
NULL
5
NULL
NULL
NULL
4
2
Bala
Sivakasi
2
2
Bala
Sivakasi
2
1
Siva
Srivilliputtur
1

 Share This Post


Friendly Learn Translate to Tamil

Smilely

Services

© 2014