Boxing vs UnBoxing

Boxing and Unboxing is a  essential concept in .Net’s type system. With Boxing and Unboxing one can link between value-types and reference-types by allowing any value of a value-type to be converted to and from type object. The basic differences are :-

	Boxing	Unboxing
Definition:	Boxing is the process of converting a value type to the reference type.	Unboxing is the process of converting a reference type to value type .
Type of Conversion:	Implicit Conversion	Explicit Conversion
Example:	int i = 221; object obj = i; //boxing	object obj = 213; i = (int)obj ; // unboxing

Collections

using System;
using System.Collections; // for Collections
using System.Collections.Generic; // for Generics

Collection classes are specialized classes for data storage and retrieval.

Collections are enhancement to the arrays.

Collection classes serve various purposes, such as allocating memory dynamically to elements and accessing a list of items on the basis of an index etc.

There are two distinct collection types in C#.

The standard collections, which are found under the System.Collections namespace (Array list, Hashtable, BitArray, Queue, SortedList, Stack)
And the generic collections, under System.Collections.Generic. (List, LinkedList, Dictionary)

The generic collections are more flexible and are the preferred way to work with data.
The generic collections or generics were introduced in .NET framework 2.0.
Generics enhance code reuse, type safety, and performance.

ArrayList

• It represents ordered collection of an object that can be indexed individually.


• It is basically an alternative to an array.


• However unlike array you can add and remove items from a list at a specified position using an index and the array resizes itself automatically.


• It also allows dynamic memory allocation, add, search and sort items in the list.


• Unlike arrays, an ArrayList can hold data of multiple data types.

ArrayList al = new ArrayList();
al.Add(45);
al.Add("hello");
al.Count;
al.Sort();

1. Add( object value ); // Adds an object to the end of the ArrayList.


2. AddRange( ICollection c ); // Adds the elements of an ICollection to the end of the ArrayList.


3. Clear(); // Removes all elements from the ArrayList.


4. Contains( object item ); // Determines whether an element is in the ArrayList.


5. GetRange( int index, int count ); // Returns an ArrayList which represents a subset of the elements in the source ArrayList.


6. IndexOf(object); // Returns the zero-based index of the first occurrence of a value in the ArrayList or in a portion of it.


7. Insert( int index, object value ); // Inserts an element into the ArrayList at the specified index.


8. InsertRange( int index, ICollection c );// Inserts the elements of a collection into the ArrayList at the specified index.


9. Remove( object obj ); // Removes the first occurrence of a specific object from the ArrayList.


10. RemoveAt( int index ); // Removes the element at the specified index of the ArrayList.


11. RemoveRange( int index, int count ); // Removes a range of elements from the ArrayList.


12. Reverse(); // Reverses the order of the elements in the ArrayList.


13. SetRange( int index, ICollection c ); // Copies the elements of a collection over a range of elements in the ArrayList.


14. Sort(); // Sorts the elements in the ArrayList.


15. TrimToSize(); // Sets the capacity to the actual number of elements in the ArrayList.

Hashtable

• The Hashtable class represents a collection of key-and-value pairs that are organized based on the hash code of the key.


• It uses a key to access the elements in the collection.


• A hash table is used when you need to access elements by using key, and you can identify a useful key value.


• Each item in the hash table has a key/value pair. The key is used to access the items in the collection.

Hashtable ht = new Hashtable();
ht.Add("001", "Zara Ali");

1. Add( object key, object value ); // Adds an element with the specified key and value into the Hashtable.


2. Clear(); // Removes all elements from the Hashtable.


3. ContainsKey( object key ); // Determines whether the Hashtable contains a specific key.


4. ContainsValue( object value ); // Determines whether the Hashtable contains a specific value.


5. Remove( object key ); // Removes the element with the specified key from the Hashtable.

SortedList

• It uses a key as well as an index to access the items in a list.


• A sorted list is a combination of an array and a hash table.


• It contains a list of items that can be accessed using a key or an index.


• If you access items using an index, it is an ArrayList, and if you access items using a key , it is a Hashtable.


• The collection of items is always sorted by the key value.

SortedList sl = new SortedList();
sl.Add("001", "Zara Ali");

1. public virtual void Add( object key, object value ); // Adds an element with the specified key and value into the SortedList.


