Home

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 Practical Learning: Using Cross Joins

  1. Start Microsoft Visual C++ or Visual Studio .NET and create a Windows Forms Application named CarInventory4
  2. To create a data adapter, in the Data section of the Toolbox, click SqlDataAdapter and click the form
  3. In the first page of the wizard, click New Connection...
  4. In the top text box of the Connection, select or enter the name of the computer in which you created the above CarInventory2 database
  5. In the Select the Database combo box, select CarInventory2 and click Test Connection
     
  6. Click OK twice
  7. Back in the first page of the wizard, click Next
  8. In the second page of the wizard, accept the Use SQL Statement option and click Next
  9. In the third page of the wizard, click Query Builder
  10. In the Add Table dialog box, double-click CarsCategories and Cars then click Close
  11. In the CarsCategories section, click the check box on the left of CarCatetory
  12. In the Cars list, click the check boxes on the left of TagNumber, Make, Model, CarYear, and Available
  13. Right-click somewhere in the window and click Run
     
  14. Notice the CROSS JOIN expression in the statement
  15. Click OK
  16. In the third page of the wizard, click Next
  17. Read the Primary Key Columns Missing message box and click No then click Finish
  18. On the main menu, click Data -> Generate Dataset...
  19. In the Generate Dataset dialog box, click the New radio button and replace the name with dsCars
  20. Click OK
  21. From the Windows Forms section of the Toolbox, add a Button and a DataGrid to the form
  22. Set the data grid's DataSource property to dsCars.CarsCategoperties
     
  23. Change the button's name to btnLoad
  24. Double-click the button and implement its Click event as follows:
     
    private void btnLoad_Click(object sender, System.EventArgs e)
    
    {
    
    	 this.sqlDataAdapter1.Fill(this.dsCars1);
    
    }
    
    
    
    private void btnClose_Click(object sender, System.EventArgs e)
    
    {
    
    	Close();
    
    }
  25. Execute the application and click the Load button
     
  26. Close the form
 

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 Practical Learning: Using Inner Joins

  1. Return to the SQL Query Analyzer and re-open the Cars.sql file from the File menu
  2. Change all occurrences of CategoryID to CarCategoryID
     
    -- =============================================
    
    -- Database: CarInventory2
    
    -- Table:    Cars
    
    -- =============================================
    
    USE CarInventory2
    
    GO
    
    IF EXISTS (SELECT name 
    
    	   FROM   sysobjects 
    
    	   WHERE  name = N'Cars' 
    
    	   AND    type = 'U')
    
        DROP TABLE Cars
    
    GO
    
    
    
    CREATE TABLE Cars (
    
    CarID int PRIMARY KEY Identity(1, 1) NOT NULL,
    
    TagNumber varchar(10),
    
    Make varchar(50),
    
    Model varchar(50),
    
    CarYear varchar(5),
    
    CarCategoryID int,
    
    HasK7Player bit DEFAULT(0),
    
    HasCDPlayer bit DEFAULT(0),
    
    HasDVDPlayer bit DEFAULT(0),
    
    Available bit DEFAULT(1))
    
    GO
    
    
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CarCategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('HAD-722', 'Hyundai', 'Accent', '2003', 1, 0, 0, 1, 1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CarCategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('CDJ-85F', 'Mercury', 'GrandMarquis', 1998,	4,	0,	0,	1,	0)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CarCategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('FGE-920', 'Ford', 'Escape',	2004, 6,	0,	0,	0,	1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CarCategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('GMM-186', 'Mercury', 'Grand Marquis', 2001,	4,	0,	1,	1,	1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CarCategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('GHL-22G', 'Lincoln', 'TownCar', 1998,	4,	0,	1,	1,	1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CarCategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('HHS-382', 'Hyundai', 'Sonata', 2002,	2,	0,	0,	0,	1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CarCategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('LBN-755', 'Lincoln', 'Navigator', 2000,	6,	1,	1,	1,	0)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CarCategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('FDX-984', 'Kia', 'Sephia',	2002,	2,	0,	1,	1,	1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CarCategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('AFS-888', 'Ford', 'SportTrac', 1998,	7,	0,	0,	1,	1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CarCategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('CAM-422', 'Chevrolet', 'Metro',	2000,	1,	0,	0,	0,	0)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CarCategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('QFH-608', 'Ford', 'F150', 2001,	7,	0,	0,	1,	0)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CarCategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('DCC-713', 'Chevrolet', 'Camaro',	2001,	3,	1,	0,	0,	1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CarCategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('LFT-268', 'Ford', 'Club Wagon',	1998,	5,	0,	1,	1,	1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CarCategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('PBR-69G', 'Buick', 'Regal',	2000, 4,	0,	1,	1,	0)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CarCategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('DBP-832', 'Buick', 'Park Avenue',	2001,	4,	0,	0,	1,	0)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CarCategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('FM-685', 'Ford', 'Mustang Convertible',	2002,	3,	1,	0,	0,	1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CarCategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('FAX-48T', 'Mecury', 'Villager',	1999,	5,	1,	0,	0,	1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CarCategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('DPM-42', 'Pontiac',	'Mountana',	2002,	5,	0,	1,	1,	0)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CarCategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('AFW-928', 'Ford', 'Windstar Minivan GL',	2001,	5,	0,	0,	0,	1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CarCategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('UFX-963', 'Cadillac', 'Sedan de Ville',	1998,	4,	1,	1,	1,	0)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CarCategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('KCR-656', 'Chevrolet', 'Blazer',	2001,	6,	0,	0,	1,	0)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CarCategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('LLT-358', 'Lincoln', 'City Car',	2004,	4,	0,	1,	1,	0)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CarCategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('RBL-618', 'Buick', 'LeSabre',	2002,	4,	0,	1,	1,	1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CarCategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('WFV-688', 'Ford', 'E350',	2000,	8,	0,	0,	0,	0)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CarCategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('GCV-557', 'Chevrolet', 'Camaro',	1999,	3,	0,	1,	0,	1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CarCategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('TPG-905', 'Pontiac', 'Grand Am',	2002,	2,	0,	0,	0,	1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CarCategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('JYW-682', 'Jeep', 'Wrangler',	2003,	6,	1,	0,	0,	1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CarCategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('DFR-214', 'Ford', 'Ranger',	2000,	7,	0,	0,	1,	1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CarCategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('RKR-670', 'Kia', 'Rio',	2002,	1,	1,	0,	0,	1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CarCategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('YJC-498', 'Ford', 'Escort',	1996,	1,	0,	0,	0,	1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CarCategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('QDC-922', 'Dodge', 'Caravan',	2002,	5,	1,	0,	0,	0)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CarCategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('DSS-374', 'Hyundai', 'Accent',	1996,	1,	0,	0,	0,	1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CarCategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('SCC-262', 'Chrysler', 'Concorde',	2002,	4,	0,	1,	1,	1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CarCategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('BDI-588', 'Dodge', 'Intrepid',	2004,	2,	1,	0,	0,	0)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CarCategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('MCM-952', 'Jaguar', 'S-Type',	1999,	4,	0,	1,	1,	1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CarCategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('HDN-167', 'Dodge', 'Neon',	2002,	1,	0,	0,	0,	1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CarCategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('EDS-664', 'Ford', 'Explorer XLT',	1998,	6,	1,	0,	0,	1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CarCategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('C362344', 'Dodge', 'Caravan', 2002,	5,	1,	0,	0,	0)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CarCategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('753723', 'Honda', 'Civic', 1999,	2,	0,	0,	0,	1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CarCategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('846884', 'Chrysler', 'Concorde', 2002,	4,	0,	1,	1,	1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CarCategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('DK-3622', 'Dodge', 'Intrepid', 2004,	2,	1,	0,	0,	0)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CarCategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('3826DD', 'Chrysler', '300M',	2004, 3,	0,	0,	1,	1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CarCategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('683678', 'Nissan', 'Sentra',	1996,	1,	0,	0,	0,	1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CarCategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('PWEY22', 'Dodge', 'Stratus',	2003,	3,	1,	0,	0,	1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CarCategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('D276332', 'Hyundai', 'Elantra', 1996,	2,	1,	0,	1,	1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CarCategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('CDJ-855', 'Jeep', 'Cherokee', 2002,	6,	1,	0,	1,	0)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CarCategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('273222G', 'Ford', 'Minivan GL', 2001,	5,	0,	1,	1,	1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CarCategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('DF4-888', 'Mazda', 'Protégé', 1997,	1,	0,	0,	1,	1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CarCategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('ACV-557', 'Chevrolet', 'Cavalier',	2003,	2,	0,	0,	1,	1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CarCategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('PPL-498', 'Jeep',	'Grand Cherokee Laredo',	2001,	6,	0,	1,	0,	0)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CarCategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('268952', 'Chrysler',	'300M',	2004,	3,	0,	0,	1,	1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CarCategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('F6348T', 'Ford',	'Explorer XLT',	2000,	6,	1,	0,	1,	1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CarCategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('C367264', 'Ford',	'Taurus',	2002,	4,	1,	0,	1,	1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CarCategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('KKL-348', 'Toyota', 'Corolla',	1998,	2,	0,	0,	0,	0)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CarCategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('827835V', 'Dodge', 'Neon',	2002,	1,	0,	0,	0,	1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CarCategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('KWL-888', 'Ford', 'Focus',	2002,	1,	0,	0,	1,	1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CarCategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('382963', 'Ford', 'Contour',	1999,	2,	1,	1,	1,	0)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CarCategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('SWW-664', 'Dodge', 'Stratus',	2003,	3,	1,	0,	0,	1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CarCategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('DLO-723', 'Mercury', 'Mystique',	1998,	3,	1,	0,	0,	0)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CarCategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('GTE-332', 'Daewoo', 'Lanos',	2000,	2,	1,	0,	1,	1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CarCategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('D82963', 'Ford', 'Expedition XLS',	2004,	6,	1,	1,	1,	0)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CarCategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('362-723', 'Dodge', 'Sebring',	2002,	3,	1,	0,	0,	0)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CarCategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('DDG-984', 'Fort', 'Expedition XLT',	2000,	6,	0,	1,	1,	1)
    
    GO
    
    INSERT INTO Cars(TagNumber, Make, Model, CarYear, CarCategoryID, HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
    
    VALUES('D287323', 'Dodge', 'Sebring',	2002,	3,	1,	0,	0,	0)
    
    GO
  3. Execute the statement and return to your programming environment
  4. Display the form. Under the form, right-click sqlDataAdapter1 and click Configure Data Adapter...
  5. In the first page of the wizard, click Next
  6. In the second page of the wizard, accept the Server.CarInventory2.dbo option in the combo box and click Next
  7. In the third page of the wizard, accept the Use SQL Statement option and click Next
  8. In the fourth page of the wizard, delete the SQL statement and click Query Builder
  9. In the Add Table dialog box, double-click CarsCategories and Cars then click Close
  10. In the CarsCategories section, click the check box on the left of CarCatetory
  11. In the Cars list, click the check boxes on the left of Make, Model, CarYear, and Available
  12. Right-click somewhere in the window and click Run
     
  13. Notice the INNER JOIN expression in the statement
  14. Click OK
     
  15. In the third page of the wizard, click Next
  16. Read the Primary Key Columns Missing message box and click No then click Finish
  17. On the main menu, click Data -> Generate Dataset...
  18. In the Generate Dataset dialog box, accept the Existing radio button and click OK
  19. Execute the application to see the results:
     
  20. Close the form
 

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 Practical Learning: Using Left Outer Joins

  1. Display the form. Under the form, right-click sqlDataAdapter1 and click Configure Data Adapter...
  2. In the first page of the wizard, click Next
  3. In the second page of the wizard, accept the Server.CarInventory2.dbo option in the combo box and click Next
  4. In the third page of the wizard, accept the Use SQL Statement option and click Next
  5. In the fourth page of the wizard, delete the SQL statement and click Query Builder
  6. In the Add Table dialog box, double-click CarsCategories and Cars then click Close
  7. Notice the INNER JOIN in the SQL statement.
    Right-click the line between both tables in the top section of the window and click Select All Rows From CarsCategories
     
  8. Notice now the LEFT OUTER JOIN expression in the SQL statement
  9. In the CarsCategories section, click the check box on the left of CarCatetory
  10. In the Cars list, click the check boxes on the left of Make, Model, CarYear, HasCDPlayer
  11. In the CarsCategories section, click the check boxes on the left of DailyRate and WeekendRate
  12. In the Cars list, click the check box on the left of Available
  13. Right-click somewhere in the window and click Run
     
  14. Click OK
  15. In the fourth page of the wizard, click Next
  16. Read the Primary Key Columns Missing message box and click No then click Finish
  17. On the main menu, click Data -> Generate Dataset...
  18. In the Generate Dataset dialog box, accept the Existing radio button and click OK
  19. Execute the application to see the results:
     
  20. Close the form
 

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 FunctionX, Inc. Next