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
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:
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:
Of course you can filter the results you are retrieving using the WHERE SQL keyword (read more here), with a statement like this:
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:
You can also order the result of your SQL JOIN using the ORDER BY clause like this:
The statement above will order all records by Date of Birth. Here is the result:
If you are interested in learning more about SQL JOIN visit this site.