2. Clear(); // Removes all elements from the SortedList.


3. ContainsKey( object key ); // Determines whether the SortedList contains a specific key.


4. ContainsValue( object value ); // Determines whether the SortedList contains a specific value.


5. GetByIndex( int index ); // Gets the value at the specified index of the SortedList.


6. GetKey( int index ); // Gets the key at the specified index of the SortedList.


7. IList GetKeyList(); // Gets the keys in the SortedList.


8. IList GetValueList(); // Gets the values in the SortedList.


9. IndexOfKey( object key ); // Returns the zero-based index of the specified key in the SortedList.


10. IndexOfValue( object value ); // Returns the zero-based index of the first occurrence of the specified value in the SortedList.


11. Remove( object key ); // Removes the element with the specified key from the SortedList.


12. RemoveAt( int index ); // Removes the element at the specified index of SortedList.


13. TrimToSize(); // Sets the capacity to the actual number of elements in the SortedList.

Stack

It represents a last-in, first out collection of object. It is used when you need a last-in, first-out access of items.

When you add an item in the list, it is called pushing the item and when you remove it, it is called popping the item.

Stack st = new Stack();
st.Push('A');
st.Push('H');
st.Peek() - H
st.Pop();

1. Clear(); // Removes all elements from the Stack.


2. Contains( object obj ); // Determines whether an element is in the Stack.


3. Peek(); // Returns the object at the top of the Stack without removing it.


4. Pop(); // Removes and returns the object at the top of the Stack.


5. Push( object obj ); // Inserts an object at the top of the Stack.


6. ToArray(); // Copies the Stack to a new array.

Queue

It represents a first-in, first out collection of object. It is used when you need a first-in, first-out access of items.

When you add an item in the list, it is called enqueue and when you remove an item, it is called deque.

Queue q = new Queue();
q.Enqueue('A');
q.Enqueue('M');
char ch = (char)q.Dequeue(); - Removes 'A'

1. Clear(); // Removes all elements from the Queue.


2. Contains( object obj ); // Determines whether an element is in the Queue.


3. Dequeue(); // Removes and returns the object at the beginning of the Queue.


4. Enqueue( object obj ); // Adds an object to the end of the Queue.


5. ToArray(); // Copies the Queue to a new array.


6. TrimToSize(); // Sets the capacity to the actual number of elements in the Queue.

BitArray

It represents an array of the binary representation using the values 1 and 0.
It is used when you need to store the bits but do not know the number of bits in advance.
You can access items from the BitArray collection by using an integer index, which starts from zero.

System.Collections.Generic;

List

A List is a strongly typed list of objects that can be accessed by index.
This is an efficient, dynamically-allocated array. It does not provide fast lookup in the general case (the Dictionary is better for lookups). List is often used in loops.

List<int> list = new List<int>();
list.Add(2);
list.Add(6);
list.Remove(2);
list.Insert(1,5);

int[] arr = new int[2];
arr[0] = 2;
arr[1] = 3;
list = new List<int>(arr);

Dictionary

• This is an implementation of a hash table: an extremely efficient way to store keys for lookup.


• Dictionary is fast, well-designed and reliable.


• A dictionary, also called an associative array, is a collection of unique keys and a collection of values, where each key is associated with one value.


• Retrieving and adding values is very fast. Dictionaries take more memory, because for each value there is also a key.

Dictionary<string, string> domains = new Dictionary<string, string>();
domains.Add("de", "Germany");
domains.Add("sk", "Slovakia");
foreach(KeyValuePair<string, string> kvp in domains) { }

Diff. b/w where and having in Sql Server

• WHERE clause is used for filtering rows and it applies on each and every row, while HAVING clause is used to filter groups.


• We can use WHERE and HAVING clause together in a SELECT query, WHERE clause is applied first on individual rows and only after grouping HAVING clause is applied.


• Syntactically where is used before GROUP BY clause, while having is used after GROUP BY clause.


• We can't use having clause in update, delete queries, allowed only in select query.


• To use Having Clause, we have to use Group By Clause since it filters data that we get from Group By Clause


• We can use aggregate operations with having, but not with where


• We can use Group By Clause with or without Where Clause.


• select count(1) count  from emp having avg(rating)<10

How a HAVING clause works

• The select clause specifies the columns.


