Ajax Cascading DropDownList With GridView

Introduction

Hi every one today i am going to write an article on how to use Ajax cascading drop down list with in the grid using a web services.Cascading drop down list help us to save the post-back of a page and Each time ,When the selection of parent Drop-Down List controls changes,It makes a call to a specified web service to retrieve the list of values for the next Drop-Down List in the set.

Earlier we used the Ajax and java-script to achieve this kind of tasks , But now the same functionality is available in Ajax control-toolkit control named as Cascading Drop-down control. This article covers the following points:

• Ajax Cascading drop-down list binding inside the grid.
• How to set the properties of cascading control.
• After the page load.How to rebind it.

Background  

You can also follow this Cascading-Dropdownlist  to know the basic feature and the basic properties of this control.Some of the feature list given below:

• TargetControlID - The ID of the Drop-Down List to populate.
• Category - The name of the category this Drop-Down List represents.
• PromptText - Optional text to display before the user has selected a value from the DropDownList.
• PromptValue - Optional value set when Prompt-text is displayed.
• EmptyText - Optional text to display when the Drop-Down List has no data to display.
• EmptyValue - Optional value set when Empty-text is displayed.
• LoadingText - Optional text to display while the data for the Drop-Down List is being loaded.
• ServicePath - Path to a web service that returns the data used to populate the Drop-DownList. This property should be left null if ServiceMethod refers to a page method. The web service should be decorated with the System.Web.Script.Services.ScriptService attribute.
• ServiceMethod - Web service method that returns the data used to populate the DropDownList.
• ContextKey - User/page specific context provided to an optional overload of the web method described by ServiceMethod/ServicePath. If the context key is used, it should have the same signature with an additional parameter named contextKey of type string.
• UseContextKey - Whether or not the ContextKey property should be used. This will be automatically enabled if the ContextKey property is ever set (on either the client or the server).
• ParentControlID - Optional ID of the parent DropDownList that controls the contents of this DropDownList.
• SelectedValue - Optional value to select by default. This needs to exactly match the string representation of a value in the DropDownList.  

Using the code 

I am going to create a grid view control on my web page.I have placed two drop-downlist and two cascading control inside my grid.  

Client Side Code:

 Collapse | Copy Code

<asp:GridView GridLines="Vertical" ID="grdDemo" runat="server" AutoGenerateColumns="False"
                    BackColor="White" BorderColor="#DEDFDE" BorderStyle="None" BorderWidth="1px"
                    CellPadding="4" ForeColor="Black" OnRowDataBound="grdDemo_RowDataBound" OnRowDeleting="grdDemo_RowDeleting">
                    <EmptyDataTemplate>
                        <div>
                            No record found</div>
                    </EmptyDataTemplate>
                    <AlternatingRowStyle BackColor="White" />
                    <Columns>
                        <asp:TemplateField HeaderText="Brand Name">
                            <ItemTemplate>
                                <asp:DropDownList ID="ddlBrand" runat="server" Font-Size="9" Width="130">
                                </asp:DropDownList>
                                <ajaxToolKit:CascadingDropDown UseContextKey="true" ContextKey='<%# Bind("BrandId") %>'
                                    ID="ccdRegion" runat="server" Category="BrandName" TargetControlID="ddlBrand"
                                    ServiceMethod="GetAllCarBrand" ServicePath="~/WebService/CarService.asmx">
                                </ajaxToolKit:CascadingDropDown>
                            </ItemTemplate>
                        </asp:TemplateField>
                        <asp:TemplateField HeaderText="Model Name">
                            <ItemTemplate>
                                <asp:DropDownList ID="ddlModelName" runat="server" Font-Size="9" Width="130">
                                </asp:DropDownList>
                                <ajaxToolKit:CascadingDropDown UseContextKey="true" ContextKey='<%# Bind("ModelId") %>'
                                    ID="ccdDistrict" runat="server" Category="Model" ParentControlID="ddlBrand" TargetControlID="ddlModelName"
                                    ServiceMethod="GetAllModelByBrand" ServicePath="~/WebService/CarService.asmx">
                                </ajaxToolKit:CascadingDropDown>
                            </ItemTemplate>
                        </asp:TemplateField>
                        <asp:TemplateField>
                            <ItemTemplate>
                                <asp:Button ID="BtnAdd" Text="Add" runat="server" OnClick="BtnAdd_Click" />
                                <asp:Button ID="BtnDelete" Visible="false" Text="Delete" CommandName="Delete" runat="server" />
                            </ItemTemplate>
                        </asp:TemplateField>
                    </Columns>
                    <FooterStyle BackColor="#CCCC99" />
                    <HeaderStyle BackColor="#6B696B" Font-Bold="True" ForeColor="White" />
                    <PagerStyle BackColor="#F7F7DE" ForeColor="Black" HorizontalAlign="Right" />
                    <RowStyle BackColor="#F7F7DE" />
                    <SelectedRowStyle BackColor="#CE5D5A" Font-Bold="True" ForeColor="White" />
                    <SortedAscendingCellStyle BackColor="#FBFBF2" />
                    <SortedAscendingHeaderStyle BackColor="#848384" />
                    <SortedDescendingCellStyle BackColor="#EAEAD3" />
                    <SortedDescendingHeaderStyle BackColor="#575357" />
                </asp:GridView>

