When studying relationships, we reviewed techniques of
making data from one table available to the records of another table. This
demonstrated to reduce data duplication and mistakes. Another issue that
involves the combination of tables consists of creating records from more than
one table and making the result into a single list. This is the basis of
data joins.

A data join is a technique of creating a list of records
from more than one table, using all columns from all tables involved, or
selecting only the desired columns from one or all of the tables involved.
This means that a data join is essentially created in three steps:

Selecting the tables that will be involved in the join

Selecting a column that will create the link in each table

Writing or creating a SQL statement that will produce the records

The Tables of a Join

Before creating a join, you must have the tables that would
be involved. The tables are created using the techniques we have seen in
previous lessons. It is also important to create a primary key for each table.
The parent table would usually need only this primary key that would be used to
"link" it to a child table. Here is an example of such a table:

If needed, you can then create the necessary records for the
table. Here is an example:

When creating the child table, remember to create a column
that would serve as the link to the parent table. By a (good) habit as we saw
when studying relationships, the name and the data type of this column is the
same as the primary key of the parent table. Here is an example of a child table
that would be joined to the above parent table:

Once again, if necessary, you can add the needed records to
the table. Here is an example:

Practical Learning: Introducing Joins

Start Microsoft Visual C# and create a new Windows Application named
AltairRealtors7

In the Solution Explorer, right-click Form1.cs and click Rename

Type AltairRealtors.cs and press Enter

Double-click the middle of the form and implement the Load event as
follows:

Execute the application to actually create the database and its tables

Close the form and return to your programming environment

To create a data source, on the main menu, click Data -> Add New Data
Source...

In the first page of the wizard, make sure Database is selected and
click Next

In the combo box

If you see a AltairRealtors2, select it

If you do not have AltairRealtors2, click New
Connection... In the Server combo box, select the server or type
(local). In the Select Or Enter A Database Name combo box,
select AltairRealtors2. Click Test Connection. Click OK twice. In the
Data Source Configuration Wizard, make sure the new connection is
selected and click Next. Change the Connection String to
csAltairRealtors and click Next. Expand the Tables node and
click the check box of Properties. Change the DataSet Name to dsAltairRealtors

Click Finish

In the Data Sources window, drag the Properties node and drop it on the form

Under the form, click propertiesBindingNavigator and press Delete

Using the Properties window, change the names of the other two objects as follows:

Object

Name

propertiesBindingSource

bsProperties

propertiesTableAdapter

taProperties

Design the form as follows:

Control

Text

Name

Other Properties

DataGridView

dgvProperties

Anchor: Top, Bottom, Left, Right

Label

Show:

Anchor: Bottom, Right

ComboBox

All

Anchor: Bottom, Right
Items:
All
Townhouses Only
Condominiums Only
Single Families Only

Button

Close

btnClose

Anchor: Bottom, Right

Double-click the Close button and implement its even as follows:

private void btnClose_Click(object sender, EventArgs e)
{
Close();
}

Save the form

Visually Creating a Join

Equipped with the necessary tables and their columns,
you can create the join. To do this in the Microsoft SQL Server Management Studio,
you can right-click one of the tables involved and click Open Table. This would display the
Table window. You should then display the Diagram and the SQL sections. Because the foundation of a join lies on
at least two tables, you should add one. To do this

On the main menu, you can click Query Designer -> Add Table...

On the Query Designer toolbar, you can click the Add
Table button

You can right-click the Diagram section of the window and click Add table...

Any of these actions would display the Add Table dialog box. To select a
table:

You
can click the table's name and click Add

You can double-click a table

Alternatively, instead of using Add Table, you can
drag the child table from the Object Explorer and drop it in the Diagram
section.

Here is an example of two tables that have been added:

Remember that you can drag the title bars of the
tables to move them and position them to your liking. After selecting the table(s),
on the Add Table dialog box, you can click Close.

If a relationship was already established between the
tables, a joining line would show it.

Programmatically Creating a Join

In SQL code, the basic formula to create a join is:

SELECT WhatColumn(s)
FROM ChildTableTypeOfJoinParentTable
ON Condition

The ChildTable factor specifies the table that
holds the records that will be retrieved. It can be represented as
follows:

SELECT WhatColumn(s)
FROM PersonsTypeOfJoinParentTable
ON Condition

The ParentTable factor specifies the table that
holds the column with the primary key that will control what records,
related to the child table, that will display. This factor would be
represented as follows:

The Condition factor is a logical expression
used to validate the records that will be isolated. To create the
condition, you should assign the primary key column of the parent table to
the foreign key column of the child table. Because both columns likely
have the same name, to distinguish them, their names should be qualified.
This would be done as follows:

The WhatColumn(s) factor of our formula allows
you to make a list of the columns you want to include in your statement.
As you should be aware, you can include all columns by using the *
operator. Here is an example:

SELECT *
FROM Persons
TypeOfJoin Sexes
ON Persons.SexID = Sexes.SexID

In this case, all columns from all tables would be
included in the result. Instead of all columns, you may want a restricted
list. In this case, create the list after the SELECT keyword
separating them with commas. You can use the name of a column normally if
that name is not duplicated in more than one column. Here is an example:

If the same name of a column is found in more than one
table, as is the case for a primary-foreign key combination, you should
qualify the name of the column by preceding it with the name of its parent
table followed by a period. Here are examples:

In the Data Source window, right-click dsAltair and click Edit Dataset
with Designer

In the designer, right-click the title bar of the Properties table and
click Configure

In the TableAdapter Configuation Wizard, click Query Builder...

