The MS SQL Server Joins condition is utilized to consolidate records from at least two tables in a data set. A JOIN is a methods for consolidating fields from two tables by utilizing values normal to each.
Think about the accompanying two tables, (a) CUSTOMERS table is as per the following −
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
Allow us to join these two tables in our SELECT assertion 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 order will create the accompanying yield.
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 observable that the join is acted in the WHERE statement. A few administrators can be utilized to join tables, for example, =, <, >, <>, <=, >=, !=, BETWEEN, LIKE, and NOT; they would all be able to be utilized to join tables. In any case, the most widely recognized administrator is the equivalent image.
MS SQL Server Join Types −
There are various kinds of joins accessible in MS SQL Server −
- Inward JOIN − Returns lines when there is a match in the two tables.
- LEFT JOIN − Returns all columns from the left table, regardless of whether there are no matches in the correct table.
- RIGHT JOIN − Returns all lines from the correct table, regardless of whether there are no matches in the left table.
- FULL JOIN − Returns lines when there is a match in one of the tables.
- SELF JOIN − This is utilized to join a table to itself as though the table were two tables, briefly renaming at any rate one table in the MS SQL Server articulation.
- CARTESIAN JOIN − Returns the Cartesian result of the arrangements of records from the at least two joined tables.