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.
Very good blog article.Really looking forward to read more. Really Cool.