Home

Data Joins
 

Joins
 

Introduction

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 that 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:

  1. Selecting the tables that will be involved in the join
  2. Selecting a column that will create the link in each table
  3. Writing a SQL statement that will create 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. If needed, you can then create the necessary records for the table. Here is an example:

CREATE TABLE Genders
(
    GenderID int identity(1, 1) not null,
    Gender nchar(15),
    CONSTRAINT PK_Genders PRIMARY KEY(GenderID)
);
GO

INSERT INTO Genders(Gender)
VALUES(N'Male'),(N'Female'),(N'Unknown');
GO

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

-- ===================================================
-- Database:	 People
-- Author:	 FunctionX
-- Date Created: Tuesday, July 28, 2009
-- ===================================================

IF EXISTS(SELECT name FROM sys.databases
	  WHERE name = N'People')
DROP DATABASE People;
GO
CREATE DATABASE People;
GO

USE People;
GO

IF OBJECT_ID('Genders', 'U') IS NOT NULL
  DROP TABLE Genders
GO

-- ===================================================
-- Database:	 People
-- Table:	 Genders
-- Author:	 FunctionX
-- Date Created: Tuesday, July 28, 2009
-- Description:	 This table holds the list of genders
-- ===================================================
CREATE TABLE Genders
(
    GenderID int identity(1, 1) not null,
    Gender nchar(15),
    CONSTRAINT PK_Genders PRIMARY KEY(GenderID)
);
GO

INSERT INTO Genders(Gender)
VALUES(N'Male'),(N'Female'),(N'Unknown');
GO

-- ===================================================
-- Database:	 People
-- Table:	 Persons
-- Author:	 FunctionX
-- Date Created: Tuesday, July 28, 2009
-- Description:	 This table holds a list of people
--				 and their genders
-- ===================================================
CREATE TABLE Persons
(
    PersonID int identity(1, 1) not null,
    FirstName nvarchar(20),
    LastName nvarchar(20),
    GenderID int,
    CONSTRAINT PK_Persons PRIMARY KEY(PersonID)
);
GO

INSERT INTO Persons(FirstName, LastName, GenderID)
VALUES(N'John', N'Franks', 1), (N'Peter', N'Sonnens', 1);
GO
INSERT INTO Persons(FirstName, LastName)
VALUES(N'Leslie',N'Aronson');
GO
INSERT INTO Persons(FirstName, LastName, GenderID)
VALUES(N'Mary', N'Shamberg', 2), (N'Chryssa', N'Lurie', 2),
      (N'Hellah', N'Zanogh', 3), (N'Olympia', N'Sumners', 2),
      (N'Roberta', N'Jerseys', 2);
GO
INSERT INTO Persons(FirstName, LastName)
VALUES(N'Helène', N'Campo');
GO
INSERT INTO Persons(LastName, GenderID)
VALUES(N'Millam', 1), (N'Hessia', 2);
GO
INSERT INTO Persons(FirstName, LastName, GenderID)
VALUES(N'Stanley', N'Webbs', 2), (N'Arnie', N'Ephron', 3),
      (N'Mike', N'Pastore', 1);
GO
INSERT INTO Persons(FirstName) VALUES(N'Salim');
GO
INSERT INTO Persons(FirstName, LastName, GenderID)
VALUES(N'Mary', N'Shamberg', 2), (N'Chryssa', N'Lurie', 2);
GO
INSERT INTO Persons(LastName) VALUES(N'Millers');
GO
INSERT INTO Persons(FirstName, GenderID) VALUES(N'Robert', 1);
GO

Practical LearningPractical Learning: Introducing Joins
  1. Start Microsoft SQL Server with the SQL Server Management Studio and connect to the server
  2. On the main menu, click File -> New -> Query With Current Connection
  3. To prepare a database for our exercises, copy and paste the following code in the Query window:
      
    -- =============================================
-- Author:   FunctionX
-- Database: RealEstate2
-- =============================================
IF EXISTS (
  SELECT * 
    FROM sys.databases 
   WHERE name = N'RealEstate2'
)
  DROP DATABASE RealEstate2
GO
CREATE DATABASE RealEstate2;
GO

