Linq Query to compare only Date part of DateTime

In this post I am going to discuss about comparing the date part of the datetime in linq query. In linq query its easy to perform datetime comparison how to compare only date not time part of datetime filed of your entity.

Let have coloseure look, In Sql Server one can do something like as below to just compare date part of the field.

SELECT * FROM dbo.tbl_MyTable
WHERE 
CAST(CONVERT(CHAR(10), DateTimeValueColumn, 102) AS DATE) = 
            CAST(CONVERT(CHAR(10),GETDATE(),102) AS DATE)

So in above query convert function did task of removing time part and only date part comparison happens.

Linq Queires
Following discussion about doing same task using linq queries.
Solution 1:
First way to achieve same thing (i.e. comparing date part) of entity or object is following

var data = context.t_quoted_value.Where(x => x.region_name == "Hong Kong" 
                  && DateTime.Compare(x.price_date.Value.Date, dt.Date) == 0)
                  .ToList();

Here Date property of DatTime used to get only date part of datetime property and made use of DateTime.Compare function to get matching object.

But the problem with this approach when make use of EnityFramework i.e. Linq To Entity , its gives following error at runtime

The specified type member 'Date' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported.

so to avoid above error in Linq to Entity query is modified to following

var data = context.t_quoted_value.Where(x => x.region_name == "Hong Kong")
                            .ToList()
                            .Where (x=> DateTime.Compare(x.price_date.Value.Date, dt.Date) == 0)
                            .ToList(); 

So in above query first data is get fetched from the database and than on list date comparison get applied. But the problem with this approach is need to load all data first than the date comparison get applied because entityframework doesn't support direct query.

Solution 2:
One more easy and simple solution to just compare datet part of datetime object is as following

            
var data1 = context.t_quoted_value.Where(x => x.region_name == "Hong Kong" 
                            && x.price_date.Value.Year == dt.Year
                            && x.price_date.Value.Month == dt.Month
                            && x.price_date.Value.Day == dt.Day).ToList();

its query use the year,month and day property of datetime object to compare date. Advantage of this solution is this is compatible with all flavor of Linq i.e. it works in linq to sql, linq to object and also in linq to enitity.

Conclusion
So the post is useful when you need to compare only date part of datetime property in Linq queries.

Leave your comments if you like it. CodeProject

Call Sql Server inbuilt functions using Linq

The post is about the the new class introduce in .net framwork for support of built in SQL-Server function. The SqlFunctions class allows to call SQL-Server function from linq queries while using EntityFramwork.

Following describes how it works
Create Edmx file i.e EntityFramwork file

 Create connection with database

Select Table(s),view(s) and Stored procedure(s)

Created EDMX file

Use SqlFunction in query
Now after deisigning the enityframwork edmx file following is way to utilize the inbuilt sql server functions in Linq queries.

public List<person> SqlTest()
        {
            using (SchoolEntities se = new SchoolEntities())
            {
                var person = from p in se.People
                             where SqlFunctions.CharIndex("a", p.FirstName) == 1
                             select p;
                return person.ToList<person>();
            }
        }

As you see in above linq query its find out all persons whose name starts with letter "a". This is easily achievable by CharIndex function of sql server as you can see in query. This function is part of SqlFunction class avilable in .net framework.

SqlFunction class inside

#region Assembly System.Data.Entity.dll, v4.0.30319
// C:\Program Files\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.0\Profile\Client\System.Data.Entity.dll
#endregion

using System;
using System.Collections.Generic;
using System.Data.Objects.DataClasses;

namespace System.Data.Objects.SqlClient
{
 // Summary:
    //     Provides common language runtime (CLR) methods that call functions in the
    //     database in LINQ to Entities queries.
    public static class SqlFunctions
    {
        ........list of other function that is supported by this class
        //
        // Summary:
        //     Returns the starting position of one expression found within another expression.
        //
        // Parameters:
        //   toSearch:
        //     The string expression to be searched.
        //
        //   target:
        //     The string expression to be found.
        //
        //   startLocation:
        //     The character position in toSearch where searching begins.
        //
        // Returns:
        //     The starting position of target if it is found in toSearch.
        [EdmFunction("SqlServer", "CHARINDEX")]
        public static int? CharIndex(string toSearch, string target, int? startLocation)
    }
}

As you can see SqlFuction is static class and contains static methods which calls sql server inbuilt function.
Get more information about SqlFunction class and its method on msdn at : SqlFunction

