Prizes & Awards
My Profile
Active Members
TodayLast 7 Days
more...
|
 New Feature: Community Sites:
Create your own .NET community website and start earning from Google AdSense !
It's Free !
|
JOINS IN SQL-SERVER EXPLAINED
Posted Date: 11 Sep 2008 Resource Type: Articles Category: Databases
|
Posted By: G Vijay Bhargav Member Level: Bronze
Rating:  Points: 12
|
Hi,
This article explains about the following SQL-Server joins types.
a) Inner Join
b) Left-Outer Join
c) Right-Outer Join
d) Full-Outer Join
First of all lets create the following two tables.
The queries to create tables are given below:
a) tblOne
create table tblOne(intField int not null primary key, strField nvarchar(10))
b) tblTwo
create table tblTwo(intField int not null primary key, strField nvarchar(10))
If you observe the structure of the above tables you will find 2 fields in each table.
The first field is the primary key which helps us to maintain unique data in the [intField] excluding NULL values.
The second field [strField] contains the data.
Note: In this example Foreign-Key constraint is not used to make it simple.
Second, we will insert data into both of these tables. While inserting we will make sure both tables will contain some data common in both of them, [tblOne] contains unmatched data with respect to [tblTwo] and vice-versa.
The queries to insert data are given below:
a) tblOne
insert into tblone values(11,'Vijay_01')
insert into tblone values(22,'Bhargav')
insert into tblone values(33,'Ram_01')
insert into tblone values(44,'John')
b) tblTwo
insert into tbltwo values(11,'Vijay_02')
insert into tbltwo values(33,'Ram_02')
insert into tbltwo values(55,'Mike')
insert into tbltwo values(77,'Tom')
Now, we have 4 records in [tblOne] and 4 in [tblTwo] in which only 2 records in both tables match with each other.
Finally we will start experimenting with the joins:
a) Inner Join:
- Retrieves data common in both tables (w.r.t the ID field).
Our Query:
select O.intField as [One_intField],O.strField as [One_strField],T.intField as [Two_intField],T.strField as [Two_strField]
from tblOne O
inner join tblTwo T on O.intField = T.intField
Output:
One_IntField One_strField Two_IntField Two_strField
------------ ------------ ------------ ------------
11 Vijay_01 11 Vijay_02
33 Ram_01 33 Ram_02
In the above output you can identify that sql-server returned only matching rows from [tblOne] and [tblTwo].
b) Left-Outer Join:
-Retrieves data common in both tables and
-Unmatched rows from "table1" with NULL value filled in the selected "table2" columns
Our Query:
select O.intField as [One_intField],O.strField as [One_strField],T.intField as [Two_intField],T.strField as [Two_strField]
from tblOne O
left outer join tblTwo T on O.intField = T.intField
Output:
One_IntField One_strField Two_IntField Two_strField
------------ ------------ ------------ ------------
11 Vijay_01 11 Vijay_02
22 Bhargav NULL NULL
33 Ram_01 33 Ram_02
44 John NULL NULL
In the above output you can identify that sql-server returned all rows from [tblOne]. The rows in [tblOne] which does not have a match in [tblTwo] were filled with [NULL] values.
c) Right-Outer Join:
-Retrieves data common in both tables and
-Unmatched rows from "table2" with NULL value filled in the selected "table1" columns
Our Query:
select O.intField as [One_intField],O.strField as [One_strField],T.intField as
[Two_intField],T.strField as [Two_strField]
from tblOne O
right outer join tblTwo T on O.intField = T.intField
Output:
One_IntField One_strField Two_IntField Two_strField
------------ ------------ ------------ ------------
11 Vijay_01 11 Vijay_02
33 Ram_01 33 Ram_02
NULL NULL 55 Mike
NULL NULL 77 Tom
In the above output you can identify that sql-server returned all rows from [tblTwo]. The rows in [tblTwo] which does not have a match in [tblOne] were filled with [NULL] values.
d) Full-Outer Join:
-Retrieves data common in both tables and
-Unmatched rows from "table1" with NULL value filled in the selected "table2" columns
-Unmatched rows from "table2" with NULL value filled in the selected "table1" columns
Our Query:
select O.intField as [One_intField],O.strField as [One_strField],T.intField as [Two_intField],T.strField as [Two_strField]
from tblOne O
full outer join tblTwo T on O.intField = T.intField
Output:
One_IntField One_strField Two_IntField Two_strField
------------ ------------ ------------ ------------
11 Vijay_01 11 Vijay_02
22 Bhargav NULL NULL
33 Ram_01 33 Ram_02
44 John NULL NULL
NULL NULL 55 Mike
NULL NULL 77 Tom
In the above output you can identify that sql-server returned all rows from [tblOne] and [tblTwo] with the unmatched rows having NULL values filled.
Regards,
G. Vijay Bhargav
|
Responses
|
| Author: Sriram 02 Nov 2008 | Member Level: Gold Points : 2 | nice attempt ...but
a) tblOne
create table tblOne(intField int not null primary key, strField nvarchar(10))
b) tblTwo
create table tblTwo(intField int not null primary key, strField nvarchar(10))
here u have mentiond not null primary key what is the need for not null and primary key any one is enough i meen primary key is enough because primary key is not null and avoid duplicate values
SriramRamaswamy
|
|