Home » T-SQL JOINS

T-SQL JOINS

by Online Tutorials Library

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 –

IDNAMEAGEADDRESSSALARY
1Hamilton23Australia34000
2Warner34England22000
3Martin28China25000
4Twinkle30Turkey50000
5Tinu32Nepal45000
6Michal31Bhutan20000
7Harper20Bangladesh15000

(b) Another table ORDERS, is as follows –

OIDDATECUSTOMER_IDAMOUNT
1002020-10-08 00.00.000315000
1012020-11-20 00.00.000215600
1022020-10-08 00.00.000330000
1032019-05-20 00.00.000420600

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

The command produces the given output.

T-SQL JOINS

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

T-SQL JOINS

S.noDescription
INNER JOINIt returns rows when there is a match in the tables.
LEFT JOINIt selects records from the first (left-most) table with matching correct table records.
RIGHT JOINIt selects all the rows from the right table, if there are no any matches found in the left table.
FULL JOINIf there is any match in one of the tables then the rows are joining in full join.
SELF JOINSELF JOIN is used to join a table, if it has two tables, rename at least one table in the MS SQL SERVER.
CARTESIAN JOINThe 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.

T-SQL JOINS


You may also like