Design TypeDataSet from Storedprocedure using temptable

I found one problem recently while designing TypeDataset in VisualStudio using storedproceudre which is making use of temporary table to get result.

Here is detail of the it what I did and how I resolved the issue.

Step 1: Created Procedure with Temporary table

create PROCEDURE [dbo].[GetData]
AS
begin
   create TABLE #MyTable  (
  ID int,
  Name nvarchar(50) )

 INSERT INTO #MyTable (ID, Name)
 SELECT  PersonID, FirstName + ' ' + LastName
 FROM  dbo.Person
 
 SELECT ID,
  Name 
 FROM #MyTable
end

Step 2: Add TableAdapter in the design view of TypeDataSet and create database connection

Step 3: Create Procedure or Select Existing procedure

Step 4 : Select Procedure that I created already

Note : here it's not displaying DataColumn of select statement related to proceudre


Step 5 : Click on finish it display that in valid object #table

so it doesn't able to create tableadapter for procedure and display like this

Solution
To resolve this issue you can try one of the following solution , I tried first solution because its easy and not require to change in my existing storedprocedure code

Solution 1
Just add below line at top of the procedure after begin statement

SET FMTONLY OFF

This will resolve the issue and allow to create tableadapter easily without any error. So procedure will be

create PROCEDURE [dbo].[GetData]
AS
begin
  SET FMTONLY OFF
   //code of the procedure as above
end

Solution 2
To try this solution just create table variable instead of temporary table in procedure. So procedure will be

create PROCEDURE [dbo].[GetData]
AS
begin
  DECLARE @MyTable TABLE (
  ID int,
  Name nvarchar(50) )

 INSERT INTO @MyTable (ID, Name)
 SELECT  PersonID, FirstName + ' ' + LastName
 FROM  dbo.Person
 
 SELECT ID,
  Name 
 FROM @MyTable
end

After trying the above solution tableadapter on XSD file will be like this

Bulk Insertion of Data Using C# DataTable and SQL server OpenXML function

In this post I am going to show how you can insert bulk data by using DataTable of C# and OpenXML function available in Sql Server.

I got requirement that "Read data from the Excel file and than after validating data
push all record in the database table". Other thing is when inserting data in database if there is failure during insertion of record, I have to rollback all inserted record.

To achieve the task I did as following

OpenXML
I created procedure which make use of OpenXML function of the sql server which allow to insert multiple record in one time. OpenXML require xml string of record to insert data in the database.

ALTER PROCEDURE [dbo].[Ins_Employee]    
(    @XmlString text    )    
AS    
BEGIN    
 SET NOCOUNT ON    
 BEGIN TRANSACTION    
 Begin Try    

  DECLARE @XMLDocPointer INT    
  EXEC sp_xml_preparedocument @XMLDocPointer OUTPUT, @XmlString    

   INSERT INTO Employee
   (Name, Email, PhoneNo)    
   SELECT Name,Email,PhoneNo   
   FROM OPENXML(@XMLDocPointer,'/ROOT/DATA',2)    
   WITH  (Name VARCHAR(50),-- '@Name',     
         Email VARCHAR(50),-- '@Email',     
         PhoneNo VARCHAR(50) --'@PhoneNo')     

   EXEC sp_xml_removedocument @XMLDocPointer    
   COMMIT TRANSACTION    
   Return 0;     
 End Try    
 Begin Catch    
   ROLLBACK TRANSACTION    
 End Catch    
END 

As you see in above procedure OpenXML make use of xmlDocument as input which is get created by system define procedure sp_xml_preparedocument which take xmlString as input and return XmlDocument.
Once OpenXML done task of insertion sp_xml_removedocument system proceudre is require to remove that element.
All record get inserted in once by the OpenXML function as I used transaction if the one record insertion fails all inserted record get rollback.

Following line of the code used to execute code i.e stored procedure
As you see in I am passing Element centric xml to the proceudre.

Exec Ins_Employee
 '
  
    pranay
    pranayamr@gmail.com
    99007007
  
 '

Note
If you are passing XML string as Attribute centric in it as in procedure than you need to define variable so the select statement in procedure will be

SELECT Name,Email,PhoneNo   
   FROM OPENXML(@XMLDocPointer,'/ROOT/DATA',2)    
   WITH  (Name VARCHAR(50) '@Name',     
         Email VARCHAR(50) '@Email',     
         PhoneNo VARCHAR(50) '@PhoneNo')

