|
Please note that the information on
this page is related to an older version of Microsoft SQL
Server.
Click on SQL Server
Tutorials for new training content and material. In
addition we have FREE SQL
Tutorial videos here, so feel free to download
the demos. Our partners also manage two blogs on
the following SQL Server Versions:
-SQL Server 2008
Blog
-SQL
Azure Blog
Please send any comments or suggestion
to info@sqlserver2008tutorial.com
Joins are an integral component of
relational database design and usage. Joins let you match data from
multiple tables, based on significant key information. There are two
styles of joins, older style and ANSI style. In the older versions
of SQL Server, the join operation was performed in the WHERE clause
where as in the ANSI style, the join operation is performed in the
FROM clause. There are five types of joins,
CROSS JOIN, INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN. We used a LEFT JOIN in the following example.
A LEFT JOIN specifies all the records from the table on the left side of the join statement. It also gives corresponding values from the table on the right side and NULLs for the missing values. Consider a table "mrm_patient" with five fields and one primary key (sysno_patient)
here is another table "mrm_condensed_history" with five field and one primary key (sysno_porder)
| mrm_condensed_history (CHILD TABLE) |
| sysno_porder(PK) |
sysno_patient(FK) |
exam_status_ind |
exam_date |
sysno_trans |
| 107425 |
146494 |
7 |
Dec 7 1998 13:15PM |
7 |
| 107426 |
168314 |
5 |
Dec 8 1998 11:30PM |
3 |
| 107427 |
135264 |
6 |
Jan 21 1998 03:45PM |
8 |
| 107428 |
146502 |
8 |
Feb 14 1998 01:00PM |
9 |
| 107428 |
146500 |
7 |
Mar 7 1998 12:15PM |
2 |
If we do the following LEFT join
SELECT c.sysno_patient, p.sysno_patient,c.exam_date,p.First_Name
FROM mrm_condensed_history c
LEFT JOIN mrm_patient p
ON c.sysno_patient=p.sysno_patient
ORDER BY p.sysno_patient
we would get the following result
| c.sysno_patient |
p.sysno_patient |
c.exam_date |
p.First_Name |
| 146494 |
NULL |
Dec 7 1998 13:15PM |
NULL |
| 168314 |
NULL |
Dec 8 1998 11:30PM |
NULL |
| 135264 |
NULL |
Jan 21 1998 03:45PM |
NULL |
| 146500 |
146500 |
Mar 7 1998 12:15PM |
Taylor |
| 146502 |
146502 |
Feb 14 1998 01:00PM |
Sue |
Notice how for the first three records, we got a NULL value in the second field. The reason for this is that in the parent table "mrm_patient", there are no patients with ID's 14694, 168314, and 135264. However these patients do exist in the child table "mrm_condensed history". This is a problem in order for these tables to have a relationship which is explained in the foreign constraint section of the website. This Problem is solved using the following three commands.
DELETE FROM mrm_condensed_history WHERE sysno_patient=146494
DELETE FROM mrm_condensed_history WHERE sysno_patient=168314
DELETE FROM mrm_condensed_history WHERE sysno_patient=135264
This is one of the useful features of using a join in your query. After you have an established relationship between the tables, the users can easily search for data in both tables using just one command.
CROSS JOIN is used to return all the data from the tables. As you can see, the cross join does not return much useful inoformation and hence is not used often. In order to use a cross join on the above tables we use the following command
SELECT *
FROM mrm_patient, mrm_condensed_history
INNER JOIN also known as STANDARD JOIN returns only the records that match in both tables. The syntax for the inner join in the old style is
SELECT c.sysno_patient,p.Last_Name,p.First_Name
FROM mrm_condensed_history c, mrm_patient p
WHERE c.sysno_patient=p.sysno_patient
will give the following result
| sysno_patient(PK) |
Last_ Name |
First_Name |
| 146500 |
James |
Taylor |
| 146502 |
James |
Sue |
RIGHT JOIN specifies all the records from the table on the right side of the join statement. It also gives corresponding values from the table on the left side and NULLs for the missing values. Consider the above tables again.
If we do the following RIGHT join
SELECT p.sysno_patient,c.sysno_porder,p.Last_Name,p.First_Name
FROM mrm_condensed_history c
RIGHT JOIN mrm_patient p
ON c.sysno_patient=p.sysno_patient
will give the following result
| sysno_patient(PK) |
sysno_porder(PK) |
Last_ Name |
First_Name |
| 146498 |
Null |
Mitchel |
John |
| 146499 |
Null |
Spencer |
Teri |
| 146500 |
107427 |
James |
Taylor |
| 146501 |
Null |
Pewinsky |
Lewis |
| 146502 |
107428 |
James |
Sue |
Notice how for the three records, we got a NULL value in the second field. The reason for this is that in the table "mrm_condensed_history", there are no patients with ID's 146498, 146499, and 146501.
|