-- =============================================
-- Author:   FunctionX
-- Database: RealEstate2
-- Table:    PropertyTypes
-- =============================================
USE RealEstate2;
GO
CREATE TABLE PropertyTypes
(
  PropertyTypeID int identity(1,1) NOT NULL,
  PropertyType varchar(20)
);
GO
INSERT INTO PropertyTypes(PropertyType)
VALUES(N'Condominium');
GO
INSERT INTO PropertyTypes(PropertyType)
VALUES(N'Single Family');
GO
INSERT INTO PropertyTypes(PropertyType)
VALUES(N'Townhouse');
GO
INSERT INTO PropertyTypes(PropertyType)
VALUES(N'Unknown');
GO

-- =============================================
-- Author:   FunctionX
-- Database: RealEstate2
-- Table:    Conditions
-- =============================================
USE RealEstate2;
GO
CREATE TABLE Conditions
(
  ConditionID int identity(1,1) NOT NULL,
  Condition varchar(20)
);
GO
INSERT INTO Conditions(Condition)
VALUES(N'Excellent');
GO
INSERT INTO Conditions(Condition)
VALUES(N'Good');
GO
INSERT INTO Conditions(Condition)
VALUES(N'Bad Shape');
GO
INSERT INTO Conditions(Condition)
VALUES(N'Highly Damaged');
GO

-- =============================================
-- Author:   FunctionX
-- Database: RealEstate2
-- Table:    Properties
-- =============================================
CREATE TABLE Properties
(
  PropertyID int identity(1,1) NOT NULL,
  PropertyNumber char(6),
  Address varchar(100),
  City varchar(50),
  State char(2),
  ZIPCode varchar(12),
  PropertyTypeID int,
  ConditionID int,
  Bedrooms smallint,
  Bathrooms float,
  FinishedBasement bit,
  IndoorGarage bit,
  Stories smallint,
  YearBuilt smallint,
  MarketValue money
);
GO

INSERT INTO Properties(PropertyNumber, Address, City, State,
  ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms,
  FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)
VALUES(N'524880', N'1640 Lombardo Ave', N'Silver Spring', N'MD',
  N'20904', 2, 2, 4, 2.5, 3, 1, 3, 1995, 495880.00);
GO
INSERT INTO Properties(PropertyNumber, Address, City, State,
  ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms,
  FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)
VALUES(N'688364', N'10315 North Hacht Rd', N'College Park', N'MD',
       N'20747', 2, 1, 4, 3.5, 3,
       1, 2, 2000, 620724.00);
GO
INSERT INTO Properties(PropertyNumber, Address, City, State,
  ZIPCode, PropertyTypeID, ConditionID, FinishedBasement,
  Stories, MarketValue)
VALUES(N'611464', N'6366 Lolita Drive', N'Laurel', N'MD',
       N'20707', 2, 2, 1, 2, 422625.00);
GO
INSERT INTO Properties(Address, City, PropertyTypeID,
  Bedrooms, MarketValue)
VALUES(N'9002 Palasko Hwy', N'Tysons Corner',
       1, 2, 422895.00);
GO
INSERT INTO Properties(PropertyNumber, State,
  ZIPCode, Bedrooms, YearBuilt, MarketValue)
VALUES(N'420115', N'DC',
       N'20011', 2, 1982, 312555);
GO     
INSERT INTO Properties(PropertyNumber, City, ZIPCode,
 PropertyTypeID, Bedrooms, YearBuilt, MarketValue)
VALUES(N'917203', N'Alexandria', N'22024',
       2, 3, 1965, 345660.00);
GO
INSERT INTO Properties(PropertyNumber, Address, City, State,
  PropertyTypeID, ConditionID, Bedrooms, Bathrooms, MarketValue)
VALUES(N'200417', N'4140 Holisto Crt', N'Germantown', N'MD',
       1, 1, 2, 1, 215495.00);
GO
INSERT INTO Properties(City, State, PropertyTypeID, ConditionID,
  Bedrooms, Bathrooms,  YearBuilt, MarketValue)
VALUES(N'Rockville', N'MD', 1, 2, 2, 2, 1996, 436885.00);
GO
INSERT INTO Properties(PropertyNumber, Address, City, State,
  ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms,
  FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)
