sql server joins

1/*General Formula For Joins*/
2SELECT Column_List
3FROM Left_Table_Name
4JOIN_TYPES Right_Table_Name
5ON Join_Condition
6
7/*INNER JOIN - Matching Rows + Non Matching Rows are Eliminated
8  LEFT JOIN - Matching Rows + Non Matching Rows from the Left table
9  RIGHT JOIN - Matching Rows + Non Matching Rows from the Right table
10  FULL JOIN - Matching Rows + Non Matching Rows from the Both  tables
11  CROSS JOIN - Return cartesian product of the tables involved in the Join
12  SELF JOIN - Return each table row is combined with itself and with every other table row.
13  The SELF JOIN can be thought of as a JOIN of two copies of the same tables.*/
14  
15/*Cross Join*/
16SELECT Column_List
17FROM Left_Table_Name
18CROSS JOIN Right_Table_Name

1SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
2FROM Orders
3INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;

1INNER JOIN:
2is used when retrieving data from multiple
3tables and will return only matching data.
4
5LEFT OUTER JOIN:
6is used when retrieving data from
7multiple tables and will return
8left table and any matching right table records.
9
10RIGHT OUTER JOIN:
11is used when retrieving data from
12multiple tables and will return right
13table and any matching left table records
14
15FULL OUTER JOIN:
16is used when retrieving data from
17multiple tables and will return both
18table records, matching and non-matching.
19
20
21
22INNER JOIN :
23SELECT select_list From TableA A
24Inner Join TableB B
25On A.Key = B.Key
26
27
28LEFT OUTER JOIN :
29SELECT select_list From TableA A
30Left Join TableB B
31On A.Key = B.Key
32
33(where b.key is null)//For delete matching data
34
35
36
37RIGTH OUTER JOIN :
38SELECT select_list From TableA A
39Right Join TableB B
40On A.Key = B.Key
41
42
43FULL JOIN :
44SELECT select_list From TableA A
45FULL OUTER Join TableB B
46On A.Key = B.Key
47
48

1CREATE PROCEDURE SP_Purechase_Selecte
2
3AS
4
5SELECT
6p.P_Id ,
7p.p_name,
8p.alias_name,
9p.alias_code,
10p.p_name_inlocal_language,
11p.barcode,
12p.buy_price,
13p.sell_price_tak,
14p.sell_price_ko,
15p.qty,
16typeQty.qty_type,
17b.brand_name,
18CA.cat_name ,
19SN.sn_name ,
20
21P.P_Description ,
22p.Barwari_Krin 
23
24 FROM tbl_Purchase p 
25 INNER JOIN tbl_Qty_Type typeQty ON P.qty_type_id=typeQty.id
26 INNER JOIN tbl_Brand b ON p.brand_id=b.brand_id
27 INNER JOIN tbl_Catigory CA ON P.cat_id =CA.cat_id
28 INNER JOIN tbl_Sceintific_Name SN ON p.sn_id= SN.sn_id
29 INNER JOIN tbl_Product_Creation
30 GROUP BY P.P_Id, 
31CA.Cat_Name ,
32P.P_Name ,
33SN .Sn_Name ,
34P.sellPrice ,
35P.buyPrice ,
36P.Barcode , 
37P.Qty ,
38P.P_Description,
39p.Barwari_Krin 
40RETURN