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
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
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
You can also read these related post
https://www.mindstick.com/blog/291/join-in-sql-server
https://www.mindstick.com/Articles/450/joins-in-sql-server
Anonymous User
17-Jun-2019Thank You for the post.
Sunil Singh
16-Jun-2017It was really helpful to read this post.