VALUES(N'927474', N'9522 Lockwood Rd', N'Chevy Chase', N'MD',
       N'20852', 3, 3, 3, 2.5, 3, 0, 3,
       1992, 415665.00);
GO
INSERT INTO Properties(PropertyNumber, Address, City, State,
  ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms,
  FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)
VALUES(N'207850', N'14250 Parkdoll Rd', N'Rockville', N'MD',
       N'20854', 3, 2, 3, 2.5, 2, 1, 2,
       1988, 325995.00);
GO    
INSERT INTO Properties(City, PropertyTypeID, Bedrooms,
  YearBuilt, MarketValue)
VALUES(N'Washington', 3, 4, 1975, 366775.00);
GO
INSERT INTO Properties(PropertyNumber, Address, City, State,
  ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms,
  YearBuilt, MarketValue)
VALUES(N'288540', N'10340 Helmes Street #408', N'Silver Spring', N'MD',
       N'20906', 1, 2, 1, 1, 2000, 242775.00);
GO
INSERT INTO Properties(PropertyNumber, Address, City, State,
  ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms,
  FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)
VALUES(N'247472', N'1008 Coppen Street', N'Silver Spring', N'MD',
       N'20906', 2, 1,
       3, 3, 3, 1, 3, 1996, 625450.00);
GO
INSERT INTO Properties(City, ZIPCode, PropertyTypeID, 
   Stories, YearBuilt, MarketValue)
VALUES(N'Chevy Chase', N'20956', 2, 
       3, 2001, 525450.00);
GO
INSERT INTO Properties(Address, City, State,
  PropertyTypeID, ConditionID, Bedrooms, MarketValue)
VALUES(N'686 Herod Ave #D04', N'Takoma Park', N'MD',
       1, 1, 2, 360885.00);
GO
INSERT INTO Properties(PropertyNumber, Address, City, State,
  ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms,
  FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)
VALUES(N'297446', N'14005 Sniders Blvd', N'Laurel', N'MD',
       N'20707', 3, 4,
       4, 1.5, 3, 1, 2, 2002, 412885.00);
GO
INSERT INTO Properties(City, ZIPCode, ConditionID, Bedrooms,
  Stories, YearBuilt)
VALUES(N'Silver Spring', N'20905', 2,
       4, 2, 1965);
GO
INSERT INTO Properties(PropertyNumber, Address, City, State,
  ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms,
  FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)
VALUES(N'924792', N'680 Prushia Rd', N'Washington', N'DC',
       N'20008', 2, 2,
       5, 3.5, 3, 0, 3, 2000, 555885.00);
GO
INSERT INTO Properties(PropertyNumber, Address, City, State,
  ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms,
  FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)
VALUES(N'294796', N'14688 Parrison Street', N'College Park', N'MD',
       N'20742', 2, 1,
       5, 2.5, 2, 1, 2, 1995, 485995.00);
GO
INSERT INTO Properties(City, State, PropertyTypeID, ConditionID,
  Bedrooms, Bathrooms,  YearBuilt, MarketValue)
VALUES(N'Rockville', N'MD', 1, 2, 1, 1, 1996, 418885.00);
GO
INSERT INTO Properties(PropertyNumber, Address, City, State,
  ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms,
  YearBuilt, MarketValue)
VALUES(N'811155', N'10340 Helmes Street #1012', N'Silver Spring',
       'MD', N'20906', 1, 2,
       1, 1, 2000, 252775.00);
GO
INSERT INTO Properties(PropertyNumber, Address, City, State,
  ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms,
  FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)
VALUES(N'447597', N'4201 Vilamar Ave', N'Hyattsville', N'MD',
       N'20782', 3, 1,
       3, 2, 2, 1, 3, 1992, 365880.00);
GO
INSERT INTO Properties(Address, ZIPCode, Bathrooms)
VALUES(N'1622 Rombard Str', 20904, 2.5);
GO
INSERT INTO Properties(City, State, PropertyTypeID, ConditionID,
  Bedrooms, Bathrooms,  YearBuilt, MarketValue)
