The INNER JOIN is the most frequently used SQL join. Also referred to as an EQUIJOIN, the INNER JOIN returns rows that have matching values in both tables and discards unmatched rows from both tables. Inner join is the default if no join type is specified.
Keeping in mind that an inner join is used to find related data in two tables. On inspecting our AdventureWorks Sample Database, we are tasked to retrieve data about a product and its category from the SalesLT.Product table and SalesLT.ProductCategory table. We will be able to find the relevant product category record for a product based on its ProductCategoryID field; which is a foreign-key in the product table that matches a primary key in the product category table.
SELECT SalesLT.Product.Name AS ProductName, SalesLT.ProductCategory.Name AS Category
FROM SalesLT.Product
INNER JOIN SalesLT.ProductCategory
ON SalesLT.Product.ProductCategoryID = SalesLT.ProductCategory.ProductCategoryID;
This Transact-SQL query is an example of an inner join and as you can see from the result, we are able to return the ProductName from the products table and the corresponding Category from the product category table. Since INNER join is used, any products that do not have corresponding categories, and any categories that do not contain products are omitted from the results.
Keep in Mind:
By default, SQL Server performs an INNER JOIN if only the JOIN keyword is specified. In the above example, if you modify the query and remove the INNER keyword, as follows, the result will still be the same.
SELECT SalesLT.Product.Name AS ProductName, SalesLT.ProductCategory.Name AS Category
FROM SalesLT.Product
JOIN SalesLT.ProductCategory
ON SalesLT.Product.ProductCategoryID = SalesLT.ProductCategory.ProductCategoryID;
This inner join is known as an equijoin - a join based on the testing of equality between two tables. It returns all the columns in both tables (Product and ProductCategory tables), and returns only the rows for which there is an equal value in the join column.
Table aliases is used to simplify a query, especially when multiple joins must be used. Below are two examples of using aliases.
SELECT oh.OrderDate, oh.SalesOrderNumber, p.Name AS ProductName, od.OrderQty, od.UnitPrice, od.LineTotal
FROM SalesLT.SalesOrderHeader AS oh
JOIN SalesLT.SalesOrderDetail AS od
ON od.SalesOrderID = oh.SalesOrderID
JOIN SalesLT.Product AS p
ON od.ProductID = p.ProductID
ORDER BY oh.OrderDate, oh.SalesOrderID, od.SalesOrderDetailID;
The above query retrieves sales order data from the following 3 tables, SalesLT.SalesOrderHeader, SalesLT.SalesOrderDetail, and SalesLT.Product tables
In SQL, there are ways to express joins: explicit join and implicit join.
Explicit join uses the JOIN keyword to specify the table to join, and the ON keyword to specify the predicates for the join.
SELECT p.ProductID, m.Name AS Model, p.Name AS Product
FROM SalesLT.Product AS p
INNER JOIN SalesLT.ProductModel AS m
ON p.ProductModelID = m.ProductModelID
ORDER BY p.ProductID;
Implicit join simply lists the tables for joining (in the FROM clause of the SELECT statement), using commas to separate them.
SELECT p.ProductID, m.Name AS Model, p.Name AS Product
FROM SalesLT.Product AS p
JOIN SalesLT.ProductModel AS m
ON p.ProductModelID = m.ProductModelID
ORDER BY p.ProductID;
Multiple join is a query that contains the same or different join types, which are used more than once. Multiple joins gives you the ability to combine multiple tables.
SELECT od.SalesOrderID, m.Name AS Model, p.Name AS ProductName, od.OrderQty
FROM SalesLT.Product AS p
JOIN SalesLT.ProductModel AS m
ON p.ProductModelID = m.ProductModelID
JOIN SalesLT.SalesOrderDetail AS od
ON p.ProductID = od.ProductID
ORDER BY od.SalesOrderID;
.
-- By default, SQL Server performs an INNER JOIN if only the JOIN keyword is specified.
SELECT p.Name,
sod.SalesOrderID
FROM SalesLT.Product AS p
INNER JOIN SalesLT.SalesOrderDetail AS sod
ON p.ProductID = sod.ProductID
ORDER BY p.Name;
GO
| ** | ||
| ** | ||
Note
