INNER JOIN
In general, a JOIN operationcombines two or more tables, generating one result set from the information stored in such tables. These tables should have similar columns, commonly foreign keys, which are the ones used in JOIN operations to link tables. Also, as you might have noticed in previous examples, the columns involved in a JOIN condition don't need to have the same name.
An INNER JOIN operation between two tables returns all common rows in these two tables. Specifically, INNER JOIN evaluates the JOIN condition for each row in both tables and if this condition is met, the row is included in the result set. For example, if you want to retrieve information about products and the name of the supplier of each product, the Products table and the Suppliers table must be joined (through an INNER JOIN), thus generating a result set with all the information needed (products and suppliers). Listing 5.7 shows the query that retrieves eachproduct and its supplier.
Code Listing 5.7. Using INNER JOIN to Retrieve Information from Two Tables
|
USE Northwind
SELECT productid, productname, companyname
FROM Products INNER JOIN Suppliers
ON Products.supplierid = Suppliers.supplierid
GO
-- Partial result
productid productname companyname
--------- ------------------------------- ----------------------------------
1 Chai Exotic Liquids
2 Chang Exotic Liquids
3 Aniseed Syrup Exotic Liquids
4 Chef Anton's Cajun Seasoning New Orleans Cajun Delights
5 Chef Anton's Gumbo Mix New Orleans Cajun Delights
6 Grandma's Boysenberry Spread Grandma Kelly's Homestead
7 Uncle Bob's Organic Dried Pears Grandma Kelly's Homestead
8 Northwoods Cranberry Sauce Grandma Kelly's Homestead
9 Mishi Kobe Niku Tokyo Traders
10 Ikura Tokyo Traders
11 Queso Cabrales Cooperativa de Quesos 'Las Cabras'
12 Queso Manchego La Pastora Cooperativa de Quesos 'Las Cabras'
13 Konbu Mayumi's
14 Tofu Mayumi's
15 Genen Shouyu Mayumi's
Figure 5.1 shows a graphical representation of the INNER JOIN performed in Listing 5.7. In this figure, you can see how an INNER JOIN is processed: For every row in the first table, SQL Server goes through the second table trying to find a corresponding row based on the join column (supplierid in this case), and if a row matches,it is returned in the result set.
Figure 5.1. Processing INNER JOIN operations.
Caution
Be aware that columns with NULL values don't match any values, because NULL is not equal to anything. In particular, NULL is not equal to NULL.
Tip
To specify an INNER JOIN operation, you can use either JOIN or INNER JOIN (they're equivalent).
The columns specifiedin a JOIN condition don't necessarily need to have the same data type but, at least, they have to be compatible. Basically, compatible means one of the following two things:
Both columns have the same data type.
If the columns have different data types, the data type of one column can be implicitly converted to the data type of the other column.
For example, when two tables are joined and the JOIN condition has two columns with different data types, SQL Server tries to perform an implicit conversion; otherwise, CAST or CONVERT must be used to perform an explicit conversion. An example of this implicit conversion appears in Listing 5.8. Notice that the data types of the columns in the JOIN condition are different (VARCHAR and INT), thus SQL Server performs an implicit conversionto process the JOIN.
Code Listing 5.8. Performing an with Columns of Different Data Types in the JOIN Condition
|
USE Northwind
CREATE TABLE Parents (
parentid INT IDENTITY(1,1) PRIMARY KEY,
fullname VARCHAR(50),
relationship VARCHAR(50),
employeeid VARCHAR(10))
GO
SET SHOWPLAN_TEXT ON
GO
SELECT lastname, firstname, fullname
FROM employees JOIN Parents
ON employees.employeeid = parents.employeeid
GO
SET SHOWPLAN_TEXT OFF
GO
DROP TABLE Parents
GO
-- Notice that a convert operation is performed in the last line of the
-- execution plan
StmtText
------------------------------------------------
SELECT lastname, firstname, fullname
FROM employees JOIN Parents
ON employees.employeeid = parents.employeeid
(1 row(s) affected)
StmtText
-------------------------------------------------------------------------
|--Nested Loops(Inner Join, OUTER REFERENCES:([Parents].[employeeid]))
|--Clustered Index Scan(OBJECT:([Northwind].[dbo].[Parents].
[PK__Parents__1F98B2C1]))
|--Clustered Index Seek(OBJECT:([Northwind].[dbo].[Employees].
[PK_Employees]),
SEEK:([Employees].[EmployeeID]=Convert([Parents].[employeeid]))
ORDERED FORWARD)
(3 row(s) affected)
The column list of a query that joins tables can reference any of the columns in any of these tables. There are many different ways to show columns in the result set of a JOIN operation. Be aware that when more than one table has a column with the same name, you must qualify the name of the column with the table's name—for example, tablename.columnname. If a column name doesn't have a duplicate in any of the other joined tables, it doesn't have to be qualified with the table name or alias. Listing 5.9 demonstrates how columns with the same name are included in the result set of a JOIN.
Code Listing 5.9. Qualifying the Name of Columns in the Column List
|
USE Northwind
-- Notice that both tables that are being joined contain the regionid column
-- (this is the only column that has to be fully qualified in the query)
SELECT Region.regionid, territorydescription, regiondescription
FROM Territories JOIN Region
ON Territories.regionid = Region.regionid
ORDER BY Region.regionid
GO
-- Partial result
regionid territorydescription regiondescription
----------- ------------------------- -------------------------
1 Westboro Eastern
1 Bedford Eastern
1 Georgetown Eastern
1 Boston Eastern
1 Cambridge Eastern
1 Braintree Eastern
1 Providence Eastern
1 Wilton Eastern
1 Morristown Eastern
1 Edison Eastern
1 New York Eastern
1 New York Eastern
1 Mellvile Eastern
1 Fairport Eastern
If you want to referenceall the columns in a table, this syntax can be used: tablename.*. If you specify only * in the column list, all columns from all tables involved in the query will be returned. These two approaches are shown in Listing 5.10.
Code Listing 5.10. Specifying All Columns from a Table in a Column List Using the * Keyword
|
USE Northwind
SELECT Territories.*
FROM Territories JOIN Region
ON Territories.regionid = Region.regionid
SELECT *
FROM Territories JOIN Region
ON Territories.regionid = Region.regionid
GO
-- Partial results
territoryid territorydescription regionid
--------------- ------------------------- -----------
01581 Westboro 1
01730 Bedford 1
01833 Georgetown 1
02116 Boston 1
02139 Cambridge 1
02184 Braintree 1
02903 Providence 1
03049 Hollis 3
03801 Portsmouth 3
06897 Wilton 1
07960 Morristown 1
08837 Edison 1
TerritoryID TerritoryDescription RegionID RegionID RegionDescription
------------ --------------------- --------- --------- -----------------
01581 Westboro 1 1 Eastern
01730 Bedford 1 1 Eastern
01833 Georgetown 1 1 Eastern
02116 Boston 1 1 Eastern
02139 Cambridge 1 1 Eastern
02184 Braintree 1 1 Eastern
02903 Providence 1 1 Eastern
03049 Hollis 3 3 Northern
03801 Portsmouth 3 3 Northern
06897 Wilton 1 1 Eastern
07960 Morristown 1 1 Eastern
08837 Edison 1 1 Eastern
Table aliases can be used when referencingtables in JOIN operations to make queries easier to read. However, make sure that if an alias is specified, every reference to the table uses the alias; otherwise (if the name of the table is used and an alias was specified), you will get a syntax error. Listing 5.11 illustrates the use of tablealiases in JOIN operations.
Code Listing 5.11. Using Table Aliases in JOIN Operations
|
USE Northwind
-- Notice that the aliases of the tables are used
-- in the column list and in the JOIN condition
SELECT P.productname, C.categoryname
FROM Products P JOIN Categories C
ON P.categoryid = C.categoryid
GO
-- Partial result
productname categoryname
---------------------------------------- ---------------
Chai Beverages
Chang Beverages
Aniseed Syrup Condiments
Chef Anton's Cajun Seasoning Condiments
Chef Anton's Gumbo Mix Condiments
Grandma's Boysenberry Spread Condiments
Uncle Bob's Organic Dried Pears Produce
Northwoods Cranberry Sauce Condiments
Mishi Kobe Niku Meat/Poultry
Ikura Seafood
Queso Cabrales Dairy Products
Queso Manchego La Pastora Dairy Products
Tip
In general, the performance of a JOIN operation can be improved if the columns involved in the JOIN condition are indexed.
A query can involve more thanone JOIN operation; therefore, more than two tables can be joined in a query to generate one result set. In particular, to join more than two tables, specify two tables first, and then specify the remaining tables one by one along with the JOIN condition. As stated earlier, not all columns from all tables have to be specified in the column list; you just have to specify the ones you need.
For example, if you want to know all regions associated with employees in the Northwind database, you must retrieve the territory for each employee first, and then retrieve the region of each territory. This is accomplished by joining the Employees, Employeeterritories, Territories, and Region tables, as shown in Listing 5.12.
Code Listing 5.12. Joining More Than Two Tables
|
USE Northwind
SELECT firstname, lastname, territorydescription, regiondescription
FROM Employees E JOIN Employeeterritories ET
ON E.employeeid = ET.employeeid
JOIN Territories T
ON ET.territoryid = T.territoryid
JOIN Region R
ON T.regionid = R.regionid
GO
-- Partial result
firstname lastname territorydescription regiondescription
---------- -------------------- ----------------------- --------------------
Nancy Davolio Wilton Eastern
Nancy Davolio Neward Eastern
Andrew Fuller Westboro Eastern
Andrew Fuller Bedford Eastern
Andrew Fuller Georgetown Eastern
Andrew Fuller Boston Eastern
Andrew Fuller Cambridge Eastern
Andrew Fuller Braintree Eastern
Andrew Fuller Louisville Eastern
Janet Leverling Atlanta Southern
Janet Leverling Savannah Southern
Janet Leverling Orlando Southern
Janet Leverling Tampa Southern
Margaret Peacock Rockville Eastern
Margaret Peacock Greensboro Eastern
Margaret Peacock Cary Eastern
Steven Buchanan Providence Eastern
Steven Buchanan Morristown Eastern
Steven Buchanan Edison Eastern
Steven Buchanan New York Eastern
Steven Buchanan New York Eastern
Steven Buchanan Mellvile Eastern
Steven Buchanan Fairport Eastern
Internally, a JOIN that involves more than three tables works this way:
A result set is generated joining the first two tables.
This result set is joined with the third table and so on.
The columns specified in the result set are the ones shown in the result set of the JOIN operation.
A JOIN operation can also be used in UPDATE and DELETEstatements. Basically, this allows us to update or delete rows based on information stored in many tables. For example, suppose that you have to increase the price of all products of a certain supplier. In this case, you have to update the Products table and join it with the Suppliers table because the name of the supplier is stored in the Suppliers table and not in the Products table. Listing 5.13 increases by 5 dollars the unitprice of all products of the 'Exotic Liquids' supplier.
Code Listing 5.13. Using JOIN in UPDATE Statements
|
USE Northwind
SELECT productid, unitprice, companyname
FROM Products P JOIN Suppliers S
ON P.supplierid = S.supplierid
WHERE companyname = 'Exotic Liquids'
UPDATE Products
SET unitprice = unitprice + 5
FROM Products P JOIN Suppliers S
ON P.supplierid = S.supplierid
WHERE companyname = 'Exotic Liquids'
SELECT productid, unitprice, companyname
FROM Products P JOIN Suppliers S
ON P.supplierid = S.supplierid
WHERE companyname = 'Exotic Liquids'
GO
productid unitprice companyname
----------- --------------------- ----------------------------------------
1 18.0000 Exotic Liquids
2 19.0000 Exotic Liquids
3 10.0000 Exotic Liquids
(3 row(s) affected)
3 row(s) affected)
productid unitprice companyname
----------- --------------------- ----------------------------------------
1 23.0000 Exotic Liquids
2 24.0000 Exotic Liquids
3 15.0000 Exotic Liquids
(3 row(s) affected)
A JOIN operation can also be used in a DELETE statement in the same way as it is used in an UPDATE statement.