VALUES(N'Rockville', N'MD', 1, 2, 1, 1, 1996, 420555.00);
GO
INSERT INTO Properties(PropertyNumber, Address, City, State,
  ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms,
  FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)
VALUES(N'297415', N'980 Phorwick Street', N'Washington', N'DC',
       N'20004', 2, 2,
       4, 3.5, 3, 3, 1, 2004, 735475.00);
GO
INSERT INTO Properties(PropertyNumber, Address, City, State,
  ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms,
  FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)
VALUES(N'475974', N'9015 Marvin Crow Ave', N'Gaithersburg', N'MD',
       N'20872', 2, 4,
       4, 2.5, 3, 1, 1, 1965, 615775.00);
GO
INSERT INTO Properties(PropertyNumber, Address, City, State,
  ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms,
  FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)
VALUES(N'836642', N'3016 Feldman Court', N'Rockville', N'MD',
       N'20954', 2, 3,
       5, 3, 3, 1, 3, 1960, 528555.00);
GO
INSERT INTO Properties(Address, City, ZIPCode, PropertyTypeID,
  Bedrooms, Bathrooms, YearBuilt, MarketValue)
VALUES(N'2444 Arielson Rd', N'Rockville', N'20854', 
	1, 2, 1, 1996, 475555.00);
GO

INSERT INTO Properties(City, State, PropertyTypeID, Stories)
VALUES(N'Rockville', N'MD',
       3, 1);
GO
INSERT INTO Properties(PropertyNumber, Address, City, State,
  ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms,
  FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)
VALUES(N'208304', N'7307 Everett Hwy', N'Washington', N'DC',
       N'20012', 3, 1,
       2, 2.5, 2, 0, 4, 2006, 420550.00);
GO
INSERT INTO Properties(PropertyNumber, Address, City, State,
  ZIPCode, PropertyTypeID, ConditionID, Bedrooms,
  Bathrooms, YearBuilt, MarketValue)
VALUES(N'644114', N'10340 Helmes Street#1006', N'Silver Spring',
       'MD', N'20906', 1, 2,
       2, 2, 2000, 258445.00);
GO
INSERT INTO Properties(PropertyNumber, Address, City, State,
  ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms,
  FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)
VALUES(N'937966', N'7303 Warfield Court', N'Tysons Corner', N'VA',
       '22131', 2, 2,
       3, 2.5, 3, 1, 4, 2006, 825775.00);
GO
INSERT INTO Properties(City, ZIPCode, ConditionID, Bedrooms, 
  Stories, YearBuilt)
VALUES(N'Fairfax', N'22232', 2, 3, 3, 1985);
GO
INSERT INTO Properties(PropertyNumber, Address, City, State,
  ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms,
  FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)
VALUES(N'297497', N'12401 Conniard Ave', N'Takoma Park', N'MD',
       N'20910', 3, 2,
       3, 2.5, 3, 1, 3, 2004, 280775.00);
GO
INSERT INTO Properties(PropertyNumber, City, ZIPCode,
  PropertyTypeID, ConditionID, Bedrooms, Bathrooms,
  YearBuilt, Stories, MarketValue)
VALUES(N'855255', N'Laurel', N'20707', 2,
       4, 3, 2, 1962, 2, 342805.00);
GO
INSERT INTO Properties(PropertyNumber, Address, City, State,
  ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms,
  FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)
VALUES(N'469750', N'6124 Falk Rd', N'Arlington', N'VA',
       '22031', 2, 4,
       4, 3.5, 3, 1, 1, 1982, 635995.00);
GO
INSERT INTO Properties(PropertyNumber, Address, City, State,
  ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms,
  FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)
VALUES(N'826927', N'5121 Riehl Ace', N'Fairfax', N'VA',
       '22232', 3, 1,
       3, 1.5, 2, 0, 1, 2002, 325620.00);
GO
INSERT INTO Properties(City, ZIPCode, PropertyTypeID, Bedrooms, 
  Bathrooms, MarketValue)
VALUES(N'Silver Spring', N'20906', 1, 2, 2, 335655.00);
GO

INSERT INTO Properties(PropertyNumber, Address, City, State,
  ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms,
  FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)
