Introducing JOIN in SQL Server

JOIN is used to retrieve data from two or more tables based on the logical relationship between the tables. A join ( JOIN ) typically specifies a foreign key relationship between tables. It determines how two tables are related in a query by:

• Specify the column from each table to be used for the join ( JOIN ). A typical join ( JOIN ) specifies a foreign key from one table and its related key in another.
• Specify a logical operator such as =, <> to be used to compare values from columns.

The JOIN can be specified in the FROM or WHERE clause .

Syntax:

 SELECT <ColumnName1>, <ColumnName2>, ... <ColumnNameN> FROM Table_Name1 AS alias_1 JOIN Table_Name2 AS alias_2 ON alias_1.<RelatedColumn> = alias_2.<RelatedColumn>

Assume that you want to get a list of employee names, last name, and job title from the HumanResources.Employee and Person.Person tables. To get the information of 2 tables, it is necessary to join the 2 tables according to the foreign key association of the BusinessEntiyID column. For example:

 SELECT A.FirstName,A.LastName,B.JobTitle FROM Person.Person A JOIN HumanResources.Employee B ON A.BusinessEntityID = B.BusinessEntityID

Developing this problem further, we have 3 conceptual JOINS types:

• Inner Joins
• Outer Joins
• Self Joins

Inner Join

An inner join is formed when records from two tables are combined only if rows from both tables are matched based on a common column.

Difference between JOIN and INNER JOIN : JOIN returns all rows from tables where the key record of one table is equal to the key records of another table . INNER JOIN selects all rows from both join tables as long as there is a match between the columns

Syntax:

 SELECT <ColumnName1>,<ColumnName2> ... <ColumnNameN> FROM Table_A AS Table_Alias_A INNER JOIN Table_B AS Table_Alias_B ON Table_Alias_A.<RelatedColumn> = Table_Alias_B.<RelatedColumn>

For example:

 SELECT A.FirstName, A.LastName, B.JobTitle FROM Person.Person A INNER JOIN HumanResources.Employee B ON A.BusinessEntityID = b.BusinessEntityID

Outer Join

Outer Joins are join statements that return all rows from at least one of the tables specified in the FROM clause, as long as those rows meet any of the SELECT statement 's WHERE or HAVING conditions. Two commonly used outer join types are as follows:

• Left Outer Join
• Right Outer Join

Left Outer Join

The left outer join returns all records from the left table and only the right matched record.

Syntax:

 SELECT <ColumnList> FROM Table_A AS ALIAS_A LEFT OUTER JOIN Table_B AS ALIAS_B ON ALIAS_A.<RelatedColumn> = ALIAS_B.<RelatedColumn>

Let's say you want to get all customer ids from Sales.Customers table and order information like ship dates., due dates, get even customers who didn't place any orders, but the number of records is very large so we will order limit before 2019, to do this, you perform the LEFT OUTER JOIN as follows:

 SELECT A.CustomerID, B.DueDate, B.ShipDAte FROM Sales.Customer A LEFT OUTER JOIN Sales.SalesOrderHeader B ON A.CustomerID = B.CustomerID AND YEAR(B.DueDate) < 2019

In the code above, the left outer join is a structure between the Sales.Customer and Sales.SalesOrderHeader tables. The table is joined based on the customer id column. In this situation, all the records of the left table are Sales.Customer and only the matched records of the right table are Sales.SalesOrderHeader , if the data of the right table do not match, the left table still is retrieved and the table to the right of the data will return null.

Right Outer Join

Right outer join retrieves all records from the right table of the join, regardless of whether there is a match in the first table.

 SELECT <ColumnList> FROM Left_Table_Name AS Alias_A RIGHT OUTER JOIN Table_B as ALIAS_B ON Alias_A.<RelatedColumn> = Allias_B.<RelatedColumn>