After Creating the grid on client-side page.I am going to create a web service named as CarService.asmx . Inside this web service ,I have created two method to bind those drop-down list  control named asGetAllCarBrand and GetAllModelByBrand.

Those methods returns the array of CascadingDropDownName Value class provided by Ajax Control-toolkit Library. The list of car brand name and on the basis of those brand it will return the car model name , Like Maruti is a brand name and swift is a car model.I have used constant value to fill my list.In a web service method three parameters should be defined.

• Category : The name of the category that represents the dropdownlist.
• KnownCategoryValues: This parameter will return a string containing the currently selected category values, as well as the category to retrieve values for.So that we can easily find out the value of parent dropdownlist.
• Context key:It is used to bind the selected value of that DropdownList.You can use this context key to rebind the already selected value of dropdownlist.      

 Web Service code is given below: 

 Collapse | Copy Code

    /// <summary>
    /// Summary description for CarService
    /// </summary>
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]
    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
     [System.Web.Script.Services.ScriptService]
    public class <var> CarService </var>: System.Web.Services.WebService
    {
        #region Public Member Declare Here
        private List<CascadingDropDownNameValue> ListCascading;
        #endregion

        /// <summary>
        /// Gets all car brand.
        /// </summary>
        /// <param name="knownCategoryValues">The known category values.</param>
        /// <param name="category">The category.</param>
        /// <param name="contextKey">The context key.</param>
        /// <returns></returns>
        [WebMethod]
        public CascadingDropDownNameValue[] GetAllCarBrand(string knownCategoryValues, string category, string contextKey)
        {
            try
            {                
                ListCascading = new List<CascadingDropDownNameValue>();
                List<CarBrand> LstBrand = new List<CarBrand>();
                LstBrand.Add(new CarBrand() { BrandId = 1, BrandName = "BMW" });
                LstBrand.Add(new CarBrand() { BrandId = 2, BrandName = "Maruti" });
                LstBrand.Add(new CarBrand() { BrandId = 3, BrandName = "Audi" });
                LstBrand.Add(new CarBrand() { BrandId = 4, BrandName = "Ford" });

                foreach (var item in LstBrand)
                {
                    string brandId = item.BrandId.ToString();
                    string brandName = item.BrandName;
                    ListCascading.Add(new CascadingDropDownNameValue(brandName, brandId));
                }
                CascadingDropDownNameValue selectedVal = (from x in ListCascading where x.value == contextKey select x).FirstOrDefault();
                if (selectedVal != null)
                    selectedVal.isDefaultValue = true;
                return ListCascading.ToArray();
            }
            catch (SoapException)
            {
                throw;
            }
            catch (Exception)
            {

                throw;
            }
        }

        /// <summary>
        /// Gets all model by brand.
        /// </summary>
        /// <param name="knownCategoryValues">The known category values.</param>
        /// <param name="category">The category.</param>
        /// <param name="contextKey">The context key.</param>
        /// <returns></returns>
        [WebMethod]
        public CascadingDropDownNameValue[] GetAllModelByBrand(string knownCategoryValues, string category, string contextKey)
        {
            try
            {
                ListCascading = new List<CascadingDropDownNameValue>();
                //Find the selected value of brand
                StringDictionary brandDetails = AjaxControlToolkit.CascadingDropDown.ParseKnownCategoryValuesString(knownCategoryValues);
                var brandId = Convert.ToInt64(brandDetails["BrandName"]);
                var LstOfCarModel = GetListOfModel().Where(u=>u.BrandId == brandId) ;

                foreach (var item in LstOfCarModel)
                {
                    string modelId = item.ModelId.ToString();
                    string modelName = item.CarName;
                    ListCascading.Add(new CascadingDropDownNameValue(modelName, modelId));
                }

                //Select the Selected value of Dropdown list.
                CascadingDropDownNameValue selectedVal = (from x in ListCascading where x.value == contextKey select x).FirstOrDefault();
                if (selectedVal != null)
                    selectedVal.isDefaultValue = true;


                return ListCascading.ToArray();
            }
            catch (SoapException)
            {
                throw;
            }
            catch (Exception)
            {

                throw;
            }
        }
  

GetAllCarBrand return the list of mulitiple car brands and method name GetAllModelByBrand return the cars model on the basis of their brand.

After creating the web service set properties of your's cascading drop-down list inside a grid.I have done those things in my client code. 

The CascadingDropDown class has a helper method for unpacking the category values:  

 Collapse | Copy Code

StringDictionary brandDetails = AjaxControlToolkit.CascadingDropDown.ParseKnownCategoryValuesString(knownCategoryValues); 

This method will return a StringDictionary containing the name/value pairs of the currently selected values.

Here is my C# Class ,This class contains a method for bind a grid from a list of cars model named as CarList.I have also used grdDemo_RowDeleting event to delete the grid row and grdDemo_RowDataBound event to maintain the add and delete in very row of gridview control.

 Collapse | Copy Code

 public partial class Default : System.Web.UI.Page
    {
        #region Define Member Variable Here
         static List<CarList> Staticlist;
        #endregion

        /// <summary>
        /// Handles the Load event of the Page control.
        /// </summary>
        /// <param name="sender">The source of the event.</param>
        /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                BindGrid();
            }
        }

        /// <summary>
        /// Binds the grid.
        /// </summary>
        private void BindGrid()
        {
            if (Staticlist == null)
            {
                Staticlist = new List<CarList>();
                Staticlist.Add(new CarList());
            }
           

            grdDemo.DataSource = Staticlist;
            grdDemo.DataBind();
        }

        /// <summary>
        /// Handles the Click event of the BtnAdd control.
        /// </summary>
        /// <param name="sender">The source of the event.</param>
        /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
        protected void BtnAdd_Click(object sender, EventArgs e)
        {
            FillExistingValue();
            Staticlist.Add(new CarList());
            BindGrid();
        }

        /// <summary>
        /// Fills the existing value.
        /// </summary>
        private void FillExistingValue()
        {
            for (int i = 0; i < grdDemo.Rows.Count; i++)
            {
                Staticlist[i].BrandId =  Convert.ToInt32(((DropDownList)grdDemo.Rows[i].FindControl("ddlBrand")).SelectedItem.Value);
                Staticlist[i].ModelId = Convert.ToInt32(((DropDownList)grdDemo.Rows[i].FindControl("ddlModelName")).SelectedItem.Value);
            }
        }

        /// <summary>
        /// Handles the RowDeleting event of the grdDemo control.
        /// </summary>
        /// <param name="sender">The source of the event.</param>
        /// <param name="e">The <see cref="System.Web.UI.WebControls.GridViewDeleteEventArgs"/> instance containing the event data.</param>
        protected void grdDemo_RowDeleting(object sender, GridViewDeleteEventArgs e)
        {
            Staticlist.RemoveAt(e.RowIndex);
            BindGrid();
        }

        /// <summary>
        /// Handles the RowDataBound event of the grdDemo control.
        /// </summary>
        /// <param name="sender">The source of the event.</param>
        /// <param name="e">The <see cref="System.Web.UI.WebControls.GridViewRowEventArgs"/> instance containing the event data.</param>
        protected void grdDemo_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            if (e.Row.RowType == DataControlRowType.DataRow)
            {
                var BtnAdd = ((Button)e.Row.FindControl("BtnAdd"));
                var BtnDel = ((Button)e.Row.FindControl("BtnDelete"));

                if (e.Row.RowIndex == Staticlist.Count - 1)
                {
                    BtnAdd.Visible = true;
                    BtnDel.Visible = false;
                }
                else
                {
                    BtnAdd.Visible = false;
                    BtnDel.Visible = true;
                }
            }
        }
    } 