• The from clause supplies a set of potential rows for the result.


• The where clause gives a filter for these potential rows.


• The group by clause divide the rows in a table into smaller groups.


• The having clause gives a filter for these group rows.

Get count of emp's under a manager in Sqlserver

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[emp]') AND type in (N'U'))
BEGIN

CREATE TABLE [dbo].[emp](
[id] [int] IDENTITY(1,1) NOT NULL,
[empid] [int] NULL,
[empname] [varchar](50) NULL,
[mgrid] [int] NULL,
[role] [varchar](50) NULL,
CONSTRAINT [PK_emp] PRIMARY KEY CLUSTERED
([id] ASC)
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[emp] ON
INSERT [dbo].[emp] ([id], [empid], [empname], [mgrid], [role]) VALUES (1, 1, N'abc', 1, N'se')
INSERT [dbo].[emp] ([id], [empid], [empname], [mgrid], [role]) VALUES (2, 2, N'def', 1, N'se')
INSERT [dbo].[emp] ([id], [empid], [empname], [mgrid], [role]) VALUES (3, 3, N'ghi', 1, N'se')
INSERT [dbo].[emp] ([id], [empid], [empname], [mgrid], [role]) VALUES (4, 4, N'klm', 4, N'se')
INSERT [dbo].[emp] ([id], [empid], [empname], [mgrid], [role]) VALUES (5, 5, N'nop', 4, N'se')
INSERT [dbo].[emp] ([id], [empid], [empname], [mgrid], [role]) VALUES (6, 6, N'qrs', 4, N'se')
INSERT [dbo].[emp] ([id], [empid], [empname], [mgrid], [role]) VALUES (7, 7, N'tuv', 4, N'se')
INSERT [dbo].[emp] ([id], [empid], [empname], [mgrid], [role]) VALUES (8, 8, N'satya', 8, N'SSE')
INSERT [dbo].[emp] ([id], [empid], [empname], [mgrid], [role]) VALUES (9, 9, N'sai', 9, N'SSE')
INSERT [dbo].[emp] ([id], [empid], [empname], [mgrid], [role]) VALUES (12, 10, N'suman', 9, N'se')
INSERT [dbo].[emp] ([id], [empid], [empname], [mgrid], [role]) VALUES (13, 11, N'sri', 11, N'se')
INSERT [dbo].[emp] ([id], [empid], [empname], [mgrid], [role]) VALUES (14, 12, N'srikanth', 9, N'se')
INSERT [dbo].[emp] ([id], [empid], [empname], [mgrid], [role]) VALUES (15, 13, N'sirish', 9, N'se')
INSERT [dbo].[emp] ([id], [empid], [empname], [mgrid], [role]) VALUES (16, 14, N'siva', 9, N'fa')
SET IDENTITY_INSERT [dbo].[emp] OFF

END

select * from emp

-- SQL Query

select e1.empid,e1.empname,e1.role,
(select count(1) from emp e2 where e1.empid=e2.mgrid and e2.empid<>e2.mgrid)noofemps
from emp e1 where e1.empid=e1.mgrid

-- LINQ Query

var res = (from t in db.emps
join l in db.emps.Where(x => x.empid != x.mgrid) on t.empid equals l.mgrid
into gj
//from subset in gj.DefaultIfEmpty()
where t.mgrid == t.empid
select
//t).Distinct().ToList();
new empmsg{ empid = Convert.ToInt32(t.empid), empname = t.empname, role = t.role, empcount = gj.Count() });//.Distinct();

Cannot open database requested by the login. The login failed

This error occurs when you have configured your application with IIS, and IIS goes to SQL Server and tries to login with credentials that do not have proper permissions

1. Go to SQL Server > Security > Logins > right click on 'Your user error name' > Properties

In opened screen of Login Properties, go to the “User Mapping” tab.

2. Select map checkbox appropriate to your database, And change user dropdown to sa (if necessary)

3. Then change user membership to dbo.owner

Then click on OK

In almost all such cases, this should fix your problem.

 

 

Register Framework 4 for IIS hosting

%windir%\Microsoft.NET\Framework\v4.0.30319\aspnet_regiis.exe -i

Get factors of a number

int n = 10;
List<int> factors = new List<int>();
int max = Convert.ToInt32(Math.Sqrt(n));
for (int i = 1; i <= max; i++)
{
    if (n % i == 0)
    {
        factors.Add(i);
        if (i != max) // add square-root factors only once.
           factors.Add(n / i);
    }
}

Difference between String and string

1. String stands for System.String and it is a .NET Framework type.
2. string is a type in C#. System.String is a type in the CLR.
3. string is an alias for System.String in the C# language. Both of them are compiled to System.String in IL (Intermediate Language),
4. So there is no difference. Choose what you like and use that.
5. For code in C#, its prefer to use string as it's a C# type alias and well-known by C# programmers.

6. string is a reserved word, but String is just a class name.This means that 'string' cannot be used as a variable name by itself.

StringBuilder String = new StringBuilder(); // compiles
StringBuilder string = new StringBuilder(); // doesn't compile

7. If you really want a variable name called 'string' you can use @ as a prefix :
StringBuilder @string = new StringBuilder(); this applies to string also.

8. So String is not a keyword and it can be used as Identifier whereas string is a keyword and cannot be used as Identifier. And in function point of view both are same.
9. You can't use String without using System; but string can use without that namespace.
10. Static functions such as String.Format, String.Join, String.Concat, String.isNullOrWhitespace etc... are from String.

I can say the same about (int, System.Int32) etc..

default Timeouts in c#

SqlConnection.ConnectionTimeout - 15 sec

- Gets the time to wait while trying to establish a connection before terminating the attempt.

SqlCommand.CommandTimeout - 30 seconds

- Gets or sets the wait time before terminating the attempt to execute a command

Session Timeout - 20 mins

<system.web>
<SessionState timeout=”10”></SessionState>

Cookies per Domain - 20 - 1KB

 

PrimaryKey vs UniqueKey

Primary Key:
Primary Key enforces uniqueness of the column on which they are defined.
Primary Key creates a clustered index on the column.
Primary Key does not allow Nulls.

CREATE TABLE EMP (eID INT NOT NULL PRIMARY KEY,
eName VARCHAR(100) NOT NULL)

Alter table with Primary Key:
ALTER TABLE EMP
ADD CONSTRAINT pk_eID PRIMARY KEY (eID)

Unique Key:
Unique Key enforces uniqueness of the column on which they are defined.
Unique Key creates a non-clustered index on the column.
Unique Key allows only one NULL Value.

Alter table to add unique constraint to column:
ALTER TABLE EMP ADD CONSTRAINT UK_empName UNIQUE(empName)

Refer: https://www.simple-talk.com/sql/learn-sql-server/primary-key-primer-for-sql-server/

String Vs StringBuilder

System.String
-------------
1. String is immutable. It means that you can't modify string at all, the result of modification is new string. This is not effective if you plan to append to string
2. many actions that you do with string
3. Here concatenation is used to combine two strings by using String object
4. The first string is combined to the other string by creating a new copy in the memory as a string object, and then the old string is deleted
5. string is non updatable
6. everytime a object has to be created for Operations like append,Insert etc. at runtime

System.StringBuilder
--------------------
1. StringBuilder is mutable. It can be modified in any way and it doesn't require creation of new instance.
2. if you try to do some other manipulation (like removing a part from the string, replacing a part in the string, etc.), then it's better not to use StringBuilder at those places. This is because we are anyway creating newstrings.
3. system.stringbuilder is updateble
4. string builder is faster than the string object
5.String builder is more efficient in case large amount of string operations have to be perform.
6.Insertion is done on the existing string.
7. At the end, we can get the string by StringBuilder.ToString()
dis-adv:
1. We must be careful to guess the size of StringBuilder. If the size which we are going to get is more than what is assigned, it must increase the size. This will reduce its performance.
2. many actions can't be done with StringBinder.
3. When initializing a StringBuilder, you are going down in performance.
4. StringBuilder can be used where more than four or more string concatenations take place.

Update alternate rows in sqlserver

update s set recstatus=’I’ from
(select row_number() over (order by id) sno,* from mastertbl)s
where sno%2=0

or

with c as (
select row_number() over (order by id) sno,* from mastertbl
) update c set recstatus=’I’ where sno%2=0

Int range in .net

Int16 : The value of this constant is 32767 : hexadecimal 0x7FFF

Int16 (or short) uses 16 bits to store a value, giving it a range from -32,768 to 32,767

Int32 uses 32 bits to store a value, giving it a range from -2,147,483,648 to 2,147,483,647

Int 64 -- (-9223372036854775808 to +9223372036854775807)
SByte Min: -128 Max: 127
Byte Min: 0 Max: 255
Int16 Min: -32768 Max: 32767
Int32 Min: -2147483648 Max: 2147483647
Int64 Min: -9223372036854775808 Max: 9223372036854775807
Single Min: -3.402823E+38 Max: 3.402823E+38
Double Min: -1.79769313486232E+308 Max: 1.79769313486232E+308
Decimal Min: -79228162514264337593543950335 Max: 79228162514264337593543950335

9 February

i). Machine. Config