For example, you want to retrieve all product names from the product table and all the corresponding orders from the SalesOrderDetail table even though there may exist product records that do not match the data in the SalesOrderDetail table (products that have not been sold yet). ever) as follows:

 SELECT P.Name,S.SalesOrderID FROM Sales.SalesOrderDetail S RIGHT OUTER JOIN Production.Product P ON P.ProductID = S.ProductID

Self Join

A self-join join is used to show the relationship between records on the same table. A table that is joined to itself is called Self-Join .

For example, you want to use self-join to retrieve the product details that have the same color in the Production.Product table

 SELECT p1.ProductID, p1.Color, p1.Name, p2.Name FROM Production.Product p1 INNER JOIN Production.Product p2 ON p1.Color = p2.Color ORDER BY p1.ProductID

MERGE . statement

The MERGE statement allows you to maintain a destination table under certain join conditions on a source table using a single statement.

For example, if you want:

• Compare customer's lastname and firstname from 2 source and destination tables
• Update customer information in destination table if first name and last name match
• Add a new record in the destination table if the last name and first names in the source table do not exist in the destination table
• Delete records in destination table if last name and first name do not match source table

The MERGE statement completes tasks in a single statement. MERGE also allows you to display records that have been inserted, updated, or deleted using the OUTPUT clause.

Syntax:

 MERGE target_table USING source_table ON match_condition WHEN MATCHED THEN UPDATE SET col1 = vale [,Col2 = val2] WHEN [TARGET] NOT MATCHED THEN INSERT (Col1[,Col2...]) VALUES(Val1[,Val2...]) WHEN NOT MATCH BY SOURCE THEN DELETE [OUTPUT $action, inserted.Col1,Deleted.Col1,....];

Explain:

• target_table: the target table where the data will change here
• source_table: source table, contains records that can be added, updated, and deleted in the destination table
• match_conditions: the join condition (JOIN) and any comparison operators.
• MATCHED: returns true if target_table and source_table records match match_condition.
• NOT MATCHED: returns true if the record from source_table does not exist in target_table.
• SOURCE NOT MATCH: Returns if the record exists in target_table but not in source_table.
• OUTPUT: optional clause allows viewing of records that have been theme/deleted/updated in target_Table.

The MERGE statement ends with a semicolon (;).

For example:

 use AdventureWorks2019 go SET IDENTITY_INSERT [Person].[AddressType] ON MERGE INTO [Person].[AddressType] AS Target USING (VALUES (1,'Billing') , (2,'Home'),(3,'Headquarters'),(4,'Primary'),(5,'Shipping'),(6,'Archival'),(7,'Contact'),(8,'Alternative')) AS Source ([AddressTypeID],[Name]) ON (Target.[AddressTypeID] = Source.[AddressTypeID]) WHEN MATCHED AND (Target.[Name] <> Source.[Name]) THEN UPDATE SET [Name] = Source.[NAME] WHEN NOT MATCHED BY TARGET THEN INSERT ([AddressTypeID],[Name]) VALUES(Source.[AddressTypeID],Source.[Name]) WHEN NOT MATCHED BY SOURCE THEN DELETE OUTPUT $action, Inserted.[AddressTypeID], Inserted.Name, Deleted.[AddressTypeID], Deleted.Name;

Table Person.AddressType is the destination table, sample data is inserted through the USING command (VALUES (1,'Billing') , (2,'Home'),(3,'Headquarters'),(4,'Primary') ,(5,'Shipping'),(6,'Archival'),(7,'Contact'),(8,'Alternative')) AS Source is the source table, the matching condition is the AddressTypeID column of both tables. source and target. If the match condition evaluates to false (NOT MATCHED). New records will be added to the destination table. If the match conditions return true (MATCHED), the record will be updated to the destination table based on the data of the source table.

If the records in the destination table do not match the source table (NOT MATCHED BY SOURCE), they are deleted from the destination table. The last statement's role is to report the rows that have been added/updated/deleted and display the output.