CREATE TABLE "dbo"."EMPLOYEES" (
"id" "int" IDENTITY (1, 1) NOT NULL ,
"emp_number" "int" NOT NULL ,
"full_name" "varchar" (50) NOT NULL
) ON "PRIMARY"
GO
CREATE TABLE "dbo"."EMPLOYEE_COURSES" (
"id" "int" IDENTITY (1, 1) NOT NULL ,
"employee_id" "int" NOT NULL ,
"course" "varchar" (30) NOT NULL
) ON "PRIMARY"
GO
CREATE TABLE "dbo"."EMPLOYEE_OFFICES" (
"id" "int" identity(1, 1) NOT NULL
, "employee_id" "int" NOT NULL
, "office_location" "varchar"(10) NOT NULL
, "building" "varchar"(10) NOT NULL
)
GO
ASPX Inline Code: |
<%@ OutputCache Duration="4000" VaryByParam="*"%>
<%@ Page language="C#" Trace="false" debug="false" Inherits="exampleJoins" Src="default.aspx.cs" strict="false" %>
<%@ Register TagPrefix="Dlp" TagName="CodeViewer" Src="~/templates/CodeViewer/codeViewer.ascx" %>
<html>
<head>
<title>Join Examples</title>
<link rel="stylesheet" href="/Templates/style.css" type="text/css" />
</head>
<body>
<h2>Join Examples</h2>
<a href="/testsite/">Back to Test Home</a>
<br />
<form runat="server">
Click on a button to view the detail about the specific query type
<br />
<asp:button id="ShowInnerJoin" text="Inner Join" onClick="ShowInnerJoin_Click" runat="server"
/><asp:button id="ShowLeftJoin" text="Left Join" onClick="ShowLeftJoin_Click" runat="server"
/><asp:button id="ShowRightJoin" text="Right Join" onClick="ShowRightJoin_Click" runat="server"
/><asp:button id="ShowFullJoin" text="Full Join" onClick="ShowFullJoin_Click" runat="server"
/><asp:button id="ShowCrossJoin" text="Cross Join" onClick="ShowCrossJoin_Click" runat="server"
/><br /><asp:button id="ShowTripleJoinAll" text="Triple Join" onClick="ShowTripleJoin_Click" runat="server"
/><asp:button id="ShowSelfJoin" text="Self Join" onClick="ShowSelfJoin_Click" runat="server"
/><asp:button id="ShowUnion" text="UNION" onClick="ShowUnion_Click" runat="server"
/><asp:button id="ShowUnionAll" text="UNION ALL" onClick="ShowUnionAll_Click" runat="server"
/>
</form>
<div align="left" id="innerJoinData" runat="server">
When you are trying to make a query that is a LEFT, RIGHT, or FULL OUTER
JOIN the best way to approach it is from an INNER JOIN standpoint. Let's
take two (and later three) table's structure and execute several joins on them. The goal will
be to get a listing of all employees and the course they are taking, showing
the employees that are not taking courses and/or the courses which have no employees.
<br />
Inner Join - shows only employess which have courses
<pre id="l1" runat="server" />
<ASP:DataGrid id="dg1" runat="server"
Width="100%"
BackColor="white"
BorderColor="black"
ShowFooter="false"
CellPadding=3
CellSpacing="0"
Font-Name="Verdana"
Font-Size="8pt"
Headerstyle-BackColor="lightblue"
Headerstyle-Font-Size="10pt"
Headerstyle-Font-Style="bold"
MaintainState="false"
/>
</div>
<div align="left" id="leftJoinData" runat="server">
Left Join - shows all employees regardless if they have a course
<pre id="l2" runat="server" />
<ASP:DataGrid id="dg2" runat="server"
Width="100%"
BackColor="white"
BorderColor="black"
ShowFooter="false"
CellPadding=3
CellSpacing="0"
Font-Name="Verdana"
Font-Size="8pt"
Headerstyle-BackColor="lightblue"
Headerstyle-Font-Size="10pt"
Headerstyle-Font-Style="bold"
MaintainState="false"
/>
</div>
<div align="left" id="rightJoinData" runat="server">
Right Join - shows all courses and employees which have courses assigned
<pre id="l3" runat="server" />
<ASP:DataGrid id="dg3" runat="server"
Width="100%"
BackColor="white"
BorderColor="black"
ShowFooter="false"
CellPadding=3
CellSpacing="0"
Font-Name="Verdana"
Font-Size="8pt"
Headerstyle-BackColor="lightblue"
Headerstyle-Font-Size="10pt"
Headerstyle-Font-Style="bold"
MaintainState="false"
/>
</div>
<div align="left" id="fullJoinData" runat="server">
Full Outer Join - shows all courses and all employees that are related either from one table or the other. A Cartesian Join (or Cartesian product/Cross product) is not the same thing as a Full Join. Note this is *basically* a UNION of the LEFT join and the RIGHT join.
<pre id="l4" runat="server" />
<ASP:DataGrid id="dg4" runat="server"
Width="100%"
BackColor="white"
BorderColor="black"
ShowFooter="false"
CellPadding=3
CellSpacing="0"
Font-Name="Verdana"
Font-Size="8pt"
Headerstyle-BackColor="lightblue"
Headerstyle-Font-Size="10pt"
Headerstyle-Font-Style="bold"
MaintainState="false"
/>
</div>
<div align="left" id="crossJoinData" runat="server">
Cross Join - shows all courses and all employees regardless of their relation. Referred to as a Cartesian Join (or Cartesian product/Cross product). Note that there is no removal of duplicates. Think of this like so: Table A has 3 rows; Table B has 4 rows; CROSS JOIN has (3 x 4) = 12 rows.
<pre id="l7" runat="server" />
<ASP:DataGrid id="dg7" runat="server"
Width="100%"
BackColor="white"
BorderColor="black"
ShowFooter="false"
CellPadding=3
CellSpacing="0"
Font-Name="Verdana"
Font-Size="8pt"
Headerstyle-BackColor="lightblue"
Headerstyle-Font-Size="10pt"
Headerstyle-Font-Style="bold"
MaintainState="false"
/>
</div>
<div align="left" id="unionData" runat="server">
Same as a FULL JOIN but using UNION. Note that it is sorted different. This is because UNION forces a sort; the rows as a whole are not duplicated.
<pre id="l5" runat="server" />
<ASP:DataGrid id="dg5" runat="server"
Width="100%"
BackColor="white"
BorderColor="black"
ShowFooter="false"
CellPadding=3
CellSpacing="0"
Font-Name="Verdana"
Font-Size="8pt"
Headerstyle-BackColor="lightblue"
Headerstyle-Font-Size="10pt"
Headerstyle-Font-Style="bold"
MaintainState="false"
/>
<br />
<div align="left">If you split the SQL statement and say the 1st query rows are represented by "A" and
the 2nd query rows are represented by "B", then "C" represents the rows that are the same between both
(all) queries. Rows represented:
<b>
<br />"A" = 450 and 654
<br />"B" = no emp number "NO EMPLOYEE IN THIS CLASS!"
<br />"C" = 43215(Math), 43215(English), 8787
</b>
<br />The records in "C" are in both queries, but are only shown once in the UNION example.
<br /><br />
A graphical representation:</div>
<img src="union.jpg" border="0"/>
</div>
<div align="left" id="unionAllData" runat="server">
Same query as the UNION but using UNION ALL instead. Note the duplication and the difference in the sorting.
<pre id="l6" runat="server" />
<ASP:DataGrid id="dg6" runat="server"
Width="100%"
BackColor="white"
BorderColor="black"
ShowFooter="false"
CellPadding=3
CellSpacing="0"
Font-Name="Verdana"
Font-Size="8pt"
Headerstyle-BackColor="lightblue"
Headerstyle-Font-Size="10pt"
Headerstyle-Font-Style="bold"
MaintainState="false"
/>
<br />
<div align="left">If you split the SQL statement and say the 1st query rows are represented by "A" and
the 2nd query rows are represented by "B", then "C" represents the rows that are the same between both
(all) queries. Since there are two separate queries, then there are two (2) records for each record in
either representation of "C". Rows represented:
<b>
<br />"A" = 450 and 654
<br />  "C" = 43215(Math), 43215(English), 8787
<br />"B" = no emp number "NO EMPLOYEE IN THIS CLASS!"
<br />  "C" = 43215(Math), 43215(English), 8787
</b>
<br />The records in "C" are in both queries, and are shown as many times as available.
<br /><br />
A graphical representation:</div>
<img src="unionall.jpg" border="0"/>
</div>
<div align="left" id="tripleJoinData" runat="server">
I added a table called EMPLOYEE_OFFICES which is a listing of the office numbers and buildings they are located in. This query finds out the number of courses an employee has taken and the number of offices the employee has assigned.
<pre id="l8" runat="server" />
<ASP:DataGrid id="dg8" runat="server"
Width="100%"
BackColor="white"
BorderColor="black"
ShowFooter="false"
CellPadding=3
CellSpacing="0"
Font-Name="Verdana"
Font-Size="8pt"
Headerstyle-BackColor="lightblue"
Headerstyle-Font-Size="10pt"
Headerstyle-Font-Style="bold"
MaintainState="false"
/>
</div>
<div align="left" id="selfJoinData" runat="server">
This query shows all EMPLOYEE_OFFICES and how many the employee has in his/her possesion. It utilizes an INNER JOIN on itself, or a SELF JOIN
<pre id="l9" runat="server" />
<ASP:DataGrid id="dg9" runat="server"
Width="100%"
BackColor="white"
BorderColor="black"
ShowFooter="false"
CellPadding=3
CellSpacing="0"
Font-Name="Verdana"
Font-Size="8pt"
Headerstyle-BackColor="lightblue"
Headerstyle-Font-Size="10pt"
Headerstyle-Font-Style="bold"
MaintainState="false"
/>
</div>
<div align="left">
<br />
<br />
The tables used in these examples are defined as follows:
<br />
<pre>
<b>CREATE TABLE</b> "dbo"."EMPLOYEES" (
"id" "int" IDENTITY (1, 1) NOT NULL ,
"emp_number" "int" NOT NULL ,
"full_name" "varchar" (50) NOT NULL
) ON "PRIMARY"
GO
<b>CREATE TABLE</b> "dbo"."EMPLOYEE_COURSES" (
"id" "int" IDENTITY (1, 1) NOT NULL ,
"employee_id" "int" NOT NULL ,
"course" "varchar" (30) NOT NULL
) ON "PRIMARY"
GO
<b>CREATE TABLE</b> "dbo"."EMPLOYEE_OFFICES" (
"id" "int" identity(1, 1) NOT NULL
, "employee_id" "int" NOT NULL
, "office_location" "varchar"(10) NOT NULL
, "building" "varchar"(10) NOT NULL
)
GO
</pre>
</div>
<Dlp:CodeViewer CodeFileName="default.aspx" CodeFileType="Inline" runat="server" />
<Dlp:CodeViewer CodeFileName="default.aspx.cs" CodeFileType="CodeBehind" runat="server" />
</body>
</html>
ASPX CodeBehind: |
using System;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Configuration;
public class exampleJoins : System.Web.UI.Page
{
protected Button ShowInnerJoin;
protected Button ShowLeftJoin;
protected Button ShowRightJoin;
protected Button ShowFullJoin;
protected Button ShowCrossJoin;
protected Button ShowUnion;
protected Button ShowUnionAll;
protected Button ShowTripleJoinAll;
protected Button ShowSelfJoin;
protected HtmlGenericControl innerJoinData;
protected HtmlGenericControl leftJoinData;
protected HtmlGenericControl rightJoinData;
protected HtmlGenericControl fullJoinData;
protected HtmlGenericControl crossJoinData;
protected HtmlGenericControl unionData;
protected HtmlGenericControl unionAllData;
protected HtmlGenericControl tripleJoinData;
protected HtmlGenericControl selfJoinData;
protected DataGrid dg1;
protected DataGrid dg2;
protected DataGrid dg3;
protected DataGrid dg4;
protected DataGrid dg5;
protected DataGrid dg6;
protected DataGrid dg7;
protected DataGrid dg8;
protected DataGrid dg9;
protected HtmlGenericControl l1;
protected HtmlGenericControl l2;
protected HtmlGenericControl l3;
protected HtmlGenericControl l4;
protected HtmlGenericControl l5;
protected HtmlGenericControl l6;
protected HtmlGenericControl l7;
protected HtmlGenericControl l8;
protected HtmlGenericControl l9;
protected void Page_Load(System.Object Src, System.EventArgs E)
{
System.String connectionString = ConfigurationSettings.AppSettings["connectionString"];
System.String strSql;
System.String strfjSql;
System.String sql = "{0} a.\"emp_number\" \"Employee Number\", a.\"full_name\" \"Full Name\", b.\"course\" \"Course Enrolled\"\n" +
"{1} \"dbo\".\"EMPLOYEES\" a\n{2} \"dbo\".\"EMPLOYEE_COURSES\" b {3} a.\"id\" = b.\"employee_id\"";
SqlConnection conn = new SqlConnection(connectionString);
if (!Page.IsPostBack) makePartVisible(0); //show no divs
strSql = System.String.Format(sql, "SELECT", "FROM", "INNER JOIN", "ON");
l1.InnerHtml = System.String.Format(sql, "<b>SELECT</b>", "<b>FROM</b>", "<b>INNER JOIN</b>", "<b>ON</b>");
bindToDataGrid(dg1, conn, strSql);
strSql = System.String.Format(sql, "SELECT", "FROM", "LEFT JOIN", "ON");
strfjSql = strSql;
l2.InnerHtml = System.String.Format(sql, "<b>SELECT</b>", "<b>FROM</b>", "<b>LEFT JOIN</b>", "<b>ON</b>");
bindToDataGrid(dg2, conn, strSql);
strSql = System.String.Format(sql, "SELECT", "FROM", "RIGHT JOIN", "ON");
strfjSql = strfjSql + " UNION " + strSql;
l3.InnerHtml = System.String.Format(sql, "<b>SELECT</b>", "<b>FROM</b>", "<b>RIGHT JOIN</b>", "<b>ON</b>");
bindToDataGrid(dg3, conn, strSql);
strSql = System.String.Format(sql, "SELECT", "FROM", "FULL OUTER JOIN", "ON");
l4.InnerHtml = System.String.Format(sql, "<b>SELECT</b>", "<b>FROM</b>", "<b>FULL OUTER JOIN</b>", "<b>ON</b>");
bindToDataGrid(dg4, conn, strSql);
strSql = System.String.Format(sql.Replace(" {3} a.\"id\" = b.\"employee_id\"", ""), "SELECT", "FROM", "CROSS JOIN");
l7.InnerHtml = System.String.Format(sql.Replace(" {3} a.\"id\" = b.\"employee_id\"", ""), "<b>SELECT</b>", "<b>FROM</b>", "<b>CROSS JOIN</b>");
bindToDataGrid(dg7, conn, strSql);
l5.InnerHtml = l2.InnerHtml + "\n<b>UNION</b>\n" + l3.InnerHtml;
bindToDataGrid(dg5, conn, strfjSql);
l6.InnerHtml = l2.InnerHtml + "\n<b>UNION ALL</b>\n" + l3.InnerHtml;
strfjSql = strfjSql.Replace("UNION", "UNION ALL");
bindToDataGrid(dg6, conn, strfjSql);
sql = "{0} a.\"emp_number\", a.\"full_name\"\n\t, {1}({2} b.\"course\") \"# of courses taken\"\n\t, {1}({2} c.\"office_location\") \"# of Offices\" \n{3} \"dbo\".\"EMPLOYEES\" a \n{4} \"dbo\".\"EMPLOYEE_COURSES\" b {5} a.\"id\" = b.\"employee_id\" \n{6} \"dbo\".\"EMPLOYEE_OFFICES\" c {5} a.\"id\" = c.\"employee_id\" \n{7} a.\"emp_number\", a.\"full_name\"";
strSql = System.String.Format(sql, "SELECT", "COUNT", "DISTINCT", "FROM", "LEFT OUTER JOIN", "ON", "INNER JOIN", "GROUP BY");
l8.InnerHtml = System.String.Format(sql, "<b>SELECT</b>", "<b>COUNT</b>", "<b>DISTINCT</b>", "<b>FROM</b>", "<b>LEFT OUTER JOIN</b>", "<b>ON</b>", "<b>INNER JOIN</b>", "<b>GROUP BY</b>");
bindToDataGrid(dg8, conn, strSql);
strSql = "{0} a.\"id\", a.\"employee_id\", a.\"office_location\", a.\"building\"\n\t, {1}(*) \"Total Employee Offices\"\n{2} \"dbo\".\"EMPLOYEE_OFFICES\" a\n{3} \"dbo\".\"EMPLOYEE_OFFICES\" b {4} a.\"employee_id\" = b.\"employee_id\"\n{5} a.\"id\", a.\"employee_id\", a.\"office_location\", a.\"building\"";
l9.InnerHtml = System.String.Format(strSql, "<b>SELECT</b>", "<b>COUNT</b>", "<b>FROM</b>", "<b>INNER JOIN</b>", "<b>ON</b>", "<b>GROUP BY</b>");
bindToDataGrid(dg9, conn, System.String.Format(strSql, "SELECT", "COUNT", "FROM", "INNER JOIN", "ON", "GROUP BY"));
conn.Close();
conn = null;
}
static void bindToDataGrid(DataGrid dg, SqlConnection conn, System.String strSql)
{
if (conn.State == System.Data.ConnectionState.Closed) conn.Open();
SqlCommand cmd = new SqlCommand(strSql, conn);
SqlDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
dg.DataSource = dr;
dg.DataBind();
dr.Close();
cmd = null;
}
private void makePartVisible(System.Int32 divToShow)
{
innerJoinData.Visible = (divToShow == 1);
leftJoinData.Visible = (divToShow == 2);
rightJoinData.Visible = (divToShow == 3);
fullJoinData.Visible = (divToShow == 4);
crossJoinData.Visible = (divToShow == 7);
unionData.Visible = (divToShow == 5);
unionAllData.Visible = (divToShow == 6);
tripleJoinData.Visible = (divToShow == 8);
selfJoinData.Visible = (divToShow == 9);
}
protected void ShowInnerJoin_Click(System.Object Sender, System.EventArgs e)
{ makePartVisible(1);
}
protected void ShowLeftJoin_Click(System.Object Sender, System.EventArgs e)
{ makePartVisible(2);
}
protected void ShowRightJoin_Click(System.Object Sender, System.EventArgs e)
{ makePartVisible(3);
}
protected void ShowFullJoin_Click(System.Object Sender, System.EventArgs e)
{ makePartVisible(4);
}
protected void ShowCrossJoin_Click(System.Object Sender, System.EventArgs e)
{ makePartVisible(7);
}
protected void ShowUnion_Click(System.Object Sender, System.EventArgs e)
{ makePartVisible(5);
}
protected void ShowUnionAll_Click(System.Object Sender, System.EventArgs e)
{ makePartVisible(6);
}
protected void ShowTripleJoin_Click(System.Object Sender, System.EventArgs e)
{ makePartVisible(8);
}
protected void ShowSelfJoin_Click(System.Object Sender, System.EventArgs e)
{ makePartVisible(9);
}
} //exampleJoins