SQL FULL JOIN

The FULL JOIN keyword return rows when there is a match in one of the tables.

Syntax 


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


Pur

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

PurDet

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
Full Join PurDet
ON Pur.Orderno=PurDet.OrderNo 

ORDER BY Pur.CustName

Output :

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

Quick Links :


      Refer Left  & Right Join ....  Click Me 
  

Posted by Siva 0 comments

Read More

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

Posted by Siva 0 comments

Read More

SQL INNER JOIN

The INNER JOIN keyword return rows when there is at least one match in both tables.

SELECT column_name
FROM table_name1
INNER 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	5	30	30
5	4	60	80

Example :



SELECT PurDet.OrderNo,Pur.custname, Pur.City
FROM Pur
INNER JOIN PurDet
ON Pur.Orderno=PurDet.OrderNo
ORDER BY Pur.CustName

OrderNo	custname	city
2	Bala	Sivakasi
2	Bala	Sivakasi
1	Siva	Srivilliputtur

Posted by Siva 0 comments

Read More

SQL JOINS

• JOIN: Return rows when there is at least one match in both tables
• LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table
• RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table
• FULL JOIN: Return rows when there is a match in one of the tables

Posted by Siva 0 comments

Read More