I hope this article will help you guys a lot.I am going attach the whole code with this article. Final view of my demo grid is given below.

Points of Interest  

This is a very good feature provided by a Ajax Control Toolkit.First, I tried the same thing with Jquery , It's a bit diffcult to handle the client side and server side in jquery you will lost the client side values after the page postback .You have to write a lot of client side code to maintain these kinds of situation. This control minimize the code as well as provide a more flexibility to handle yours page postbacks and events like, I have in my demo grid Add and Delete buttons.

Conclusion

For more details of the source code, please download the source code from the link at the top of this article. The source code is easy and well commented.

Hope this tutorial is useful .

Stored Procedure return a data set

Stored Procedure return a data set

SqlConnection sqlConnection1 = new SqlConnection("Your Connection String");
SqlCommand cmd = new SqlCommand();
SqlDataReader reader;

cmd.CommandText = "StoredProcedureName";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = sqlConnection1;

sqlConnection1.Open();

reader = cmd.ExecuteReader();
// Data is accessible through the DataReader object here.

sqlConnection1.Close();

Difference Between Primary Key & Unique key

primary key:
*apply on only one column in single table
*not allow null values
*create clusterd index on column
Unique Key:
*apply on multiple column in single table
*allow onle one null value
*create non clusterd index on cloumn