VALUES(N'287064 ', N'9533 Pensulian Rd', N'Silver Spring', N'MD',
       N'20904', 2, 3,
       3, 1.5, 3, 1, 2, 1992, 485775.00);
GO
INSERT INTO Properties(PropertyNumber, City, ZIPCode,
  PropertyTypeID, ConditionID, Bedrooms, YearBuilt, Stories)
VALUES(N'724001 ', N'705 Helios Ave', N'20004',
       3, 3, 3, 1974, 4);
GO
INSERT INTO Properties(PropertyNumber, Address, City, State,
  ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms,
  FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)
VALUES(N'209275', N'944 Fryer Ave', N'Chevy Chase', N'MD',
       N'20852', 2, 1,
       5, 2.5, 3, 0, 2, 2002, 625665.00);
GO
INSERT INTO Properties(PropertyNumber, Address, City, State,
  ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms,
  FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)
VALUES(N'204759', N'1950 Galego Street', N'Germantown', N'MD',
       N'20874', 2, 1,
       4, 3.5, 2, 1, 4, 2007, 428665.00);
GO
INSERT INTO Properties(PropertyNumber, Address, City, State,
  ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms,
  FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)
VALUES(N'937259', N'12366 Fowler Ave', N'Alexandria', N'VA',
       '22031', 3, 2,
       3, 1.5, 3, 1, 3, 2007, 402815.00);
GO
  4. Press F5 to execute

Join Creation

Equipped with the necessary tables and their columns, you can create the join. To do this in the SQL Server Management Studio, in the Object Explorer, right-click the database and click open a Query window. Then:

  • On the main menu, click Query -> Query Design in Editor...
  • Right-click somewhere in the Query window and click Query Design in Editor

Any of these actions would display the Table window:

Query Designer

Because the foundation of a join lies on at least two tables, you should add them. To do this, you use the Add Table dialog box. If you had closed the Add Table dialog box, you can right-click the top section of the Query Designer and click Add Table...

Query Designer

On the Add Table dialog box:

  • You can click the table's name and click Add
  • You can double-click a table

After adding the tables, click Close.

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

Query Designer

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

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

SELECT WhatColumn(s)
FROM ChildTable
TypeOfJoin ParentTable
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 Persons
TypeOfJoin ParentTable
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:

SELECT WhatColumn(s)
FROM Persons
TypeOfJoin Genders
ON Persons.GenderID = Genders.GenderID

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:

SELECT WhatColumn(s)
FROM Persons
TypeOfJoin Genders
ON Persons.GenderID = Genders.GenderID

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 Genders
ON Persons.GenderID = Genders.GenderID

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:

SELECT LastName, FirstName, Gender
FROM Persons
TypeOfJoin Genders
ON Persons.GenderID = Genders.GenderID

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. Here is an example:

SELECT LastName, FirstName, Persons.GenderID,
       Genders.GenderID, Gender
FROM Persons
TypeOfJoin Genders
ON Persons.GenderID = Genders.GenderID

In fact, to make your code easier to read, you should qualify the name of each column of your SELECT statement. Here is an example:

SELECT Persons.LastName, Persons.FirstName, Persons.GenderID,
       Genders.GenderID, Genders.Gender
FROM Persons
TypeOfJoin Genders
ON Persons.GenderID = Genders.GenderID

If you have a schema, you can use it to qualify a (each) table. Here is an example:

SELECT dbo.Persons.LastName, dbo.Persons.FirstName, dbo.Persons.GenderID,
       dbo.Genders.GenderID, dbo.Genders.Gender
FROM dbo.Persons
TypeOfJoin dbo.Genders
ON dbo.Persons.GenderID = dbo.Genders.GenderID

You can also use an alias name for each table. Here is an example:

SELECT pers.LastName, pers.FirstName, pers.GenderID,
       Genders.GenderID, Genders.Gender
FROM Persons pers
TypeOfJoin Genders
ON pers.GenderID = Genders.GenderID