ii). Web.Config (For desktops app.Config)

Common paints for confn files:

• These are simple text files & can be edited using any simple text editor.


• These are xml formatted (case sensitive) & any xml programs can use these files or they can be directly edited because xml is human readable.


• * They are automatically cached on memory for faster retrieval of settings (Cache memory àprogrammed memory area).


• * Processed in hierarchical manner which means every configuration file inherits its parent configuration file settings. The root for all these files is Machine.Config.


• Also called as configuration inheritance apart from this configuration is also hierarchical as xml is hierarchical.

Machine.Config points:

• It is called per-server basis file. Only one machine.config is allowed for one runtime/server.


• Located .NET framework folder not on our project & all settings are common to all websites running in that system. * 4 MB (default)(more than 4.0 go to machine.config – one file- change)


• Most of the settings are maintained by administrator but not by application developers.


• here we find all website settings(default) i.e., namespaces, services of support, net support, compileation settings, deploy settings, db settings ......

Web.Config points:

• It is called as application Configuration file.


• **** It is optional today also to have web.config in our application located in root & can be created sub folders also. We can create multiple web.config’s on our site maximum is how many folders we have in our project.


• we can perform the following functionalities in web.config file:

1.  Maintain connection strings


2. Custom errors


3. Security


4. Debugging


