By using joins, we can get the data from two or more tables based on logical condition between the tables.
code: Can't Copy and Paste this? Click here for a copy-and-paste friendly version of this code!
Terms of Agreement:
By using this code, you agree to the following terms...
You may use
this code in your own programs (and may compile it into a program and distribute it in compiled format for languages that allow it) freely and with no charge.
You MAY NOT redistribute this code (for example to a web site) without written permission from the original author. Failure to do so is a violation of copyright laws.
You may link to this code from another website, but ONLY if it is not wrapped in a frame.
You will abide by any additional copyright restrictions which the author may have placed in the code or code's description.
//**************************************
// Name: A glance of SQL server Joins
// Description:By using joins, we can get the data from two or more tables based on logical condition between the tables.
// By: Shyam Srinivas
//
//This code is copyrighted and has// limited warranties.Please see http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=7288&lngWId=10//for details.//**************************************
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
1.Inner join
2.Outer join
3.Left outer join
4.Right outer join
5.Cross join
6.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
For More Articles : http://www.shyamsrinivas.com/Articles-and-News.aspx
Your feedback will be posted below and an email sent to
the author. Please remember that the author was kind enough to
share this with you, so any criticisms must be stated politely, or they
will be deleted. (For feedback not related to this particular code, please
click here instead.)
By 1 Users