Practical LearningPractical Learning: Preparing a Join
  1. Click the top section of the window that contains code
  2. Press Ctrl + A to select everything
  3. Type USE RealEstate2; and press Enter
  4. Type GO and press Enter
  5. On the main menu, click Query -> Query Design in Editor
  6. In the Add Table dialog box, click Properties and click Add
  7. In the Add Table dialog box, double-click PropertyTypes
  8. Click Close
 
 
 

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, 
             Genders.GenderID, Genders.Gender
FROM Persons
CROSS JOIN Genders
GO

By default, in the SQL Server Management Studio, after you have just added a table to another one (if no relationship was already established between both tables), the query would be automatically make a cross join. All you have to do is to select the needed columns:

After selecting the columns, you can click OK and 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:

Genders

Notice that some records in the Persons table don't have an entry for the GenderID 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, from the SQL Server Management Studio, 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 create it or edit the SQL statement. Consider the following:

Notice that, because no relationship is 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:

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.GenderID,
       Genders.GenderID AS [Gender ID], Genders.Gender
FROM   Persons INNER JOIN Genders ON Persons.GenderID = Genders.GenderID

After creating the join, in the Diagram section, a line would be created to join the tables:

Join

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 gender of each Persons record, we would not need the GenderID column from the Genders table. Here is an example:

Join

Join

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.

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

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:
     
    Joining
  2. Release the mouse
  3. On the tables, click the check boxes of the following fields: PropertyType, City, Bedrooms, Bathrooms, YearBuilt, and MarketValue
     
    Join
  4. Click OK
  5. On the SQL Editor toolbar, click the Execute button Execute to see the result
     
    Join
  6. Select the code that was added (from SELECT to PropertyTypes.PropertyTypeID) and press Delete
  7. On the main menu, click Query -> Design Query in Editor...
  8. In the Add Table dialog box, double-click Properties and double-click PropertyTypes
  9. Click Close
  10. To create an inner join, from the PropertyTypes table, drag PropertyTypeID and drop it on the PropertyTypeID field of the Properties table:
     
    Joining
  11. Release the mouse
  12. On the tables, click the check boxes of the following fields: PropertyType, City, Bedrooms, Bathrooms, YearBuilt, and MarketValue

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, 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:

SELECT  Persons.PersonID, Persons.FirstName, Persons.LastName, 
       	Genders.GenderID, Genders.Gender
FROM Persons
LEFT OUTER JOIN Genders
ON Persons.GenderID = Genders.GenderID
GO

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:

Join

Join

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 GenderID 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 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 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 Genders.

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:

SELECT Persons.PersonID, Persons.FirstName, Persons.LastName, 
       Genders.GenderID, Genders.Gender
FROM Persons
RIGHT OUTER JOIN Genders
ON Persons.GenderID = Genders.GenderID
GO

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:

Join

Join

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. Also, notice that there are no NULL records in the Gender.

Practical LearningPractical Learning: Getting Non-NULL Records
  1. 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
     
    Join
  2. Click OK
  3. On the SQL Editor toolbar, click the Execute button Execute
     
    Join
  4. Select the code that was added (from SELECT to PropertyTypes.PropertyTypeID) and press Delete
  5. On the main menu, click Query -> Design Query in Editor...
  6. In the Add Table dialog box, double-click Properties and double-click PropertyTypes
  7. Click Close
  8. To create an inner join, from the PropertyTypes table, drag PropertyTypeID and drop it on the PropertyTypeID field of the Properties table:
     
    Joining
  9. Release the mouse
  10. On the tables, click the check boxes of the following fields: PropertyType, City, Bedrooms, Bathrooms, YearBuilt, and MarketValue
    11.

 

Full 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 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:

SELECT Persons.PersonID, Persons.FirstName, Persons.LastName, 
       Genders.GenderID, Genders.Gender
FROM Persons
FULL OUTER JOIN Genders
ON Persons.GenderID = Genders.GenderID
GO

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

Join

Join

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 SQL Server Management 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: 

SELECT Persons.PersonID, Persons.FirstName, Persons.LastName,
       Genders.GenderID, Genders.Gender
FROM   Persons LEFT OUTER JOIN
       Genders ON Persons.GenderID = Genders.GenderID
WHERE  Genders.Gender = N'female';
GO

Join

This would produce:

Join

