T-SQL Tutorial T-SQL Advanced Database Management T-SQL References

T-SQL - JOIN



The T-SQL (Transact-SQL) JOIN clause is used to combine rows of two or more tables based on common column between them. There are four types of JOINs in SQL:

  • INNER JOIN: It is sometimes called simple JOIN. It returns records based on matching rows in both tables.
  • LEFT JOIN: It is sometimes called LEFT OUTER JOIN. It returns records which contains all rows from left table and matching rows from right tables.
  • RIGHT JOIN: It is sometimes called RIGHT OUTER JOIN. It returns records which contains all rows from right table and matching rows from left tables.
  • FULL JOIN: It is sometimes called FULL OUTER JOIN. It returns records which contains all rows from both tables.
T-SQL JOINs

Example:

Consider a database containing tables called Employee and Contact_Info with the following records:

Table 1: Employee table

EmpIDNameCityAgeSalary
1JohnLondon253000
2MarryNew York242750
3JoParis272800
4KimAmsterdam303100
5RameshNew Delhi283000
6HuangBeijing282800

Table 2: Contact_Info table

Phone_NumberEmpIDAddressGender
+1-80540980002Brooklyn, New York, USAF
+33-1479961013Grenelle, Paris, FranceM
+31-2011503194Geuzenveld, Amsterdam, NetherlandsF
+86-10997324586Yizhuangzhen, Beijing, ChinaM
+65-672348247Yishun, SingaporeM
+81-3577990728Koto City, Tokyo, JapanM

  • The JOIN clause is used with Employee and Contact_Info tables based on common column EmpID. It returns Name, Age and Address columns based on match in both tables. The query is given below:

    SELECT Employee.Name, Employee.Age, Contact_Info.Address 
    FROM Employee
    JOIN Contact_Info
    ON Employee.EmpID = Contact_Info.EmpID;
    

    This will produce the result as shown below:

    NameAgeAddress
    Marry24Brooklyn, New York, USA
    Jo27Grenelle, Paris, France
    Kim30Geuzenveld, Amsterdam, Netherlands
    Huang28Yizhuangzhen, Beijing, China