Select from whereGroup by order by having executing order in sql

Select from whereGroup by order by having executing  order in sql


In a interview i asked the what is first execute in a sql
many people not reply any thing and some one is wrong,
 In a sql First is Selection(conditional) is execute than Projection here


Projection means choosing which columns (or expressions) the query shall return.
Selection means which rows are to be returned.

by example
Projection: what ever typed in select clause i.e, 'column list' or '*' or 'expressions' that becomes under projection.

*selection:*what type of conditions we are applying on that columns i.e, getting the records that comes under selection.

Eg: SELECT empno,ename,dno,job from Emp WHERE job='CLERK';

in the above query the columns "empno,ename,dno,job" those comes under projection, "where job='clerk'" comes under selection


here db  executed the  line by
from
where
having
order by
Group by
Select

Triggering AJAX UpdatePanel from a Button Click or DropDownList Selection Change in Code-Behind

Introduction

This is one of those problems that isn't technically difficult, like writing a word processing program, but rather is just a matter of finding the right code sequence to make it work. Much of the AJAX documentation shows how to use the controls placed in HTML markup. However, there are some situations where the programmer would prefer to add controls to a page dynamically from the code-behind (see my article Building ASP.NET Web Pages Dynamically in the Code-Behind). Using AJAX controls in this manner is not well documented. In looking for the answer, I found that other developers were posting to AJAX message boards with the same problem - particularly for the DropDownList. So here I present the solution. If you are having the same problem, I hope this is the first place you looked.

DropDownList Selection Change Sample Code

This simple example shows how to trigger an UpdatePanel update when a DropDownList selection changes. The HTML markup is just as it appears by default in Visual Studio, but with the addition of a PlaceHoldercontrol to hold the UpdatePanel and the link to the ScriptManager required for AJAX.

 Collapse | Copy Code

<%@ Page Language="C#" AutoEventWireup="true" 
    CodeFile="DropDown.aspx.cs" Inherits="AjaxTest_DropDown" %>

<!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">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <asp:ScriptManager ID="ScriptManager1" runat="server" />
    <div>
    <asp:PlaceHolder id="LocalPlaceHolder" runat="server"></asp:PlaceHolder>

    </div>
    </form>
</body>
</html>

Below, you see the code-behind file for the page. I create the UpdatePanel and add it to the placeholder. I add a Label control to the UpdatePanel. When the DropDownList selection changes, the Label displays the current time. Some important points to consider:

1. Note that both the Label and the DropDownList are added to the UpdatePanel - not to thePlaceHolder.
2. Be sure to set the DropDownList.AutoPostback to true.
3. Be sure to create a trigger for the event and add it to the UpdatePanel with the control ID of theDropDownList.
4. Pay attention to the names of the event and controls. Mistyping a name will mean the page won't work. This code is easy, but it is not forgiving. Even a minor mistake will mean that the panel does not update.

 Collapse | Copy Code

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;

public partial class AjaxTest_DropDown : System.Web.UI.Page
{
    protected Label m_TimeLabel;

