In this blog we will cover all SQL Server Join related topics with the help of some example, below the list of topics which we have learn in this:
Table Joins/Types of Joins
INNER Joins
Left Outer Join
Right Outer Join
Cross Join
SELF Joins
Multiple Table Joins
Joins with Aggregate Functions
Practice Queries
Table Joins/Types of Joins
By using joins, you can retrieve data from two or more tables based on logical relationships between the tables. Joins indicate how SQL statements should use data from one table to SELECT the rows in another table. A join condition defines the way two tables are related in a query by: ● Specifying the column from each table to be used for the join. A typical join condition specifies a foreign key from one table and its associated key in the other table. ● Specifying a logical operator (for example, = or <>,) to be used in comparing VALUES from the columns.
INNER Joins
An inner join is a join in which the VALUES in the columns being joined are compared using a comparison operator. This inner join is known as an equi-join. It returns all the columns in both tables, and returns only the rows for which there is an equal VALUE in the join column. Inner joins eliminate the rows that do not match with a row from the other table. In the SQL-92 standard, inner joins can be specified in either the FROM or WHERE clause. This is the only type of join that SQL-92 supports in the WHERE clause. Inner joins specified in the WHERE clause are known as old-style inner joins.
EXAMPLE: The following SELECT statement returns the ProductID and Name from the Product table. This is a simple SELECT statement:
Write Exercise Code:
SELECT
ProductID, Name
FROM
Product The following SELECT statement is an example of using INNER JOIN in SELECT statement and returns all data from both Employer and Contact tables that match ContactID. This is because we did not specify field names and used the asters * which brings back all fields.,
Write Exercise Code:
SELECT * FROM Employee AS e
INNER JOIN Contact AS c
ON e.ContactID = c.ContactID
ORDER BY c.LastName Result
EmployeeID NationalIDNumber ContactID LoginID ManagerID
288 481044938 1012 adventure-works\syed0 273
235 6298838 1212 adventure-works\kim1 16 200 398223854 1268 adventure-works\hazem0 148 (Not all columns and rows represented)
The following SELECT statement modifies first SELECT statement and uses an INNER JOIN to return all data from both ProductVendor and Product tables that match ProductID: Write
Exercise Code:
SELECT
ProductVendor.VendorID, Product.ProductID, Name
FROM ProductVendor
INNER JOIN Product
ON ProductVendor.ProductID = Product.ProductID Result
In more common terms, this request could be similar to. “Can you pull a Vendor report that lists the Vendor ID along with the product IDs for products we carry?”
VendorID ProductID Name
83 1 Adjustable Race
57 2 Bearing Ball
85 4 Headset Ball Bearings
(not all rows represented)
The following SELECT statement modifies first SELECT statement and uses an INNER join to return all data from both ProductVendor and Product tables that match ProductID. It also only returns the top 10 records and includes an ORDER BY clause
Write Exercise Code:
SELECT Top 10 ProductVendor.VendorID, Product.ProductID, Name
FROM ProductVendor
INNER JOIN Product
ON ProductVendor.ProductID = Product.ProductID
ORDER BY VendorIDResult
VendorID ProductID Name
1 462 Lower Head Race
2 510 LL Road Rim
2 511 ML Road Rim
(Not all rows represented)
In this example we are joining between the SalesOrderDetail and Product tables. The tables are aliased with the following: SOD for SalesOrderDetail and P for Product. The JOIN logic is based on matching records in the SOD.ProductID and P.ProductID columns. The records are filtered by only returning records with the SOD.UnitPrice greater than 1000. Finally, the result set is returned in order with the most expensive first based on the ORDER BY clause and only the highest 100 products based on the TOP clause.
Write Exercise Code:
SELECT TOP 100
P.ProductID,
P.Name,
P.ListPrice,
P.Size,
P.ModifiedDate,
SOD.UnitPrice,
SOD.UnitPriceDiscount,
SOD.OrderQty,
SOD.LineTotal
FROM SalesOrderDetail SOD
INNER JOIN Product P ON SOD.ProductID = P.ProductID
WHERE SOD.UnitPrice > 1000
ORDER BY SOD.UnitPrice DESC In more common terms, this request could be similar to. “Can you run a Sales Order Detail report where the Unit Price is great than $1000? I’d like to see the Unit Price (have it Sorted highest to lowest), and also show Discount, Order Quantity, and the Line total. Oh, also add the Product list price, size and date last modified to each product in the order detail report “
Result
ProductID Name ListPrice Size ModifiedDate
4749 Road-150 Red,62 3578.2700 62 2004-03-11 10:01:36.827
751 Road-150 Red, 48 3578.2700 48 2004-03-11 10:01:36.827
751 Road-150 Red, 48 3578.2700 48 2004-03-11 10:01:36.827
753 Road-150 Red, 56 3578.2700 56 2004-03-11 10:01:36.827
(Not all columns and rows represented)
The following SELECT statement is an example of using Implicit JOIN in SELECT statement and returns all data from both Productvendor and Product tables that match ProductID:
Write Exercise Code:
SELECT ProductVendor.VendorID, Product.ProductID, Product.Name
FROM ProductVendor, Product
WHERE ProductVendor.ProductID = Product.ProductIDResult
VendorID ProductID Name
83 1 Adjustable Race
57 2 Bearing Ball
85 4 Headset Ball Bearings
50 317 LL Crankarm
84 317 LL Crankarm
50 318 ML Crankarm
(Not all columns and rows represented)
Left Outer Join
Inner joins eliminate the rows that do not match with a row from the other table. Outer joins, however, return all rows from at least one of the tables or views mentioned in the FROM clause, as long as those rows meet any WHERE or HAVING search conditions. All rows are retrieved from the left table referenced with a left outer join, and all rows from the right table referenced in a right outer join. EXAMPLE: To include all contacts regardless if they made an order, use a left outer join. Here is the SQL query and results of the left outer join:
Write Exercise Code:
SELECT Contact.LastName, SalesOrderHeader.SalesOrderID
FROM Contact Left Outer Join SalesOrderHeader
ON Contact.ContactID = SalesOrderHeader.ContactID
ORDER By Contact.LastName ASC Result
Note: the NULL field with Abbas. This implies Abbas doesn’t have any SalesOrder IDs attached to their contact.
LastName SalesOrderID
Abbas NULL
Abel 53459
Abel 71782
Abel 58907
Abel 65157
Abercrombie 44110
(Not all rows and columns displayed)
Right Outer Join
EXAMPLE: Based on the two tables specified in the join clause, all data is returned from the right table. On the left table, the matching data is returned in addition to NULL values where a record exists in the right table but not in the left table. 6To include all sales persons in the results, regardless of whether they are assigned a territory, use a right outer join. Here is the Transact-SQL query and results of the right outer join:
Write Exercise Code
SELECT st.Name AS Territory, sp.SalesPersonID
FROM SalesTerritory st
RIGHT OUTER JOIN SalesPerson sp
ON st.TerritoryID = sp.TerritoryID ;Results
Territory SalesPersonID
NULL 268
Northeast 275
Southwest 276
Central 277
Canada 278
Southeast 279
Northwest 280
(Not all rows displayed)
Cross Join
Cross join is a Cartesian join means Cartesian product of both the tables. This join does not need any condition to join two tables. This join returns records/rows that are multiplication of record number from both the tables means each row on left table will related to each row of right table. The result set contains 170 rows (SalesPerson has 17 rows and SalesTerritory has 10; 17 multiplied by 10 equals 170)
Write Exercise Code:
SELECT p.SalesPersonID, t.Name AS Territory
FROM SalesPerson p
CROSS JOIN SalesTerritory t
ORDER BY p.SalesPersonID; Results
SalesPersonID Territory
268 Northwest
268 Northeast
268 Central
268 Southwest
268 Southeast
268 Canada
(Not all rows displayed)
SELF Joins A table can be joined to itself in a self-join. For example, you can use a self-join to find the Employee and the Manager. Because this query involves a join of the Employee table with itself, the Employee table appears in two roles. To distinguish these roles, you must give the Employee table two different aliases (e1 and e2) in the FROM clause. These aliases are used to qualify the column names in the rest of the query. EXAMPLE: This is an example of the self-join statement: Write Exercise Code:
SELECT e1.ManagerID AS ManagerID, e2.EmployeeID EmployeeID
FROM Employee e1
INNER JOIN Employee e2 ON e2.ManagerID = e1.EmployeeID
ORDER BY e1.ManagerIDResult
ManagerID EmployeeID
NULL 6
NULL 12
NULL 42
NULL 140
NULL 148
NULL 273
3 79
3 114
Now let’s add the employee
Title Write Exercise Code:
SELECT e1.EmployeeID as ManagerID, e1.Title as ManagerTitle, e2.EmployeeID, e2.Title as EmployeeTitle
FROM Employee e1
JOIN Employee e2 ON e2.ManagerID = e1.EmployeeID
WHERE ORDER BY e1.ManagerID Result
In more common terms, this request could be similar to. “Can you run a report for me on all Employees (listing their employee ID, Title), and their manager’s ID and title.”
ManagerID ManagerTitle EmployeeID EmployeeTitle
109 Chief Executive Officer 12 Vice President of Engineering 109 Chief Executive Officer 6 Marketing Manager
109 Chief Executive Officer 42 Information Services Manager 109 Chief Executive Officer 140 Chief Financial Officer
Multiple Table Joins
Although each join specification joins only two tables, the FROM clauses can contain multiple join specifications. This allows many tables to be joined for a single query. EXAMPLE: The ProductVendor table offers a good example of a situation in which joining more than two tables is helpful. The following SQL query finds the names of all products of a particular subcategory and the names of their vendors where ProductSubCategoryID = 15:
Write Exercise Code:
SELECT p.Name As ProductName, v.Name AS VendorName
FROM Product p
INNER JOIN ProductVendor pv ON p.ProductID = pv.ProductID
INNER JOIN Vendor v ON pv.VendorID = v.VendorID
WHERE ProductSubcategoryID = 15
ORDER BY v.NameResult
ProductName VendorName
------------------------------------------------- --------------------
LL Mountain Seat/Saddle Chicago City Saddles
ML Mountain Seat/Saddle Chicago City Saddles
HL Mountain Seat/Saddle Chicago City Saddles
(Not all rows displayed)
Joins with Aggregate Functions
To retrieve a list of all customers and the number of orders that each has placed we can use a join with an aggregate function EXAMPLE:
Write Exercise Code:
Select c.ContactID, c.LastName, Count(s.SalesOrderID) AS OrderID
FROM Contact c Inner Join SalesOrderHeader s ON c.ContactID = s.ContactID
Group BY c.LastName, c.ContactIDResult
contactID LastName OrderID
----------- -------------------------------------------------- --------
1 Achong 7
2 Abel 4
3 Abercrombie 12
4 Acevedo 11
5 Ackerman 4
6 Adams 12
(Not all rows displayed)
Combined Queries
Some queries are combined using the UNION operator. The UNION combines the results of two or more queries INTO a single result set that includes all the rows that belong to all queries in the union. The UNION operation is different from using joins that combine columns from two tables.
The following are basic rules for combining the result sets of two queries by using UNION:
The number and the order of the columns must be the same in all queries.
The data types must be compatible.
EXAMPLE:
The first SELECT statement returns all products with a price of more than 42.2100 The second SELECT statement uses the IN to find all products made by vendors 1001 and 1002. By default the UNION operator removes duplicates. To add duplicates just add the word ALL after UNION To combine these two statements do the following. First write the first SELECT statement and execute:
Write Exercise Code:
SELECT v.ProductID, v.VendorID, v.StandardPrice
FROM ProductVendor v
Where v.StandardPrice <= 42.2100Then write second SELECT statement and execute:
Write Exercise Code:
SELECT v.ProductID, v.VendorID, v.StandardPrice
FROM ProductVendor v
Where v.VendorID IN (50,83)When you combine the two statements using the UNION operator you basically are removing any duplicate records. To sort when you use the UNION operator you have to add only one ORDER BY clause at the end of the second statement. Otherwise you will get an error. To combine these two statements do the following: Write Exercise Code:
SELECT v.ProductID, v.VendorID, v.StandardPrice
FROM ProductVendor v Where v.StandardPrice <= 42.2100
UNION SELECT v.ProductID, v.VendorID, v.StandardPrice
FROM ProductVendor v Where v.VendorID IN (50,83)
order by v.StandardPrice ascResult
ProductID VendorID StandardPrice
325 15 0.2000
326 15 0.2000
873 6 0.8000
352 64 1.1000
355 17 1.3000
To return all records when you combine the two statements you use the UNION ALL operator. To combine these two statements do the following:
Write Exercise Code:
SELECT v.ProductID, v.VendorID, v.StandardPrice
FROM ProductVendor v Where v.StandardPrice <= 42.2100
UNION ALL SELECT v.ProductID, v.VendorID, v.StandardPrice
FROM ProductVendor v Where v.VendorID IN (50,83)
order by v.StandardPrice ascResult
To write the same UNION query above using multiple WHERE conditions you write it as follows:
Write Exercise Code:
SELECT v.ProductID, v.VendorID, v.StandardPrice
FROM ProductVendor v
WHERE v.StandardPrice <= 42.2100 OR v.VendorID IN (50,83)
ORDER BY v.StandardPrice asc Result
ProductID VendorID StandardPrice
325 15 0.2000
326 15 0.2000
873 6 0.8000
352 64 1.1000
355 17 1.3000
352 12 1.4000
Thanks for your support, if you like this and comment in below comment section and if you need any other help related to SQL Server or Any database assignment or project help then you can contact us at realcode4you@gmail.com and get help.
Comments