This article is written under ‘Quick and Short’ edition. In this article I will explain about Joins used within T-SQL statement.

Read more Quick and Short articles.

The JOIN keyword is used in an SQL statement to query data from two or more tables, based on a relationship between certain columns in these tables.

Tables in a database are often related to each other with keys.

A primary key is a column (or a combination of columns) with a unique value for each row. Each primary key value must be unique within the table. The purpose is to bind data together, across tables, without repeating all of the data in every table.

Different SQL Joins:

• Inner Join
• LEFT OUTER JOIN or LEFT JOIN
• RIGHT OUTER JOIN or RIGHT JOIN
• FULL OUTER JOIN or FULL JOIN
• Cross Join
• UNION
• UNION ALL

 

Inner Join

An inner join is a join in which the values in the columns being joined are compared using a comparison operator.

In the ISO standard, inner joins can be specified in either the FROM or WHERE clause. This is the only type of join that ISO supports in the WHERE clause. Inner joins specified in the WHERE clause are known as old-style inner joins.

In the following example inner join is used which is also 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.

USE AdventureWorks;

GO

SELECT *

FROM HumanResources.Employee AS e

    INNER JOIN Person.Contact AS c

    ON e.ContactID = c.ContactID

ORDER BY c.LastName

Outer Join

Outer join returns 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. If you want to include data rows in the result set that do not have a match in the joined table, you can create an outer join.

LEFT OUTER JOIN or LEFT JOIN

The Left Outer Join operator returns each row that satisfies the join of the left table (Production.Product) with the right table (Production.ProductReview). It also returns any rows from the left table that had no matching rows in the right table. The nonmatching rows in the right input are returned as null values.

Following is the example of Left outer join. It will include all rows in the Product table in the results, whether or not there is a match on the ProductID column in the ProductReview table. Notice that in the results where there is no matching product review ID for a product, the row contains a null value in the ProductReviewID column.

USE AdventureWorks;

GO

SELECT p.Name, pr.ProductReviewID

FROM Production.Product p

LEFT OUTER JOIN Production.ProductReview pr

ON p.ProductID = pr.ProductID

 

RIGHT OUTER JOIN or RIGHT JOIN

The Right Outer Join operator returns each row that satisfies the join of the right table (Sales.SalesPerson) with each matching row from the left table (Sales.SalesTerritory). It also returns any rows from the right table that had no matching rows in the left table, joined with NULL.

Following is the example of Right outer join. It will include all rows in the SalesPerson table in the results, whether or not there is a match on the TerritoryID column in the SalesTerritory table.

 USE AdventureWorks;

GO

SELECT st.Name AS Territory, sp.SalesPersonID

FROM Sales.SalesTerritory st

RIGHT OUTER JOIN Sales.SalesPerson sp

ON st.TerritoryID = sp.TerritoryID ;

 

FULL OUTER JOIN or FULL JOIN

To retain the nonmatching information by including nonmatching rows in the results of a join, use a full outer join. SQL Server provides the full outer join operator, FULL OUTER JOIN, which includes all rows from both tables, regardless of whether or not the other table has a matching value.

You can include a WHERE clause with a full outer join to return only the rows where there is no matching data between the tables. The following query returns only those products that have no matching sales orders, as well as those sales orders that are not matched to a product (although all sales orders, in this case, are matched to a product).

USE AdventureWorks;

GO

– The OUTER keyword following the FULL keyword is optional.

SELECT p.Name, sod.SalesOrderID

FROM Production.Product p

FULL OUTER JOIN Sales.SalesOrderDetail sod

ON p.ProductID = sod.ProductID

WHERE p.ProductID IS NULL

OR sod.ProductID IS NULL

ORDER BY p.Name ;

 

Cross Join

The Cross Join operator joins each row from the left table with each row from the right table. A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table. However, if a WHERE clause is added, the cross join behaves as an inner join.

USE AdventureWorks;

GO

SELECT p.SalesPersonID, t.Name AS Territory

FROM Sales.SalesPerson p

CROSS JOIN Sales.SalesTerritory t

WHERE p.TerritoryID = t.TerritoryID

ORDER BY p.SalesPersonID;

 

UNION

 The UNION operator is used to combine the result-set of two or more SELECT statements.

Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.

Note: The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL.

SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2

 

UNION ALL

UNION ALL acts as UNION operator. However, UNION ALL allows duplicate values to return.

SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2

 ———————————————————————————————————————————————————————————

Readers are requested to add comment, and add more information in case they want.

Read more Quick and Short articles.

2 Responses to “Quick and short: T-SQL Joins”

1. prasad says:

   June 10, 2010 at 5:28 PM

   the sql table is:-
   76    87
   pp    ff
   54    87
   pp    hh
   this is two colume:.
   i want this o/p :–

   76    87
   pp    ff
   54    null
   null   hh
2. prasad says:

   June 10, 2010 at 5:30 PM

   the sql table is:-
   76    87
   pp    ff
   54    87
   pp    hh
   this is two colume:.
   i want this o/p :–

   76    87
   pp    ff
   54    null
   null   hh
    
    
   reply