Exec Ins_Employee
  '     
       
  '

Now after done with the database , code part of the application is as below.

Uploaded Excel File which contains Employee data

Presentation layer
Following function in presentation layer read data from the excel file, which is uploaded on server.

private void ReadAndInsertExcelData()
{
     int i;
     bool blValid = true;
     OleDbCommand ocmd;
     OleDbDataAdapter oda;
     DataTable dtDetails;
     DataSet dsDetails;

     OleDbConnection oconn = new OleDbConnection     
          (@"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + 
               Server.MapPath("~/Upload/MonthlyActual.xls") + ";Extended 
               Properties='Excel 8.0;HDR=YES;IMEX=1'");
     try
     {
          ocmd = new OleDbCommand("select * from [Sheet1$]", oconn);
          oda = new OleDbDataAdapter(ocmd);
          dsDetails = new DataSet();
          oda.Fill(dsDetails, "DATA");
          dtDetails = dsDetails.Tables[0];
          dsDetails.DataSetName = "ROOT";
          i = 0;

          DataRow[] drLst = dtDetails.Select("(Name is null) or (Email is 
                              null) or (PhoneNo is null)");
          if (drLst.Count() > 0)
               blValid = false;
          if (blValid)
          {
               XMLController xMLController = new XMLController();
               xMLController.Ins(BaseLineType, dtDetails);
          }
     }
     catch 
     {
          lblMsg.Text = ex.Message;
          lblMsg.ForeColor = System.Drawing.Color.Red;
     }
     finally
     {
          lblMsg.Text = "Data Inserted Sucessfully";
          oda = null;
          dtDetails = null;
          dsDetails = null;
     }
}

Business Layer
Function below takes DataTable as input and generate XML string, As you see below I used StringWriter which use StringBuilder object, DataTable make use of StringWriter and write XML string in StringBuilder object.

public int Ins(DataTable pImportTable)
{
     int IsSuccess = -100;
     try
     {
          StringBuilder sbXMLString = new StringBuilder();
          System.IO.StringWriter sw = new System.IO.StringWriter
                                                  (sbXMLString);
          pImportTable.WriteXml(sw);

          DALXML dALManualCost = new DALXML();
          dALManualCost.Ins(sbXMLString.ToString());
          IsSuccess = dALManualCost.IsSuccess;
     }
     catch
     {
          throw;
     }
     return IsSuccess;
}

Note:Above method generate Element centric XML string.

Now if you want to write out the Attribute centric xml file you just need to replace the line of datatable.WriteXml with the below code for loop also you dont require to use the StringWriter object.

sbXMLString.Append("");
          for (int i = 0; i < pImportTable.Rows.Count; i++)
          {
            sbXMLString.Append("<DATA ");
            sbXMLString.Append("Name='" + 
                         pImportTable.Rows[i][0].ToString().Trim() + "' ");
            sbXMLString.Append("Email='" + pImportTable.Rows
                         [i][1].ToString().Trim() + "' ");
            sbXMLString.Append("PhoneNo='" + 
                         pImportTable.Rows[i][2].ToString().Trim() + "' ");
            
            sbXMLString.Append(" />");
 
          }
          sbXMLString.Append("");

DataLayer 
Now this layer call the stored procedure which pass the xmlstring of employee to database. Return parameter will tell that its successfull insert or not.

public void Ins(string pXMLString)
{
     try
     {
          Database db = CommonHelper.GetDataBaseInstance();
          DbCommand cmdXML = db.GetStoredProcCommand
                         (SP_INSERT_STAGINGMANUALCOSTMONTHLY);

          db.AddInParameter(cmdXML, "XmlString", DbType.String, 
                                                       pXMLString);
          db.AddParameter(cmdXML, "ret", DbType.Int32,                     
          ParameterDirection.ReturnValue, "", DataRowVersion.Current, 
                                                            IsSuccess);

          db.ExecuteNonQuery(cmdXML);
          IsSuccess = Convert.ToInt32(db.GetParameterValue(cmdXML, "ret"));
     }
     catch
     {
          IsSuccess = -100;
          throw;
     }
}

Note : This is the one technique I found useful to enter bulk amount of data in database in one transaction. There are also other available which might be more efficient than this.

Manage Sql Server Database within Visual Studio Team System

Introduction
In the below post I am going show how VSTS use full for the database developers. Note that I am not having full idea about all things but it’s very helpful to DBA and developer who is working on large project with the large no of team member.

Some time when we are releasing our project/product to client problem arise that our dataset version is not matching with the current application version. There is always problem when no of people working on same project and updating database object frequently and we miss updated object at time of release.

But now with the help of the VS team system 2008 we can easily resolve this issue and keep the database changes in VSS. In this article I am going to show how to create Database project and the option you get after creating the database. You get following advantages

• Manage DB change through schema management
"One version of the truth" Offline sandbox for multiple developers

•  Source control for DBs
Ability to store different versions as change sets

• Schema and data compare
Generate scripts/apply updates

Start with Create DataBase Project

   1. Sql Server 2000
   2. Sql Server 2000 Wizard
   3. Sql Server 2005
   4. Sql Server 2005 Wizard

Sql Server 2005 Wizard
Select database instance you installed on your machine or from you network.

Welcome note by wizard

Select you schema or the object type
A database schema is a way to logically group objects such as tables, views, stored procedures etc. Think of a schema as a container of objects.
You can assign user login permissions to a single schema so that the user can only access the objects they are authorized to access.
Schemas can be created and altered in a database, and users can be granted access to a schema. A schema can be owned by any user, and schema ownership is transferable.

Select database collation and some other options
A collation encodes the rules governing the proper use of characters for either a language, such as Greek or Polish, or an alphabet, such as Latin1_General (the Latin alphabet used by western European languages).

• Each SQL Server collation specifies three properties:
• The sort order to use for Unicode data types (nchar, nvarchar, and ntext). A sort order defines the sequence in which characters are sorted, and the way characters are evaluated in comparison operations.
• The sort order to use for non-Unicode character data types (char, varchar, and text).
• The code page used to store non-Unicode character data.

Create new database or import database schema form existing one by selecting from combo box
Choose important options according the needs

Provide information to connect with the database

Build and deploy Configuration

Once you done with the database creation project get created with the folder you see in below screen

• Stored Procedures
• Functions
• Triggers
• Arbitrary SQL

You can find below options which allow you to compare data or schema of the database

• Allows comparisons of:    
Project -> database; database -> database

• Object level script difference between DBs
• Notifies when data loss may occur
• Generate script or apply changes directly
• It’s smart!
understands constraints, creates temp tables to hold data, more

• Compare security settings
Users, roles and permissions

After you done with the adding and changing database object you can build and deploy project as you can see in blow project

Summary
DataBase project and the related utilities to support it by VSTS is very important, time saving and make database maintainable.

SQL to LINQ ( Case 8 - Filter data by RowNumbers )

Case 8 : Filter data by RowNumbers

Here in this case I am going to show how you can filter your data by RowNumbers that you assigned to your recored(s).

So to filter data in SQL (SQL server-2005) we use RowNumber function and than we use <=, >= or BETWEEN  to filer data.

SQL query

SELECT *
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY [id]) AS [ROW_NUMBER],
   [id], [FirstName], [LastName], [Email], [DisplayName], [Address1], [Address2], [Password], [Role]
    FROM [User] AS [t0]
    ) AS [t1]
