SQL JOINS:

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)

mrm_patient (PARENT TABLE)

here is another table "mrm_condensed_history" with five field and one primary key (sysno_porder)

mrm_condensed_history (CHILD TABLE)

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

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

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

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.

Back to the homepage