5. Tracing

----------------------------

The Cursor Process has the following steps involved in it: (exception handling in sp)

• Declare a Cursor


• Open a Cursor


• Fetch data from the Cursor


• Close the Cursor


• De-allocate the Cursor

----------------------------

“When a client, makes a request for an aspx page, it will be processed at server & all the result produced is concatenated as a single string along with some hash values in it.

These concatenated values are converted into base 64 format***. These base 64 values are stored in one or more hidden fields*** by server sent to client along with other from contents.

User adds/modifies the content & resubmits the form to server. Server first reads the hidden field values so that it retains the last given values (act as stateful) &responds the same process with new repeated as long as user is working with current page....”

---------------------------

What is the scope of application variable?

1. Accessible to all users of application, irrespective of the user who created it.


2. The common memory area which is created in the webserver, which is common for all the users is k.led as App. Memory.


3. Storing the data in this app.memory is k.ed App. state in State mngt. sys      


4. .: The values stord by the webuser in the app memory, can be accessed by the other users also.                  


5. The App State can store obj data types.


6. App.State have to be used to store the Data which will be common for all the users of website.

---------------------------

All session variables are accessible to current user only.

Session variable are also of type object which means they can store any type of data (Load on server should be considered).

Session data will be stored @ server only & only session id travels. This data will be available as long as session is preserved by server. Sessions are normally ended based on timeout***.

In ASP.NET by default 20 min’s it the timeout (idle timeout) using configuration settings as well as programmatically with session object we can change this default time.

A session can be ended programmatically also. Session object has a method called Abandon () which kills the current session.

<system.web>

<SessionState timeout=”10”></SessionState>

-------------------------------







Non.Persistent Cookies: These Cookies will be created in the browser's process area. They r not mentioned with Time duration. These Cookies will be destroyed when the app is closed

Persistent Cookies: The Cookies which are created & exists for a given time duration., is k.ld as the Persistent Cookie. They are created in the Client machine

Query string:                                                                                                                                                               Query string is Client side st.mngt sys. It can hold only strings. The Query string Memory will destroyed, when the app is closed.                                                                                                                                                The values Stored in Query string can be accessed from any page through out the app. In general Query string is used 2 carry small values from one to another page.