WHERE [t1].[ROW_NUMBER] BETWEEN 11 AND 20
ORDER BY [t1].[ROW_NUMBER]

In above query as you can see ROW_NUMBER() function assign number to records and than we use that number in outer query to filter data between 11 to 20.


LINQ query
But in LINQ it make use of two functions

• Skip: Bypasses a specified number of elements in a sequence and then returns the remaining elements. (See this link.)
• Take: Returns a specified number of contiguous elements from the start of a sequence. (See this link.)

So LINQ query is something as below.

var users = from u in Users
select u;

var filterUsers= users.OrderBy (p => p.Id).Skip (10).Take(10);

In above code we are selecting data first and than we are applying Skip and Take to get data between 11 to 20 records.
Graphic representation

Summary
Best example of this is when you are using custom paging in you gridcontrol or list control.
more detail Example : LINQ TO SQL GridView (Enhanced Gridview)

Part - 1 :SQL to LINQ ( Visual Representation )
              SQL to LINQ ( Case 7 - Filter data by using IN and NOT IN clause)

SQL to LINQ ( Case 7 - Filter data by using IN and NOT IN clause)

After the the first post SQL to LINQ ( Visual Representation ). In this post I am going to show some SQLqueries and LINQ queries, but not going to show images for all cases.

Case 7 : Filter data by using IN and NOT IN clause

Most of the developer who started working on LINQ queries gets confuse when they got requirement to write IN and NOT IN query using LINQ.

