JOINs in SQL Server

SQL JOIN

JOIN Keyword we use in SQL statement to query data from two or more tables, based on a relationship we provide in statement.
the relationship is join between two table with the same columns and keys.

for example: the syntax would be

select t1.tablecolumn1, t1.tablecolumn2, t2.tablecolumn1, t2.tablecolumn2
from sometable1 t1 JOIN sometable2 t2 ON t1.tablecolumn1 = t2.tablecolumn1

to do join between two or more tables we need one single common key column both tables should have.

Types of Join we use and differences in them

1. JOIN (Inner Join): Return rows when there is at least one match in both tables
2. LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table
3. RIGHT JOIN: Return all rows from the Right table, even if there are no matches in the left table
4. FULL JOIN: Return rows when there is a match in one of the tables

Joins with Example.

INNER JOIN

syntax with example

select t1.tablecolumn1, t1.tablecolumn2, t2.tablecolumn1, t2.tablecolumn2
from sometable1 t1 INNER JOIN sometable2 t2 ON t1.tablecolumn1 = t2.tablecolumn1

LEFT JOIN

syntax with example:

select t1.tablecolumn1, t1.tablecolumn2, t2.tablecolumn1, t2.tablecolumn2
from sometable1 t1 LEFT JOIN sometable2 t2 ON t1.tablecolumn1 = t2.tablecolumn1

RIGHT JOIN

syntax with example:

select t1.tablecolumn1, t1.tablecolumn2, t2.tablecolumn1, t2.tablecolumn2
from sometable1 t1 RIGHT JOIN sometable2 t2 ON t1.tablecolumn1 = t2.tablecolumn1

FULL JOIN

syntax with example:

select t1.tablecolumn1, t1.tablecolumn2, t2.tablecolumn1, t2.tablecolumn2
from sometable1 t1 FULL JOIN sometable2 t2 ON t1.tablecolumn1 = t2.tablecolumn1