SQL Joins in ASP.Net 2.0 with Visual Studio 2005
SQL Query Joins
ASP.Net 2.0 – Visual Studio 2005
CSE686 – Internet Programming
Instructor: James W. Fawcett
TA: Murat K. Gungor
Summer 2006
2
Query Joins
Why do we need
query joins?
View
information from
two or more separate
database tables
Type of query joins
Inner Join
Outer Join
Left Outer Join
Right Outer Join
3
Let’s Open Visual Studio .NET
2005
Using Sever Explorer
Add Connection…
We just created
BestShop.mdf
(mdf =Master Database
File)
Add new SQL Database File
4
Lets add two tables
Customers and Orders
CustomerID is Unique
and it is an Identity
ProducID is Unique and
it is an Identity
5
We have
CustomerTable and
OrderTable
Lets add some data to
our tables
6
Inner Join
An
An
inner join
inner join
is a join that
is a join that
selects only those records
selects only those records
from both database tables
from both database tables
that have matching values.
that have matching values.
Records with values in the
Records with values in the
joined field that do not
joined field that do not
appear in both of the
appear in both of the
database tables will be
database tables will be
excluded
excluded
from the query.
from the query.
SELECT
SELECT
CustomerTable.Name, OrderTable.ProductName
CustomerTable.Name, OrderTable.ProductName
FROM
FROM
CustomerTable
CustomerTable
INNER JOIN
INNER JOIN
OrderTable
OrderTable
ON
ON
CustomerTable.CustomerID = OrderTable.CustomerID
CustomerTable.CustomerID = OrderTable.CustomerID
7
•
The
The
INNER JOIN
INNER JOIN
returns all rows from
returns all rows from
both tables where
both tables where
there is a match.
there is a match.
•
If there are rows in
If there are rows in
CustomerTable that do
CustomerTable that do
not have matches in
not have matches in
OrderTable, those rows
OrderTable, those rows
will
will
not
not
be listed.
be listed.
Inner Join Query Result
Inner Join Query Result
We see ONLY matching
values
SELECT
SELECT
CustomerTable.Name, OrderTable.ProductName
CustomerTable.Name, OrderTable.ProductName
FROM
FROM
CustomerTable
CustomerTable
INNER JOIN
INNER JOIN
OrderTable
OrderTable
ON
ON
CustomerTable.CustomerID = OrderTable.CustomerID
CustomerTable.CustomerID = OrderTable.CustomerID
8
Outer Join
An
An
outer join
outer join
selects all of the records from one
selects all of the records from one
database table and only those records in the second
database table and only those records in the second
table that have matching values in the joined field.
table that have matching values in the joined field.
In a
In a
left outer join
left outer join
, the selected
, the selected
records will include all of the
records will include all of the
records in the first database table.
records in the first database table.
In a
In a
right outer join
right outer join
, the selected
, the selected
records will include all records of
records will include all records of
the second database table.
the second database table.
SYNTAX
SELECT
field1, field2, field3
FROM
first_table
LEFT JOIN
second_table
ON
first_table.keyfield = second_table.foreign_keyfield
9
SELECT
SELECT
CustomerTable.Name, OrderTable.ProductName
CustomerTable.Name, OrderTable.ProductName
FROM
FROM
CustomerTable
CustomerTable
LEFT OUTER JOIN
LEFT OUTER JOIN
OrderTable
OrderTable
ON
ON
CustomerTable.CustomerID = OrderTable.CustomerID
CustomerTable.CustomerID = OrderTable.CustomerID
NULL values also appears
•
The
The
LEFT OUTER JOIN
LEFT OUTER JOIN
returns all
returns all
the rows from the first table
the rows from the first table
(CustomerTable), even if there are
(CustomerTable), even if there are
no matches in the second table
no matches in the second table
(OrderTable).
(OrderTable).
•
If there are rows in CustomerTable
If there are rows in CustomerTable
that do not have matches in
that do not have matches in
OrderTable, those rows
OrderTable, those rows
also
also
will be
will be
listed.
listed.
10
SELECT
SELECT
CustomerTable.Name, OrderTable.ProductName
CustomerTable.Name, OrderTable.ProductName
FROM
FROM
CustomerTable
CustomerTable
RIGHT OUTER JOIN
RIGHT OUTER JOIN
OrderTable
OrderTable
ON
ON
CustomerTable.CustomerID = OrderTable.CustomerID
CustomerTable.CustomerID = OrderTable.CustomerID
•
The
The
RIGHT OUTER JOIN
RIGHT OUTER JOIN
returns
returns
all the rows from the second table
all the rows from the second table
(OrderTable), even if there are no
(OrderTable), even if there are no
matches in the first table
matches in the first table
(CustomerTable
(CustomerTable
).
).
•
If there had been any rows in
If there had been any rows in
OrderTable
OrderTable
that did not have
that did not have
matches in CustomerTable, those
matches in CustomerTable, those
rows
rows
also
also
would have been listed.
would have been listed.
11
End of Presentation
Resources
Resources
http://www.databasedev.co.uk/query_joins.html
http://www.databasedev.co.uk/query_joins.html
http://www.w3schools.com/sql/sql_join.asp
http://www.w3schools.com/sql/sql_join.asp
http://en.wikipedia.org/wiki/JOIN
http://en.wikipedia.org/wiki/JOIN
http://www.databasejournal.com/sqletc/article.php/26861_1402351_1
http://www.databasejournal.com/sqletc/article.php/26861_1402351_1
Exploring the importance of query joins in SQL, this instructional content delves into inner, outer, left outer, and right outer joins for integrating data from multiple database tables in ASP.Net 2.0. Detailed guidance is provided on setting up SQL databases in Visual Studio .NET 2005, creating tables, adding data, and executing join queries.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
You are allowed to download the files provided on this website for personal or commercial use, subject to the condition that they are used lawfully. All files are the property of their respective owners.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.
E N D
Presentation Transcript
SQL Query Joins ASP.Net 2.0 Visual Studio 2005 CSE686 Internet Programming Instructor: James W. Fawcett TA: Murat K. Gungor Summer 2006
Query Joins Why do we need query joins? View information from two or more separate database tables Type of query joins Inner Join Outer Join Left Outer Join Right Outer Join 2
Add new SQL Database File Let s Open Visual Studio .NET 2005 Using Sever Explorer Add Connection We just created BestShop.mdf (mdf =Master Database File) 3
Lets add two tables Customers and Orders CustomerID is Unique and it is an Identity ProducID is Unique and it is an Identity 4
We have CustomerTable and OrderTable Lets add some data to our tables 5
Inner Join An inner join is a join that selects only those records from both database tables that have matching values. Records with values in the joined field that do not appear in both of the database tables will be excluded from the query. SELECT CustomerTable.Name, OrderTable.ProductName FROM CustomerTable INNER JOIN OrderTable ON CustomerTable.CustomerID = OrderTable.CustomerID 6
Inner Join Query Result The INNER JOIN returns all rows from both tables where there is a match. If there are rows in CustomerTable that do not have matches in OrderTable, those rows will not be listed. We see ONLY matching values SELECT CustomerTable.Name, OrderTable.ProductName FROM CustomerTable INNER JOIN OrderTable ON CustomerTable.CustomerID = OrderTable.CustomerID 7
Outer Join An outer join selects all of the records from one database table and only those records in the second table that have matching values in the joined field. In a left outer join, the selected records will include all of the records in the first database table. In a right outer join, the selected records will include all records of the second database table. SYNTAX SELECT field1, field2, field3 FROM first_table LEFT JOIN second_table ON first_table.keyfield = second_table.foreign_keyfield 8
SELECT CustomerTable.Name, OrderTable.ProductName FROM CustomerTable LEFT OUTER JOIN OrderTable ON CustomerTable.CustomerID = OrderTable.CustomerID NULL values also appears The LEFT OUTER JOIN returns all the rows from the first table (CustomerTable), even if there are no matches in the second table (OrderTable). If there are rows in CustomerTable that do not have matches in OrderTable, those rows also will be listed. 9
SELECT CustomerTable.Name, OrderTable.ProductName FROM CustomerTable RIGHT OUTER JOIN OrderTable ON CustomerTable.CustomerID = OrderTable.CustomerID The RIGHT OUTER JOIN returns all the rows from the second table (OrderTable), even if there are no matches in the first table (CustomerTable ). If there had been any rows in OrderTable that did not have matches in CustomerTable, those rows also would have been listed. 10
End of Presentation Resources http://www.databasedev.co.uk/query_joins.html http://www.w3schools.com/sql/sql_join.asp http://en.wikipedia.org/wiki/JOIN http://www.databasejournal.com/sqletc/article.php/26861_1402351_1 11