    protected void Page_Load(object sender, EventArgs e)
    {
        // create an update panel
        UpdatePanel updatepanel = new UpdatePanel();

        // add the update panel to the placeholder
        LocalPlaceHolder.Controls.Add(updatepanel);

        // create a label to show time
        m_TimeLabel = new Label();
        m_TimeLabel.Text = DateTime.Now.ToString();

        // add the label to the update panel
        updatepanel.ContentTemplateContainer.Controls.Add(m_TimeLabel);

        // create a drop down list
        DropDownList dropdown = new DropDownList();
        dropdown.ID = "Dropdown1";
        dropdown.AutoPostBack = true; // this is absolutely required
        dropdown.Items.Add("Item 1");
        dropdown.Items.Add("Item 2");
        dropdown.Items.Add("Item 3");
        dropdown.Items.Add("Item 4");

        // add the drop down list to the update panel
        updatepanel.ContentTemplateContainer.Controls.Add(dropdown);

        // create a trigger
        AsyncPostBackTrigger trigger = new AsyncPostBackTrigger();

        // associate the trigger with the drop down
        trigger.ControlID = "Dropdown1";
        trigger.EventName = "SelectedIndexChanged";

        // add the trigger to the update panel
        updatepanel.Triggers.Add(trigger);
    }

    protected void Dropdown1_SelectedIndexChanged(object sender, EventArgs e)
    {
        // event to handle drop down change
        m_TimeLabel.Text = DateTime.Now.ToString();
    }

}

Below, you see a screen shot of this simple Web page.

Triggering the Update from a Button

You can also trigger the update from a Button control. This code shows how. While the markup for the page would be the same, pay attention to the differences from the DropDownList. Note how specifying the event handler for the Button is different than for the DropDownList.

 Collapse | Copy Code

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;

public partial class AjaxTest_ButtonUpdate : System.Web.UI.Page
{
    protected Label m_TimeLabel;

    protected void Page_Load(object sender, EventArgs e)
    {
        // create an update panel
        UpdatePanel updatepanel = new UpdatePanel();

        // add the update panel to the placeholder
        LocalPlaceHolder.Controls.Add(updatepanel);

        // create a label to show time
        m_TimeLabel = new Label();
        m_TimeLabel.Text = "Initial Text";

        // add the label to the update panel
        updatepanel.ContentTemplateContainer.Controls.Add(m_TimeLabel);

        // create a drop down list
        Button button = new Button();
        button.ID = "Button1";
        button.Text = "Update";
        button.Click += new EventHandler(Button1_Click);

        // add the button to the update panel
        updatepanel.ContentTemplateContainer.Controls.Add(button);

        // create a trigger
        AsyncPostBackTrigger trigger = new AsyncPostBackTrigger();

        // associate the trigger with the drop down
        trigger.ControlID = "Button1";
        trigger.EventName = "Click";

        // add the trigger to the update panel
        updatepanel.Triggers.Add(trigger);
    }

    protected void Button1_Click(object sender, EventArgs e)
    {
        // event to handle drop down change
        m_TimeLabel.Text = DateTime.Now.ToString();
    }
}

Conclusion

That is all there is to it. Although a bit difficult to figure out initially, it is simple to implement once you know what to do.

SQL SERVER – Definition, Comparison and Difference between HAVING and WHERE Clause

In recent interview sessions in hiring process I asked this question to every prospect who said they know basic SQL. Surprisingly, none answered me correct. They knew lots of things in details but not this simple one. One prospect said he does not know cause it is not on this Blog. Well, here we are with same topic online.

Answer in one line is : HAVING specifies a search condition for a group or an aggregate function used in SELECT statement.

HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause.

A HAVING clause is like a WHERE clause, but applies only to groups as a whole, whereas the WHERE clause applies to individual rows. A query can contain both a WHERE clause and a HAVING clause. The WHERE clause is applied first to the individual rows in the tables . Only the rows that meet the conditions in the WHERE clause are grouped. The HAVING clause is then applied to the rows in the result set. Only the groups that meet the HAVING conditions appear in the query output. You can apply a HAVING clause only to columns that also appear in the GROUP BY clause or in an aggregate function. (Reference :BOL)

Example of HAVING and WHERE in one query:

SELECT titles.pub_id, AVG(titles.price)FROM titles INNER JOIN publishersON titles.pub_id = publishers.pub_idWHERE publishers.state = 'CA'GROUP BY titles.pub_idHAVING AVG(titles.price) > 10

Sometimes you can specify the same set of rows using either a WHERE clause or a HAVING clause. In such cases, one method is not more or less efficient than the other. The optimizer always automatically analyzes each statement you enter and selects an efficient means of executing it. It is best to use the syntax that most clearly describes the desired result. In general, that means eliminating undesired rows in earlier clauses.

SQL SERVER – Introduction to JOINs – Basic of JOINs