----------------------------

1. Caching is the process of storing the page or objects in the cache memory of browse, proxy servers or webserver.      


2. in general, caching is used to improve the performance i.e., the page or object that is cached will be accessed from the cache memory tor the next request with out fetching it from the db server.


3. caching can be done at client side as well as server side.

These are 3 types of caching:

client side caching:

• output page caching (or) entire page caching


• fragment caching (or) partial page caching

server side caching:

• data caching (or) object caching.

using system.web.caching;

//create{                                                                                                                                                                                                            CacheDependency cd = new Cache Dependency(Server.MapPath("XMLfile.xml"), Cache.Insert("key1",ds.Tables[0]),cd,DateTime.Now.AddMinutes(10),Cache.NoSlidingExpression);              }

//get{                                                                                                                                                                                         gv1.DataSource = (DataTable)Cache.Get("key1");                                                                                                                      gv1.DataBind();                                                                                                                                                                                             }

1. Obj caching or data caching is server side State Management Sys.


2. we can create this kind of caching in 2 cryterious

• cache dependency: By using this, cache memory will be destroyed if the dependent file is modified.


• cache expiration: By using this parameter, cache mem. will be destroyed with in a given time after the creation.

-----------------------------

Primary Key:
Primary Key enforces uniqueness of the column on which they are defined.
Primary Key creates a clustered index on the column.
Primary Key does not allow Nulls.

CREATE TABLE EMP (eID INT NOT NULL PRIMARY KEY,
eName VARCHAR(100) NOT NULL)

Alter table with Primary Key:
ALTER TABLE EMP
ADD CONSTRAINT pk_eID PRIMARY KEY (eID)

Unique Key:
Unique Key enforces uniqueness of the column on which they are defined.
Unique Key creates a non-clustered index on the column.
Unique Key allows only one NULL Value.

Alter table to add unique constraint to column:
ALTER TABLE EMP ADD CONSTRAINT UK_empName UNIQUE(empName)

SQL SERVER – Introduction to JOINs – Basic of JOINs

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 t1
WHERE 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 t1
LEFT JOIN Table2 t2 ON t1.ID = t2.ID
WHERE 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
GO
CREATE TABLE table1
(ID INT, Value VARCHAR(10))
INSERT INTO Table1 (ID, Value)
SELECT 1,'First'
UNION ALL
SELECT 2,'Second'
UNION ALL
SELECT 3,'Third'
UNION ALL
SELECT 4,'Fourth'
UNION ALL
SELECT 5,'Fifth'
GO
CREATE TABLE table2
(ID INT, Value VARCHAR(10))
INSERT INTO Table2 (ID, Value)
SELECT 1,'First'
UNION ALL
SELECT 2,'Second'
UNION ALL
SELECT 3,'Third'
UNION ALL
SELECT 6,'Sixth'
UNION ALL
SELECT 7,'Seventh'
UNION ALL
SELECT 8,'Eighth'
GO
SELECT *
FROM Table1
SELECT *
FROM Table2
GO
USE AdventureWorks
GO
/* INNER JOIN */
SELECT t1.*,t2.*
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.ID = t2.ID
GO
/* LEFT JOIN */
SELECT t1.*,t2.*
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.ID = t2.ID
GO
/* RIGHT JOIN */
SELECT t1.*,t2.*
FROM Table1 t1
RIGHT JOIN Table2 t2 ON t1.ID = t2.ID
GO
/* OUTER JOIN */
SELECT t1.*,t2.*
FROM Table1 t1
FULL OUTER JOIN Table2 t2 ON t1.ID = t2.ID
GO
/* LEFT JOIN - WHERE NULL */
SELECT t1.*,t2.*
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t2.ID IS NULL
GO
/* RIGHT JOIN - WHERE NULL */
SELECT t1.*,t2.*
FROM Table1 t1
RIGHT JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t1.ID IS NULL
GO
/* OUTER JOIN - WHERE NULL */
SELECT t1.*,t2.*
FROM Table1 t1
FULL OUTER JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t1.ID IS NULL OR t2.ID IS NULL
GO
/* CROSS JOIN */
SELECT t1.*,t2.*
FROM Table1 t1
CROSS JOIN Table2 t2
GO
DROP TABLE table1
DROP TABLE table2
GO