T-SQL - Joining Tables

The MS SQL Server Joins clause is used to combine records from two or more tables in a database. A JOIN is a means for combining fields from two tables by using values common to each.

Consider the following two tables, (a) CUSTOMERS table is as follows −

ID  NAME       AGE       ADDRESS             SALARY 
1   Ramesh     32        Ahmedabad           2000.00 
2   Khilan     25        Delhi               1500.00 
3   kaushik    23        Kota                2000.00 
4   Chaitali   25        Mumbai              6500.00 
5   Hardik     27        Bhopal              8500.00 
6   Komal      22        MP                  4500.00 
7   Muffy      24        Indore              10000.00

(b) Another table is ORDERS as follows −

OID  DATE                       CUSTOMER_ID        AMOUNT 
100  2009-10-08 00:00:00.000    3                  1500.00 
101  2009-11-20 00:00:00.000    2                  1560.00 
102  2009-10-08 00:00:00.000    3                  3000.00 
103  2008-05-20 00:00:00.000    4                  2060.00

Let us join these two tables in our SELECT statement as follows −

SELECT ID, NAME, AGE, AMOUNT 
   FROM CUSTOMERS, ORDERS 
   WHERE  CUSTOMERS.ID = ORDERS.CUSTOMER_ID 
OR 
SELECT A.ID, A.NAME, A.AGE, B.AMOUNT 
   FROM CUSTOMERS A inner join  ORDERS B on A.ID = B.Customer_ID

The above command will produce the following output.

ID   NAME      AGE    AMOUNT 
2    Khilan    25     1560.00 
3    kaushik   23     1500.00 
3    kaushik   23     3000.00 
4    Chaitali  25     2060.00

It is noticeable that the join is performed in the WHERE clause. Several operators can be used to join tables, such as =, <, >, <>, <=, >=, !=, BETWEEN, LIKE, and NOT; they can all be used to join tables. However, the most common operator is the equal symbol.

MS SQL Server Join Types −

There are different types of joins available in MS SQL Server −

  • INNER JOIN − Returns rows when there is a match in both tables.

  • LEFT JOIN − Returns all rows from the left table, even if there are no matches in the right table.

  • RIGHT JOIN − Returns all rows from the right table, even if there are no matches in the left table.

  • FULL JOIN − Returns rows when there is a match in one of the tables.

  • SELF JOIN − This is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the MS SQL Server statement.

  • CARTESIAN JOIN − Returns the Cartesian product of the sets of records from the two or more joined tables.

Advertisements