Practical LearningPractical Learning: Analyzing Data Involving Joins

  1. To see a list of only townhouses, change the statement in the SQL section as follows:
      
    SELECT PropertyTypes.PropertyType, Properties.City, 
       Properties.State, Properties.ZIPCode, Properties.Bedrooms, 
       Properties.Bathrooms, Properties.Stories, Properties.MarketValue
FROM   Properties RIGHT OUTER JOIN PropertyTypes 
ON     Properties.PropertyTypeID = PropertyTypes.PropertyTypeID
WHERE  (PropertyTypes.PropertyTypeID = 3)
  2. On the SQL Editor toolbar, click the Execute SQL button Execute SQL
  3. To get a list of townhouses and single families, change the SQL statement as follows:
      
    SELECT PropertyTypes.PropertyType, Properties.MarketValue, 
       Properties.City, Properties.State, Properties.Bedrooms,
       Properties.YearBuilt, Properties.Bathrooms, 
       Properties.FinishedBasement, Properties.Stories
FROM   Properties RIGHT OUTER JOIN PropertyTypes 
ON     Properties.PropertyTypeID = PropertyTypes.PropertyTypeID
WHERE  (PropertyTypes.PropertyTypeID IN (2, 3))
  4. On the SQL Editor toolbar, click the Execute SQL button Execute SQL
  5. To get a list of single families arranged in chronological order starting with the newest, change the SQL statement as follows:
      
    SELECT PropertyTypes.PropertyType,
       Properties.City, Properties.State, Properties.ZIPCode,
       Properties.Bedrooms, Properties.Bathrooms, Properties.Stories, 
       Properties.YearBuilt, Properties.MarketValue
FROM   Properties RIGHT OUTER JOIN PropertyTypes 
ON     Properties.PropertyTypeID = PropertyTypes.PropertyTypeID
WHERE  (PropertyTypes.PropertyTypeID = 2)
ORDER BY Properties.YearBuilt DESC
  6. Right-click the table and click Execute SQL
  7. To get a list of properties that cost between $350,000 and $425000, change the SQL statement as follows:
      
    SELECT Properties.PropertyNumber, PropertyTypes.PropertyType, 
       Properties.MarketValue, Properties.City, Properties.State,
       Properties.Bedrooms, Properties.YearBuilt
FROM   Properties RIGHT OUTER JOIN PropertyTypes
ON     Properties.PropertyTypeID = PropertyTypes.PropertyTypeID
WHERE  (Properties.MarketValue BETWEEN 350000 AND 475000)
  8. On the SQL Editor toolbar, click the Execute SQL button Execute SQL
  9. To get a list of single family homes in Virginia arranged in chronological and only if the property number is known, change the SQL statement as follows:
      
    SELECT Properties.PropertyNumber,
       PropertyTypes.PropertyType, Properties.MarketValue, Properties.City,
       Properties.State, Properties.Bedrooms, 
       Properties.FinishedBasement, Properties.YearBuilt
FROM   Properties RIGHT OUTER JOIN PropertyTypes 
ON     Properties.PropertyTypeID = PropertyTypes.PropertyTypeID
WHERE  (PropertyTypes.PropertyTypeID = 2) AND 
       (Properties.PropertyNumber IS NOT NULL) AND
       (Properties.State = N'VA')
ORDER BY Properties.YearBuilt DESC
  10. On the SQL Editor toolbar, click the Execute SQL button Execute SQL
  11. To get a list of properties in southern Maryland but that cost less than $400,000, change the SQL statement as follows:
      
    SELECT Properties.PropertyNumber, PropertyTypes.PropertyType, 
       Properties.MarketValue, Properties.City, Properties.State, 
       Properties.Bedrooms, Properties.YearBuilt
FROM   Properties RIGHT OUTER JOIN PropertyTypes 
ON     Properties.PropertyTypeID = PropertyTypes.PropertyTypeID
WHERE  (Properties.MarketValue < 400000) AND
       (Properties.ZIPCode BETWEEN N'20500' AND N'21000')
  12. On the SQL Editor toolbar, click the Execute SQL button Execute SQL
 
 
   
 
Previous Copyright © 2007-2009 FunctionX, Inc. Next