Right-click an (any) area of the Query Builder window and click Add Table

In the Add Table dialog box, double-click PropertyTypes

Click Close

Uncheck all the columns

Cross and Inner Joins

Introduction

When studying data relationships, we saw the role of
the primary and foreign keys in maintaining the exchange of information
between two tables. 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 CROSS JOIN or CROSS OUTER JOIN.
Here is an example:

If you are working visually on a table, by default,
after you have just added a table to another one (if no 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:

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 do not
have an entry for the SexID column 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.

By default,
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
edit the SQL statement. Consider the following:

Notice that, because no relationship was previously
established between both tables, the join is crossed.

To create an inner join, 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. Here is an example:

Alternatively, you can edit the SQL
statement manually to make it an inner join. To do this, you would specify the
TypeOfJoin factor of our formula with the expression INNER JOIN.
Here is an example:

After creating the join, in the Diagram section, a
line would be created to join the tables. You can then execute the query
to see the result. This would produce:

We mentioned earlier that you could include all
columns in the query. In our result, since we are more interested in the
Sex of each Persons record, we would not need the
SexID column from the Sexes table. Here is an example:

As mentioned earlier, notice that the result includes
only records that have an entry (a non-NULL entry) in the SexID foreign
key column of the Persons table.

An alternative to the INNER JOIN expression is
to simply type JOIN. Here is an example:

To destroy a join between two tables, if you are
working in the Table window, you can right-click the line that joins the
tables and click Remove. In SQL, you must modify the expressions that make
up the join (the JOIN and the ON expressions).

Practical Learning: Creating an Inner Join

To create an inner join, from the PropertyTypes table, drag
PropertyTypeID and drop it on the PropertyTypeID field of the
Properties table:

Release the mouse

On the tables, select the following fields: PropertyType, City,
Bedrooms, Bathrooms, YearBuilt, and MarketValue

On the Query Designer toolbar, click the Execute button to see the
result

Click OK

Click Finish

Access the form and click the data grid view

In the Properties window, set its DataSource to None and set it
again to bsProperties

Execute the application to see the result

Close the form and return to your programming environment

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
do not have an entry in the foreign key column are marked as NULL.

To
create a left outer join, if you are working in the Table window, in the
Diagram section, right-click the line that joins the tables and click the
option that would select all records from the child table (in this case,
that would be Select All Rows From Persons):

Alternatively, you can replace the
TypeOfJoin factor of our formula with either LEFT JOIN or LEFT
OUTER JOIN.
Here is an example:

In both cases, the button in the middle of the line
would be added an arrow that points to the parent table. You can then
execute the query to see the result. Here is an example:

Notice that the result includes all records of the
Persons (also called the right) table and the records that don't have an
entry in the SexID column of the Persons (the right) table are marked
with NULL.

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
Sexes 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 SexID column. After the
first record, the right outer join 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 visually create a right outer join in the Table
window, after establishing a join between both tables, if you had
previously created a left outer join, you should remove it by
right-clicking the line between the tables and selecting the second option
under Remove. Then, you can
right-click the line that joins them and click the option that would
select all records from the parent table. In our example, you would click
Select All Rows From Sexes.

To create a right outer join in SQL, you can replace the
TypeOfJoin factor of our formula with RIGHT JOIN or RIGHT
OUTER JOIN.
Here is an example:

In both cases, the button on the joining line between
the tables would have an arrow that points to the child table. You can
then run the query. Here is an example:

Notice that the query result starts with the first
record of the parent table, also called the left table (in this case the
Sexes 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 SexID
value. Also, notice that there are no NULL records in the Sex.

Practical Learning: Getting Non-NULL Records

In the Data Source window, right-click dsAltairRealtors and click Edit Dataset
with Designer

In the designer, right-click the title bar of the Properties table and
click Configure...

In the TableAdapter Configuation Wizard, click Query Builder...

To get a list of only properties whose types are known, right-click
the line between the tables and click Select all rows from
PropertyTypes

Right-click anywhere in the window and click Execute SQL

Notice that the result is the list of tables in order by types
(condos, single families, and town homes)

Uncheck all columns

Full Outer Joins

A full outer join produces all records from both the
parent and the child tables. If a record from one table does not have a
value in the other value, the value of that record is marked as NULL.

To visually create a full outer join, in the Table
window, right-click the line between the tables and select each option
under Remove so that both would be checked. To create a full outer join in
SQL, replace the
TypeOfJoin factor of our formula with FULL JOIN or FULL OUTER JOIN.
Here is an example:

The button on the line between the tables would now
appear as a square. You can then execute the query. Here is an example:

Just as we have involved only two tables in our joins
so far, you can create a join that includes many tables.

Joins and Data Analysis

Introduction

As demonstrated so far and in previous lessons, the
main reason for creating queries is to isolate records. This is done using
conditions and criteria. Joins enhance this capability because they allow
you to consider records from different tables and include them in a common
SQL statement.

In the joins we have created so far, we considered all
records and let the database engine list them using only the rules of
joins built-in the SQL. To make such a list more useful or restrictive, you
can pose your own conditions that should be respected to isolate records
like a funnel. As done in previous lessons, to include
a criterion in a SELECT statement, you can create a WHERE
clause.

Using Criteria

To create a criterion in a query you create from the
table view in the Microsoft SQL Server Management Studio or Microsoft
Visual Studio, first select a column to display it in the
Grid section. Just as reviewed in the previous lessons when creating a
query, to specify a criterion, in the Criteria box corresponding to the
column, type the condition using any of the operators we reviewed in
previous lessons. Here is an example: