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.

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:

SELECT Persons.PersonID, Persons.FirstName, Persons.LastName, 
             Sexes.SexID, Sexes.Sex
FROM Persons
CROSS JOIN Sexes
GO

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:

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:

SELECT Persons.PersonID, Persons.FirstName, Persons.LastName, Persons.SexID,
       Sexes.SexID AS [Sex ID], Sexes.Sex
FROM   Persons INNER JOIN Sexes ON Persons.SexID = Sexes.SexID

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:

SELECT Persons.PersonID, Persons.FirstName, Persons.LastName, 
       Sexes.Sex
FROM Persons
JOIN Sexes
ON Persons.SexID = Sexes.SexID

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).

1. To create an inner join, from the PropertyTypes table, drag PropertyTypeID and drop it on the PropertyTypeID field of the Properties table:
    
   
2. Release the mouse
3. On the tables, select the following fields: PropertyType, City, Bedrooms, Bathrooms, YearBuilt, and MarketValue
4. On the Query Designer toolbar, click the Execute button to see the result
    
   
5. Click OK
6. Click Finish
7. Access the form and click the data grid view
8. In the Properties window, set its DataSource to None and set it again to bsProperties
9. Execute the application to see the result
    
   
10. Close the form and return to your programming environment