Different Type of Joins

The JOIN keyword is used in an SQL statement to query data from two or more tables, based on a relationship between certain columns in these tables.

1 INNER JOIN

The INNER JOIN keyword return rows when there is at least one match in both tables.

2 OUTER JOIN

There are three different Outer Join methods.

LEFT OUTER JOIN
The LEFT OUTER JOIN keyword returns all rows from the left table (table1), even if there are no matches in the right table (table2). If there are no columns

matching in the right table, it returns NULL values.

RIGHT OUTER JOIN
The RIGHT OUTER JOIN keyword returns all the rows from the right table (table2), even if there are no matches in the left table (table1). If there are no columns matching in the left table, it returns NULL values.

FULL OUTER JOIN
This join combines left outer join and right outer join. It returns row from either table when the conditions are met and returns null value when there is no match.

3 CROSS JOIN

A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table. The following example shows a Transact-SQL cross join.

SELECT p.ID, p.value, t.ID, t.value

FROM table1 p

CROSS JOIN table2 t

ORDER BY p.ID;

Cross-join is the same thing as performing a join where the condition is always true.

For Example

SELECT t1.*, t2.*
FROM t1
INNER JOIN t2
ON ( 1 = 1 )