SQL JOIN

Today's article shows how to use SQL statement to select data from related database tables using the SQL JOIN clause. We will us the following 2 tables:

Person
IDNameDOBCitizenshipID
1John12/12/19801
2Paul10/10/19812
3Simon5/4/19772
4Roger19/06/19883
Country
IDCountry
1USA
2Canada
3United Kingdom

These two tables have a common field which is the ID from the Country table, which matches with the CitizenshipID from the Person table. This common field is referred to as a foreign key, and its role is to establish relationship between the two tables.

Now that we have our two tables, let's see how we can select data from both tables. Looking at the Person table it's not immediately clear what country is each person citizen of, but we can get that information by joining the data from the 2 tables. Consider the following:

SELECT A.Name, A.DOB, B.Country FROM Person AS A INNER JOIN Country AS B ON A.CitizenshipID = B.ID

First we have added aliases to the 2 tables – the alias for table Person is A, and the alias for table Country is B. The second step is to specify which fields we are selecting from the two table - A.Name, A.DOB, B.Country. Then we are joining the two tables with the INNER JOIN statement, and finally we want the CitizenshipID from the Person table to be equal to the ID from the Country table. Here is the result of the above statement:

NameDOBCountry
John12/12/1980USA
Paul10/10/1981Canada
Simon5/4/1977Canada
Roger19/06/1988United Kingdom

Of course you can filter the results you are retrieving using the WHERE SQL keyword (read more here), with a statement like this:

SELECT A.Name, A.DOB, B.Country FROM Person AS A INNER JOIN Country AS B ON A.CitizenshipID = B.ID WHERE DOB > '1/1/1980'

This statement will still join the data from the two tables, however only for the records that have DOB in Person table greater than 1/1/1980. Here is the result of this SQL query:

NameDOBCountry
John12/12/1980USA
Paul10/10/1981Canada
Roger19/06/1988United Kingdom

You can also order the result of your SQL JOIN using the ORDER BY clause like this:

SELECT A.Name, A.DOB, B.Country FROM Person AS A INNER JOIN Country AS B ON A.CitizenshipID = B.ID ORDER BY DOB ASC

The statement above will order all records by Date of Birth. Here is the result:

NameDOBCountry
Simon5/4/1977Canada
John12/12/1980USA
Paul10/10/1981Canada
Roger19/06/1988United Kingdom

If you are interested in learning more about SQL JOIN visit this site.