SQL Query

//IN
SELECT [Id], [UserId], [IMEINo]
FROM [UserClients]
WHERE [UserId] IN (3, 4)

or

//NOT IN
SELECT [Id], [UserId],  [IMEINo]
FROM [UserClients]
WHERE [UserId] IN (3, 4)

as you see above query use IN and NOT IN clause to filter from list of records.

LINQ Query
To achieve similar task LINQ make use of Contains function of C#. which do filtering of record form the list of record.

//IN
int[] chosenOnes = { 3, 4 };
var user = from u in UserClients
where chosenOnes.Contains(u.UserId.Value)
select new  { u.id,u.userid, u.ImeiNo};

or 

//NOT IN
int[] chosenOnes = { 3, 4 };
var user = from u in UserClients
where !chosenOnes.Contains(u.UserId.Value)
select u;

Note :
IN and NOT IN use same function in LINQ query but it just use !(Not) symbol for it.

Graphical representation

Summary
Part - 1 : SQL to LINQ ( Visual Representation )  

SQL to LINQ ( Visual Representation )

Now a days most of the developers are moving towards new LINQ to SQL they find difficult to write down SQL query in C# to query data using LINQ. LINQ is a query language which get integrated in C# to query data form ObjectCollects, SQL, XML etc.

Before you start reading about this post its good to have look on the features which supports LINQ

• Extension Methods
• Anonymous types
• Lamda Expressions
• Object and Collection Initialization

Here in this post I am going to discuss about the basic SQL queries where LINQ queries are similar to SQL queries. And visual representation of LINQ queries.
Before I start discussing here is structure of the table I am using for this article.

Users

UserClient

Linq Structure

Note : In this article all LINQ queries are performed in LINQPAD application.


Case 1 - SELECT 

SQL query to get info of all user from the user table with all column

SELECT * FROM [User]

LINQ query similar to above

var user = from u in Users
select u;

Graphical representation the break down of the LINQ query that you wrote to get data form the user table.

Case 2 - SELECT WITH COLUMNS

This case is similar to above case but the change is we are not selecting all columns of the table instead of that we are going to select only two columns here for this example querying only tow column of table FirstName and LastName.

SQL query to select all row with only two column of the table

Select firstname,LastName from [User]

Now the LINQ query for the similar one is

from u in Users
select new
{
    u.FirstName,
    u.LastName
};

So you need to create new anonymous type to get only FirstName and LastName form the user object.
Graphical representation of this query is

Case 3 - FILTER SELECT DATA 

FOR INTEGER KIND OF DATA
To apply filter on the selected data we use WHERE clause with the column value.SQL query for this is

Select firstname,LastName from [User] where id = 3

same as SQL in LINQ we use WHERE clause to filter data, LINQ query is

from u in Users
where u.Id ==3
select new
{
   u.FirstName,
   u.LastName
}

Graphic representation shows breakdown of the LINQ query related to filtering of data

FOR STRING KIND OF DATA
As we can filter interger kind of data similarly inorder to filter string we use LIKE

SELECT  [Id], [FirstName], [LastName], [Email], [DisplayName], [Address1], [Address2], [Password], [Role]
FROM [User]
WHERE [Email] LIKE '%pranay%'

or

SELECT  [Id], [FirstName], [LastName], [Email], [DisplayName], [Address1], [Address2], [Password], [Role]
FROM [User]
WHERE [Email] LIKE 'pranay%'

To apply the filter on string datatype field you require to use Contains or StartWith function available in C# it generates same result as SQL query

from u in Users
where u.Email.Contains ("pranay")
select u

or

from u in Users
where u.Email.StartsWith ("pranay")
select u

Graphical representation of LINQ query filtering using string field

Case 4 - JOINING TWO TABLE 

INNER JOIN
Inner join is by which we can get common records between two table i.e related records form those table. SQL query for inner join is

SELECT [User].[Id], [FirstName], [LastName], [UserId], [MobileNo]
FROM [User]
INNER JOIN
[UserClients]
ON [User].[id] = [UserId]

SQL LINQ do the same thing it use JOIN keyword with EQUALS to join two collection.LINQ query for this is 

var user = from u in Users
join uc in UserClients on u.Id equals uc.UserId
select new {
  u.Id,
  u.FirstName,
  u.LastName,
  uc.MobileNo,
  uc.imeiNO,
  uc.Id,
};

