120

T-SQL JOINS

T-SQL combines records from two or more tables. It is used to join the records from two or more tables into the database. JOINs are used to connect the fields from many tables by using the values that are equal to each other.

See the below two tables, (a) CUSTOMERS table are as follows –

ID	NAME	AGE	ADDRESS	SALARY
1	Hamilton	23	Australia	34000
2	Warner	34	England	22000
3	Martin	28	China	25000
4	Twinkle	30	Turkey	50000
5	Tinu	32	Nepal	45000
6	Michal	31	Bhutan	20000
7	Harper	20	Bangladesh	15000

(b) Another table ORDERS, is as follows –

OID	DATE	CUSTOMER_ID	AMOUNT
100	2020-10-08 00.00.000	3	15000
101	2020-11-20 00.00.000	2	15600
102	2020-10-08 00.00.000	3	30000
103	2019-05-20 00.00.000	4	20600

Let us join two tables in our SELECT statement like below –

The command produces the given output.

The join is performed in the WHERE clause. Many operators will used to join tables, Like =, <, >, <>, <=, >=, ! =, LIKE, BETWEEN and NOT.

Types of Joins in MS SQL:

There are many types of joins used in MS SQL Server –

• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• FULL JOIN
• SELF JOIN
• CARTESIAN JOIN

S.no	Description
INNER JOIN	It returns rows when there is a match in the tables.
LEFT JOIN	It selects records from the first (left-most) table with matching correct table records.
RIGHT JOIN	It selects all the rows from the right table, if there are no any matches found in the left table.
FULL JOIN	If there is any match in one of the tables then the rows are joining in full join.
SELF JOIN	SELF JOIN is used to join a table, if it has two tables, rename at least one table in the MS SQL SERVER.
CARTESIAN JOIN	The Cartesian product sets the record of records many connected table in T-SQL.

Example:

Here are two tables named Color and Size, which we have combined with the help of full join.

---