Home

Data Joins: 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:

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:

Join

 

Inner Joins

Imagine you have two tables that can be linked through one's primary key and another's foreign key.

Sexes A list of people

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:

Join

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:

Dragging a field to create a join

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:

Join

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:

Join

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

Practical LearningPractical Learning: Creating an Inner Join

  1. To create an inner join, from the PropertyTypes table, drag PropertyTypeID and drop it on the PropertyTypeID field of the Properties table:
     
    Join
  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
     
    Query Builder
  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
     
    Altair Realtors
  10. Close the form and return to your programming environment
 
 

Published on Sunday 10 February 2008

 

Previous Copyright © 2008-2010 FunctionX, Inc. Next