SQL SERVER JOINS

Introduction

SQL joins are used to relate information from different tables. SQL join is used in WHERE clause

of SELECT, UPDATE and DELETE statements.

Various types of joins available in SQL are:

 Inner

 Outer

◦ Left

◦ Right

◦ Full

 Cross Join

 Self Join

Inner Join

The INNER JOIN creates a new result table by combining column values of two tables (table1

and table2) based upon the join-predicate. The query compares each row of table1 with each row

of table2 to find all pairs of rows which satisfy the join-predicate. When the join-predicate is

satisfied, column values for each matched pair of rows of A and B are combined into a result row.

The basic syntax of INNER JOIN is as follows:

SELECT table1.column1, table2.column2...

FROM table1

INNER JOIN table2

ON table1.common_field = table2.common_field;

Consider the following two tables, (a) CUSTOMERS table is as follows:

Left Outer Join

The SQL LEFT OUTER JOIN returns all rows from the left table, even if there are no matches in the right table. This means that if the ON clause matches 0 (zero) records in right table, the join will still return a row in the result, but with NULL in each column from right table.This means that a left outer join returns all the values from the left table, plus matched values from the right table or NULL in case of no matching join predicate.

The basic syntax of LEFT OUTER JOIN is as follows:

SELECT table1.column1, table2.column2...

FROM table1

LEFT OUTER JOIN table2

ON table1.common_field = table2.common_field;

Here given condition could be any given expression based on your requirement.

Consider the following two tables, (a) CUSTOMERS table is as follows:

Right Outer Join

The SQL RIGHT OUTER JOIN returns all rows from the right table, even if there are no matches in the left table. This means that if the ON clause matches 0 (zero) records in left table, the join will still return a row in the result, but with NULL in each column from left table.

This means that a right join returns all the values from the right table, plus matched values from the left table or NULL in case of no matching join predicate.

The basic syntax of RIGHT OUTER JOIN is as follows:

SELECT table1.column1, table2.column2...

RIGHT OUTER JOIN table2

ON table1.common_field = table2.common_field;

Consider the following two tables, (a) CUSTOMERS table is as follows:

Full Outer Join

The SQL FULL OUTER JOIN combines the results of both left and right outer joins.The joined table will contain all records from both tables, and fill in NULLs for missing matches on

The basic syntax of FULL OUTER JOIN is as follows:

SELECT table1.column1, table2.column2...

FROM table1

FULL OUTER JOIN table2

ON table1.common_field = table2.common_field;

Here given condition could be any given expression based on your requirement.

Consider the following two tables, (a) CUSTOMERS table is as follows:

Cross Join

The CARTESIAN JOIN or CROSS JOIN returns the Cartesian product of the sets of records from 

the two or more joined tables. Thus, it equates to an inner join where the join-condition always evaluates to True or where the join-condition is absent from the statement.

The basic syntax of CROSS JOIN is as follows:

SELECT table1.column1, table2.column2...

FROM table1, table2 [, table3 ]

Consider the following two tables, (a) CUSTOMERS table is as follows:

Self Join

The SQL SELF JOIN is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.

The basic syntax of SELF JOIN is as follows:

SELECT a.column_name, b.column_name...

FROM table1 a, table1 b

WHERE a.common_field = b.common_field;

Here, WHERE clause could be any given expression based on your requirement.

Consider the following two tables, (a) CUSTOMERS table is as follows: