Joins in SQL server
Fundamentals of joins in SQL server
By using joins, we can get the data from two or more tables based on logical condition between the tables.
The two tables in a query related by
- specifying a column from each table used in the join.
- specifying a logical operator to be used in the comparing the columns.
The following are joins by classification
- Inner join
- Outer join
- Left outer join
- Right outer join
- Cross join
- Self join
Inner join: is a join which returns only the rows for which there is
an equal value in the join column. Inner joins can be specified in either the
FROM or WHERE caluses.
Recommended method is specify the join condition in FROM clause which helps in specifying the other conditions in the WHERE clause.
FROM <First_Table> <Join_Type> <Second_Table>
[ON <join-condition> ]
Example: SELECT *
FROM Employee AS e
INNER JOIN Department AS d
ON e.DepartmentID = d.DepartmentID
Inner join becomes an Equi join when you specify equality condition in JOIN clause.
Outer join: is a join which returns all the rows from the at least one of
the tables mentioned in the FROM clause and meeting the condition in the WHERE clause.
Outer joins can be specified in the FROM clause only.
All rows are retrieved from the left table referenced with a left outer join
and all rows retrieved from the right table with a right outer join.
All rows from the both tables are returned in a full outer join.
Left outer join example:
SELECT
Employee.EmployeeID,Employee.EmployeeName,Department.DepartmentName FROM Employee LEFT OUTER JOIN Department ON Employee.DepartmentID = Department.DepartmentID
Right outer join example:
SELECT
Department.DepartmentID,Department.DepartmentName,Employee.EmployeeName FROM Employee RIGHT OUTER JOIN Department ON Employee.DepartmentID = Department.DepartmentID
Cross join: A cross join is join which does not have a WHERE clause and
produces the cartesian product of the tables involved in the join.
The size of the cartesian product result set is the number of rows in the
first table multiplied by the number of rows in the second table.
Example:
SELECT p.SalesPersonID, t.Name AS Territory
FROM Sales.SalesPerson p
CROSS JOIN Sales.SalesTerritory t
Self-Joins: A table can be joined to itself in a self-join. For example, you can use a self-join to find the products that are supplied by more than one vendor.
Example:
SELECT DISTINCT pv1.ProductID, pv1.VendorID
FROM Purchasing.ProductVendor pv1
INNER JOIN Purchasing.ProductVendor pv2
ON pv1.ProductID = pv2.ProductID
AND pv1.VendorID <> pv2.VendorID
SQL server 2005 Features
Development Features
- CommonTableExpressions
Which returns a record set in a statement.
CTE can be thought of as a temporary result set which can be defined in the SELECT,INSERT,UPDATE and DELETE statements. CTE can be self-referencing and can be referenced multiple times in the same query.
The basic syntax structure for a CTE is:
WITH expression_name [ ( column_name [,...n] ) ]
AS ( CTE_query_definition )
- CLR support To build business objects
(stored procedures,triggers, functions, etc) which enables you to execute .NET code in the DBMS. - ServiceBroker handles the messaging between a sender and receiver in a loosely coupled manner.
- Data Encryption
Native supported functions to encrypt the data in user defined databases. - Microsoft incorporates SMTP mail to improve the native mail capabilities.
- Multiple Active Result Sets(MARS) Allow a persistance database connection to server more requests from a single client.
- SQL server Integration Services (SSIS) has replaced
DTS(Data Transformation Services) as the primary ETL tool and ship with the SQl server for free. - XML enhancements
- Try Catch Functionality
- Data Partioning
Management Features
- Backup enhancements
- Better Email handling
- Full 64-bit support
- Database Mirroring
- Dedicated administrator connection
- Database snapshots
