This article provides an introduction to what T-SQL joins are and how to use them in the SQL Server database.
T-SQL Joins are used to return records from two or more tables in a SQL Server database.
A SQL join consists of a query that uses multiple tables.
SQL Server query uses clauses, subqueries, expressions, operators and CTEs.
Inner Join
The T-SQL Inner Join returns rows from two tables when a match is found.
SELECT a.column1, b.column1, b.column2
FROM table_A a, table_B b
WHERE a.column1 = b.column1
AND b.column2 > 100 ;
Left Join
The T-SQL Left Join returns all rows from the left table, even if there are no matches with the right table.
SELECT a.column1, a.column2, b.column1, b.column2
FROM table_A a LEFT JOIN table_B b
ON a.column1 = b.column1
ORDER BY a.column1;
Right Join
The T-SQL Right Join returns all rows from the right table, even if there are no matches with the left table.
SELECT a.column1, b.column1, b.column2
FROM table_A a RIGHT JOIN table_B b
ON a.column1 = b.column1
ORDER BY a.column1;
Self Join
The T-SQL Self Join is used to join a table to itself.
SELECT a.column1, b.column1, b.column2
FROM table_A a, table_A b
ON a.column1 = b.column1
ORDER BY a.column1;
Cross Join
The T-SQL Cross Join, also known as a Cartesian product, returns the combination of every row from the first table with every row from the second table. It is used to combine every row from one table with every row from another table, resulting in a much larger table with a number of rows equal to the product of the number of rows in each table.
SELECT *
FROM table_A
CROSS JOIN table_B;