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 -
(b) Another table ORDERS, is as follows -
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
||It returns rows when there is a match in the tables.
||It selects records from the first (left-most) table with matching correct table records.
||It selects all the rows from the right table, if there are no any matches found in the left table.
||If there is any match in one of the tables then the rows are joining in full join.
||SELF JOIN is used to join a table, if it has two tables, rename at least one table in the MS SQL SERVER.
||The Cartesian product sets the record of records many connected table in T-SQL.
Here are two tables named Color and Size, which we have combined with the help of full join.