Graphical representation of the Inner join for the LINQ query is as shown below. So as you can see in the image the User connection get added to UserClients and based on condition in On.. Equals

OUTER JOIN
Outer Join is by which we can get common records between two table i.e related records form that table and as well as the all record form left table and not found right table column get null value. SQL query for outer join is

SELECT [t0].[Id], [FirstName], [LastName], [UserId] AS [UserId], [MobileNo] AS [MobileNo]
FROM [User] AS [t0]
LEFT OUTER JOIN [UserClients]  ON ([t0].[id]) = [UserId]

In LINQ to achieve outer join you need to use DefaultIfEmpty() function which so the LINQ query for this is like

var user = from u in Users
join uc in UserClients on u.Id equals uc.UserId
into myuserwithclient
from m in myuserwithclient.DefaultIfEmpty()
select new {
 u.Id,
 u.FirstName,
 u.LastName,
 m.UserId,
 m.MobileNo
};

Graphical representation of the outer join LINQ query is same as inner join but there on more step for the function DefaultIfEmpty() is added

Case 5 - ORDERING DATA 

In SQL to Order  fetched data one need to apply ORDER BY clause with ASC or DESC word, SQL query for this is

--Ascending
Select * from [User] order by firstName

or

--Descending
Select * from [User] order by firstName desc

SQL LINQ use ORDER BY combine with ASCENDING and DESCENDING keyword so that final LINQ query is

//Ascending
var user = from u in Users
orderby u.FirstName
 select new
{
   u.FirstName,
   u.LastName 
}

//Descending
var user = from u in Users
orderby u.FirstName descending
select new
{
   u.FirstName,
   u.LastName 
};

Graphical breakdown of LINQ query is

\

Case 6 - GROUPING DATA

Group of the selected data allow to perform the aggregate function like SUM, MAX, MIN, COUNT etc. To Group data in SQL you need to use GROUP BY  clause but the thing to remember is you need to include select list column in your group by clause otherwise you will get an syntax error

SELECT COUNT(*) AS [test], [UserId]
FROM [UserClients]
GROUP BY [UserId]

LINQ use Group ... By to group data , query is look like

var user =  from u in UserClients
group u by u.UserId into c
select new
{
 t1 = c.Key,
 tcount = c.Count()
};

Note :
After you apply group by on collection of object in LINQ your group by column get converted in key column which you can see in above LINQ query that I am referring UserId as Key.

Graphical breakdown of the the Group..By LINQ query is

Summary
So the article shows visual representation LINQ queries. In part-2 I am going to discuss about more sql queries and related LINQ queries for that.

Visual Representation of SQL JOINS

In this post I am going to show the types of join available in SQL by the van diagram representation


Basic types of join

• INNER JOIN
• OUTER JOIN

1. LEFT OUTER JOIN
2. RIGHT OUTER JOIN
3. FULL OUTER JOIN

• CROSS JOIN

Following table we are going to use in this discussion
TableA Structure

TableA Data

TableB Structure

TableB Data

INNER JOIN
A Join return tose data which are match condition applied in ON clause of the query so it display data which is common in bot TableA and TableB as you see in below image  

SELECT select_column_list FROM
TableA INNER JOIN TableB
ON TableA.ID = TableB.TableA_Id

Example:

LEFT OUTER JOIN
A Join return all rows which match condition of ON clause as well as all row form left table i.e TableA. Un-match row of right table i.e TableB replace by the NULL value which you can see in below image of output window

SELECT select_column_list FROM
TableA LEFT OUTER JOIN TableB
ON TableA.ID = TableB.TableA_Id

Example :



RIGHT OUTER JOIN

A Join return all rows which match condition of ON clause as well as all row form right table i.e TableB. Un-match row of left table i.e TableA replace by the NULL value which you can see in below image of output window

SELECT select_column_list FROM
TableA RIGHT OUTER JOIN TableB
ON TableA.ID = TableB.TableA_Id

Example:



FULL OUTER JOIN

A Join return all rows which match condition of ON clause as well as all row form both table. Un-match row of left table i.e TableA replace by the NULL value and Un-match row of right table i.e TableB replace by the
Null value, which you can see in below image of output window

SELECT select_column_list FROM
TableA FULL OUTER JOIN TableB
ON TableA.ID = TableB.TableA_Id

Example:



CROSS JOIN
A join wose result set includes one row for eac possible pairin of rows form te two tables.

SELECT select_column_list FROM
TableA CROSS JOIN TableB