SQL Data Joins With ADO.NET
|
SQL Data Joins With ADO.NET |
Types of Joins
Introduction
When studying data relationships, we saw the role of the primary and foreign keys in maintaining the exchange of information between two table. This technique of linking tables plays a major part when creating a join. It allows you to decide whether you want to include all records or only isolate some of them. To respect the direction of a relationship between two tables as it is applied to a query, Transact-SQL supports three types of joins.
|
Cross Joins |
A cross join creates a list of all records from both tables as follows: the first record from the parent table is associated to each record from the child table, then the second record from the parent table is associated to each record from the child table, and so on. In this case also, there is no need of a common column between both tables. In other words, you will not use the ON clause.
To create a cross join, you can replace the TypeOfJoin factor of our formula with eight CROSS JOIN or CROSS OUTER JOIN. Here is an example:
SELECT Persons.PersonID, Persons.FirstName, Persons.LastName,
Genders.GenderID, Genders.Gender
FROM Persons
CROSS JOIN Genders
GO
By default, from the SQL Server Enterprise Manager, when creating a new query, if a relationship was already established between both tables, the query would be automatically made a cross join. All you have to do is to select the needed columns:
After selecting the columns, you can execute the query to see the result:
Practical Learning: Using Cross Joins
private void btnLoad_Click(object sender, System.EventArgs e)
{
this.sqlDataAdapter1.Fill(this.dsCars1);
}
private void btnClose_Click(object sender, System.EventArgs e)
{
Close();
}
|
Inner Joins
|
Imagine you have two tables that can be linked through one's primary key and another's foreign key. |
 |
 |
|
Notice that some records in the Persons table don't have an entry and were marked with <NULL> by the database engine. When creating a query of records of the Persons table, if you want your list to include only records that have an entry, you can create it as inner join. To do this, you would specify the TypeOfJoin factor of our formula with the expression INNER JOIN. Here is an example: SELECT Persons.LastName, Persons.FirstName, Persons.GenderID,
Genders.GenderID, Genders.Gender
FROM Persons
INNER JOIN Genders
ON Persons.GenderID = Genders.GenderID
GO
This would produce:
By default, from the SQL Server Enterprise Manager, when creating a new query, if a relationship was already established between both tables, the query is made an inner join. If there was no relationship explicitly established between both tables, you would have to appropriately edit the SQL statement. Consider the following:
Notice that, because no relationship was previously established between both tables, the join is crossed. In this case, you have two options. You can drag the primary key from the parent table and drop it on the foreign key in the child table, or you can edit the SQL statement manually to make it an inner join. We mentioned earlier that you could include all columns in the query. In our result, since we are more interested in the gender of each Persons record, we would not need to GenderID column from the Genders table. An alternative to the INNER JOIN expression is to simply type JOIN. Here is an example: SELECT Persons.PersonID, Persons.FirstName, Persons.LastName,
Genders.Gender
FROM Persons
JOIN Genders
ON Persons.GenderID = Genders.GenderID
GO
This would produce the same effect:
As mentioned earlier, notice that the result includes only records that have an entry (a non-NULL entry) in the GenderID foreign key column of the Persons table. |
Practical Learning: Using Inner Joins
|
Outer Joins
|
Introduction |
|
Instead of showing only records that have entries in the child table, you may want your query to include all records, including those that are null. To get this result, you would create an outer join. You have three options. |
|
Left Outer Joins |
|
A left outer join produces all records of the child table, also called the right table. The records of the child table that don't have an entry in the foreign key column are marked as NULL. To create a left outer join, you can replace the TypeOfJoin factor of our formula with LEFT JOIN or LEFT OUTER JOIN. Here is an example: SELECT Persons.PersonID, Persons.FirstName, Persons.LastName,
Genders.GenderID, Genders.Gender
FROM Persons
LEFT OUTER JOIN Genders
ON Persons.GenderID = Genders.GenderID
GO
This would produce:
Notice that the result includes all records of the Persons (also called the right) table and a record that doesn't have an entry in the GenderID column of the Persons (the right) table are marked with NULL. To create a left outer join in the Data In Table window, after establishing a relationship between both tables, you can right-click the line that joins them and click the top Select All Rows From option:
This would change the join into a left outer join:
|
Practical Learning: Using Left Outer Joins
|
Right Outer Joins |
|
A right outer join considers all records from the parent table and finds a matching record in the child table. To do this, it starts with the first record of the parent table (in this case the Genders table) and shows each record of the child table (in this case the Persons table) that has a corresponding entry. This means that, in our example, a right outer join would first create a list of the Persons records that have a 1 (Female) value for the GenderID column. After the first record, the right outer joins moves to the second record, and so on, each time listing the records of the child table that have a corresponding entry for the primary key of the parent table. To create a left outer join, you can replace the TypeOfJoin factor of our formula with eight RIGHT JOIN or RIGHT OUTER JOIN. Here is an example: SELECT Persons.PersonID, Persons.FirstName, Persons.LastName,
Genders.GenderID, Genders.Gender
FROM Persons
RIGHT OUTER JOIN Genders
ON Persons.GenderID = Genders.GenderID
GO
This would produce:
Notice that the query result starts with the first record of the parent table, also called the left table (in this case the Genders table), and lists the records of the child table, also called the right table (in this case the Persons table), that have the entry corresponding to that first record. Then it moves to the next GenderID value. To create a right outer join in the Data In Table window, after establishing a relationship between both tables, you can right-click the line that joins them and click the second Select All Rows From option. This would change the join into a left outer join. |
|
Left Outer Joins |
|
A full outer join produces all records from both the parent and the child tables. If a record from one table doesn't have a value in the other value, the value of that record is marked as NULL. To create a full outer join, you can replace the TypeOfJoin factor of our formula with eight FULL JOIN or FULL OUTER JOIN. Here is an example: SELECT Persons.PersonID, Persons.FirstName, Persons.LastName,
Genders.GenderID, Genders.Gender
FROM Persons
FULL OUTER JOIN Genders
ON Persons.GenderID = Genders.GenderID
GO
This would produce:
To create a full outer join in the Data In Table window, you can right-click the line that joins the tables and click put a check mark on each Select All Rows From option:
|
|
|
||
| Previous | Copyright © 2005-2016, FunctionX | Next |
|
|
||
