Home > DeveloperSection > Articles > Join in SQL Server

Join in SQL Server


Database Database 
Ratings:
0 Comment(s)
 6092  View(s)
Rate this:

JOIN in SQL Server


JOIN is used whenever we have to select data from two or more tables. To be able to use JOIN to extract data from we need a relationship between certain columns in the tables.

Table Used in this Article

Doctors

ID          Name

----------- --------------------

1           Joe Manners

2           Sue Tongs

3           Jeff Spine

4           Mary Rasch

5           Tom Thumb

6           Norm Lobe

Patients

ID          Name                 DocID

----------- -------------------- -----------

1           Jim Thick            4

2           Tom Small            2

3           Al Downs             4

4           Ann Hills            1

5           Tim Burrow           3

6           Jane Fern            5

7           Sam Broom            2

8           Gary Far             1

9           Bill Out             5

10          Dave Bell            4

11          Fred Overs           5

12          Greg Double          1

13          Bob Marks            9

INNER JOIN

An INNER JOIN is the most common join operation used in applications, and represents the default join-type. Inner join creates a new result table by combining column values of two tables (A and B) based upon the join-predicate. The query compares each row of A with each row of B 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.

Example

select d.name as DocName, p.name as PatientName

from Doctors d inner join Patients p on d.ID=p.DocID

OUTER JOIN

An OUTER JOIN does not require each record in the two joined tables to have a matching record. The joined table retains each record—even if no other matching record exists.

There are three types of OUTER JOIN.

·         LEFT OUTER JOIN

·         RIGHT OUTER JOIN

·         FULL OUTER JOIN

·        LEFT OUTER JOIN

The result of a LEFT OUTER JOIN (or simply LEFT JOIN) for table A and B always contains all records of the "left" table (A), even if the join-condition does not find any matching record in the "right" table (B). This means that if the ON clause matches 0 (zero) records in B, the join will still return a row in the result—but with NULL in each column from B. 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). If the left table returns one row and the right table returns more than one matching row for it, the values in the left table will be repeated for each distinct row on the right table.

Example

select d.name as DocName, p.name as PatientName

from Doctors d LEFT OUTER JOIN Patients p

on d.ID=p.DocID

 

 

·        RIGHT OUTER JOIN

A RIGHT OUTER JOIN (or RIGHT JOIN) closely resembles a left outer join, except with the treatment of the tables reversed. Every row from the "right" table (B) will appear in the joined table at least once. If no matching row from the "left" table (A) exists, NULL will appear in columns from A for those records that have no match in B. A right outer join returns all the values from the right table and matched values from the left table (NULL in case of no matching join predicate).

Example

select d.name as DocName, p.name as PatientName

from Doctors d RIGHT OUTER JOIN Patients p

on d.ID=p.DocID

·        FULL OUTER JOIN

A 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 either side

Example

select d.name as DocName, p.name as PatientName

from Doctors d FULL OUTER JOIN Patients p

on d.ID=p.DocID


Don't want to miss updates? Please click the below button!

Follow MindStick