|
Sometimes the data from different tables need to be fetched in the applications. The joins in SQL is used to achieve this aim in the applications. There are following types of Joins:
- Inner Join : An Inner Join will take two tables and join them together based on the values in common columns (linking field) from each table. The INNER JOIN returns all rows from both tables where there is a match. If there are rows in Employees that do not have matches in Orders, those rows will not be listed.
- Outer Join: Outer join is used to forcefully retrieve the data from the corresponding tables if the data is missing from the table for the corresponding record in another table. The outer join is further divided into two divisions i.e. either they are left outer join or right outer join.
- Self Join: This is a particular case when one table joins to itself, with one or two aliases to avoid confusion. A self join can be of any type, as long as the joined tables are the same
- Cartesian Join: This will result a Cartesian product of the two tables. Let take an example if there are 20 rows in table1 and 30 rows in table2 then the result would be 20 multiply by 30 that is 60. This will give the Cartesian product of the records that are there in the database.
The following Stored Procedure needs to be created in the database for implementing Joins
1) SELF JOIN
CREATE PROCEDURE sp_selfjoin
AS
SELECT DISTINCT TOP 50 PERCENT dbo.authors.au_lname, dbo.authors.au_fname, dbo.authors.city
FROM dbo.authors INNER JOIN
dbo.authors authors_1 ON dbo.authors.city = authors_1.city AND
dbo.authors.au_fname + ' ' + dbo.authors.au_lname <> authors_1.au_fname +' ' + authors_1.au_lname
ORDER BY dbo.authors.city, dbo.authors.au_lname
2) CARTESIAN JOIN
CREATE PROCEDURE sp_cartesianJoin
AS
select count(*) from jobs j, authors a
GO
3) INNER JOIN
CREATE PROCEDURE sp_InnerJoin
AS
Select TOP 5 c.categoryName, p.productName
from Categories c Inner Join products p
on c.categoryId = p.categoryId
GO
4) RIGHT OUTER JOIN
CREATE PROCEDURE sp_RightOuterJoin
AS
Select TOP 5 c.categoryName, p.productName
from Categories c Right Outer Join products p
on c.categoryId = p.categoryId
GO
5) LEFT OUTER JOIN
CREATE PROCEDURE sp_LeftOuterJoin
AS
Select TOP 5 c.categoryName, p.productName
from Categories c LEft Outer Join products p
on c.categoryId = p.categoryId
Note:- The following example will call the above procedures to show the data using sql joins.
Example: Demonstrate SQL Joins
JoinsWithNET.aspx
<% @ Page Language ="C#" AutoEventWireup ="true" CodeFile ="JoinsWithNET.aspx.cs" Inherits ="JoinsWithNET" %>
<! DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns ="http://www.w3.org/1999/xhtml" >
<head runat ="server">
</head >
<body >
<form id ="form1" runat ="server">
<div >
<strong ><span style ="text-decoration: underline"> SQL Joins and .Net < br />
<br />
</span>
<asp:Button ID ="txtSelf" runat ="server" OnClick ="txtSelf_Click" Text ="Self Join" />< span style ="text-decoration: underline"> </ span >
<asp:GridView ID ="grdvSelf" runat ="server" AutoGenerateColumns ="False" BackColor ="LightGoldenrodYellow" BorderColor ="Tan" BorderWidth ="1px" CellPadding ="2" Font-Bold ="False" ForeColor ="Black" GridLines ="None">
<FooterStyle BackColor ="Tan" />
<Columns >
< asp : BoundField DataField ="au_lname" HeaderText ="Last Name" />
< asp : BoundField DataField ="au_fname" HeaderText ="First Name" />
< asp : BoundField DataField ="city" HeaderText ="City" />
</Columns>
<SelectedRowStyle BackColor ="DarkSlateBlue" ForeColor ="GhostWhite" />
<PagerStyle BackColor ="PaleGoldenrod" ForeColor ="DarkSlateBlue" HorizontalAlign ="Center" />
<HeaderStyle BackColor ="Tan" Font-Bold ="True" />
<AlternatingRowStyle BackColor ="PaleGoldenrod" />
</asp:GridView>
<br/>
</strong>
<asp:Button ID ="btnCartesian" runat ="server" OnClick ="btnCartesian_Click" Text ="Cartesian Join" />
<asp:Label ID ="lblCartesian" runat ="server"></ asp : Label >
<br />
<br />
<asp:Button ID ="btnInner" runat ="server" OnClick ="btnInner_Click" Text ="Inner Join" />
<asp:GridView ID ="grdvInner" runat ="server" AutoGenerateColumns ="False" BackColor ="LightGoldenrodYellow" BorderColor ="Tan" BorderWidth ="1px" CellPadding ="2" ForeColor ="Black" GridLines ="None">
<FooterStyle BackColor ="Tan" />
<Columns >
<asp:BoundField DataField ="categoryName" HeaderText ="Category Name" />
<asp:BoundField DataField ="productName" HeaderText ="Product Name" />
</Columns>
<SelectedRowStyle BackColor ="DarkSlateBlue" ForeColor ="GhostWhite" />
<PagerStyle BackColor ="PaleGoldenrod" ForeColor ="DarkSlateBlue" HorizontalAlign ="Center" />
<HeaderStyle BackColor ="Tan" Font-Bold ="True" />
<AlternatingRowStyle BackColor ="PaleGoldenrod" />
</asp:GridView >
<br />
<asp:Button ID ="btnRight" runat ="server" OnClick ="btnRight_Click" Text ="Right Outer Join" />
<asp:GridView ID ="grdvRight" runat ="server" AutoGenerateColumns ="False" BackColor ="LightGoldenrodYellow" BorderColor ="Tan" BorderWidth ="1px" CellPadding ="2" ForeColor ="Black" GridLines ="None">
<FooterStyle BackColor ="Tan" />
<Columns >
<asp:BoundField DataField ="categoryName" HeaderText ="Category Name" />
<asp:BoundField DataField ="productName" HeaderText ="Product Name" />
</Columns>
<SelectedRowStyle BackColor ="DarkSlateBlue" ForeColor ="GhostWhite" />
<PagerStyle BackColor ="PaleGoldenrod" ForeColor ="DarkSlateBlue" HorizontalAlign ="Center" />
<HeaderStyle BackColor ="Tan" Font-Bold ="True" />
<AlternatingRowStyle BackColor ="PaleGoldenrod" />
</asp:GridView>
<br/> <asp:Button ID ="btnLeft" runat ="server" OnClick ="btnLeft_Click" Text ="Left Outer Join" />
<asp:GridView ID ="grdvLeft" runat ="server" AutoGenerateColumns ="False" BackColor ="LightGoldenrodYellow" BorderColor ="Tan" BorderWidth ="1px" CellPadding ="2" ForeColor ="Black" GridLines ="None">
<FooterStyle BackColor ="Tan" />
<Columns>
<asp:BoundField DataField ="categoryName" HeaderText ="Category Name" />
<asp:BoundField DataField ="productname" HeaderText ="Product Name" />
</Columns>
<SelectedRowStyle BackColor ="DarkSlateBlue" ForeColor ="GhostWhite" />
<PagerStyle BackColor ="PaleGoldenrod" ForeColor ="DarkSlateBlue" HorizontalAlign ="Center" />
<HeaderStyle BackColor ="Tan" Font-Bold ="True" />
<AlternatingRowStyle BackColor ="PaleGoldenrod" />
</asp:GridView>
</div>
</form>
</body >
</html >
JoinsWithNET.aspx.cs
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
public partial class JoinsWithNET : System.Web.UI. Page
{
protected void Page_Load( object sender, EventArgs e)
{
}
protected void txtSelf_Click( object sender, EventArgs e)
{
string strConn = "Data Source=localhost;Initial Catalog= pubs; user Id=sa;Password = test" ;
SqlConnection conn = new SqlConnection (strConn);
SqlDataAdapter ada = new SqlDataAdapter ( "sp_selfjoin" , conn);
DataTable dtSelf = new DataTable ();
ada.Fill(dtSelf);
grdvSelf.DataSource = dtSelf.DefaultView;
grdvSelf.DataBind();
}
protected void btnInner_Click( object sender, EventArgs e)
{
string strConn = "Data Source=localhost;Initial Catalog= Northwind; user Id=sa;Password = test" ;
SqlConnection conn = new SqlConnection (strConn);
SqlDataAdapter ada = new SqlDataAdapter ( "sp_InnerJoin" , conn);
DataTable dtInner = new DataTable ();
ada.Fill(dtInner);
grdvInner.DataSource = dtInner.DefaultView;
grdvInner.DataBind();
}
protected void btnRight_Click( object sender, EventArgs e)
{
string strConn = "Data Source=localhost;Initial Catalog= Northwind; user Id=sa;Password =test" ;
SqlConnection conn = new SqlConnection (strConn);
SqlDataAdapter ada = new SqlDataAdapter ( "sp_RightOuterJoin" , conn);
DataTable dtRight = new DataTable ();
ada.Fill(dtRight);
grdvRight.DataSource = dtRight.DefaultView;
grdvRight.DataBind();
}
protected void btnLeft_Click( object sender, EventArgs e)
{
string strConn = "Data Source=localhost;Initial Catalog= Northwind; user Id=sa;Password =test" ;
SqlConnection conn = new SqlConnection (strConn);
SqlDataAdapter ada = new SqlDataAdapter ( "sp_LeftOuterJoin" , conn);
DataTable dtLeft = new DataTable ();
ada.Fill(dtLeft);
grdvLeft.DataSource = dtLeft.DefaultView;
grdvLeft.DataBind();
}
protected void btnCartesian_Click( object sender, EventArgs e)
{ //Cartesian Join
string strConn = "Data Source=localhost;Initial Catalog= pubs; user Id=sa;Password =test" ;
SqlConnection conn = new SqlConnection (strConn);
SqlCommand cmd = new SqlCommand ();
cmd.Connection = conn;
cmd.CommandType = CommandType .StoredProcedure;
cmd.CommandText = "sp_cartesianJoin" ;
conn.Open();
lblCartesian.Text = "Cartesian Product is :: " + cmd.ExecuteScalar().ToString() ;
conn.Close();
}
}
Output
|
Total Members
Delicious
Digg
Technorati
Blink
Furl
Reddit
Newsvine
Google