The launch of Gandhinagar SQL Server User Group was a tremendous, astonishing success! It was overwhelming to see a large gathering of enthusiasts looking up to me (I was the Key Speaker) eager to enhance their knowledge and participate in some brainstorming discussions. Some members of User Group had requested me to write a simple article on JOINS elucidating its different types.

Download SQL Script used throughout in this article to practice along. Now, take a quick look at the following two tables I have created.

INNER JOIN

This join returns rows when there is at least one match in both the tables.

OUTER JOIN

There are three different Outer Join methods.

LEFT OUTER JOIN
This join returns all the rows from the left table in conjunction with the matching rows from the right table. If there are no columns matching in the right table, it returns NULL values.

RIGHT OUTER JOIN
This join returns all the rows from the right table in conjunction with the matching rows from the left table. If there are no columns matching in the left table, it returns NULL values.

FULL OUTER JOIN
This join combines left outer join and right outer join. It returns row from either table when the conditions are met and returns null value when there is no match.

CROSS JOIN

This join is a Cartesian join that does not necessitate any condition to join. The resultset contains records that are multiplication of record number from both the tables.

Additional Notes related to JOIN:

The following are three classic examples to display where Outer Join is useful. You will notice several instances where developers write query as given below.

SELECT t1.*FROM Table1 t1WHERE t1.ID NOT IN (SELECT t2.ID FROM Table2 t2)GO

The query demonstrated above can be easily replaced by Outer Join. Indeed, replacing it by Outer Join is the best practice. The query that gives same result as above is displayed here using Outer Join and WHERE clause in join.

/* LEFT JOIN - WHERE NULL */SELECT t1.*,t2.*FROM Table1 t1LEFT JOIN Table2 t2 ON t1.ID = t2.IDWHERE t2.ID IS NULL

The above example can also be created using Right Outer Join.

NOT INNER JOIN
Remember, the term Not Inner Join does not exist in database terminology. However, when full Outer Join is used along with WHERE condition, as explained in the above two examples, it will give you exclusive result to Inner Join. This join will give all the results that were not present in Inner Join.

You can download the complete SQL Script here, but for the sake of complicity I am including the same script here.

USE AdventureWorks
GOCREATE TABLE table1(ID INT, Value VARCHAR(10))INSERT INTO Table1 (ID, Value)SELECT 1,'First'UNION ALLSELECT 2,'Second'UNION ALLSELECT 3,'Third'UNION ALLSELECT 4,'Fourth'UNION ALLSELECT 5,'Fifth'GOCREATE TABLE table2(ID INT, Value VARCHAR(10))INSERT INTO Table2 (ID, Value)SELECT 1,'First'UNION ALLSELECT 2,'Second'UNION ALLSELECT 3,'Third'UNION ALLSELECT 6,'Sixth'UNION ALLSELECT 7,'Seventh'UNION ALLSELECT 8,'Eighth'GOSELECT *FROM Table1SELECT *FROM Table2
GOUSE AdventureWorks
GO/* INNER JOIN */SELECT t1.*,t2.*FROM Table1 t1INNER JOIN Table2 t2 ON t1.ID = t2.ID
GO/* LEFT JOIN */SELECT t1.*,t2.*FROM Table1 t1LEFT JOIN Table2 t2 ON t1.ID = t2.ID
GO/* RIGHT JOIN */SELECT t1.*,t2.*FROM Table1 t1RIGHT JOIN Table2 t2 ON t1.ID = t2.ID
GO/* OUTER JOIN */SELECT t1.*,t2.*FROM Table1 t1FULL OUTER JOIN Table2 t2 ON t1.ID = t2.ID
GO/* LEFT JOIN - WHERE NULL */SELECT t1.*,t2.*FROM Table1 t1LEFT JOIN Table2 t2 ON t1.ID = t2.IDWHERE t2.ID IS NULLGO/* RIGHT JOIN - WHERE NULL */SELECT t1.*,t2.*FROM Table1 t1RIGHT JOIN Table2 t2 ON t1.ID = t2.IDWHERE t1.ID IS NULLGO/* OUTER JOIN - WHERE NULL */SELECT t1.*,t2.*FROM Table1 t1FULL OUTER JOIN Table2 t2 ON t1.ID = t2.IDWHERE t1.ID IS NULL OR t2.ID IS NULLGO/* CROSS JOIN */SELECT t1.*,t2.*FROM Table1 t1CROSS JOIN Table2 t2
GODROP TABLE table1DROP TABLE table2
GO

I hope this article fulfills its purpose. I would like to have feedback from my blog readers. Please suggest me where do you all want me to take this article next.