How to Link Tables in SQL
Data can be retrieved from tables in databases using the SELECT keyword. The results can be filtered using the WHERE keyword and by using conditions along with other functions available in SQL.
Now when the data spans more than two tables they can be linked using various methods. One of the most common ways is using the JOIN keyword. There are various JOIN combinations like LEFT JOIN, RIGHT JOIN, INNER JOIN, etc.
Why Tables are Linked?
In a database just one table can be used to store all information. But this will lead to redundancy of data and reduce the efficiency of the database management system. So linking tables in SQL allows us to eliminate, or at least reduce, data redundancy and helps to increase the speed when search queries are performed on the table.
How to Link Tables in SQL:
Linking tables in SQL can be done using one or more common fields between tables. This is the one of the greatest advantages of relational databases.
In this example we will take two tables called Continents and Countries and link them.
The Continents table will have a list of Continents
The Countries table will have a list of Countries.
Create a database and create two tables in that database.
The following SQL queries are written to create a database named places and then two tables 1) continents and 2) countries.
- CREATE DATABASE places;
- USE Places;
- CREATE TABLE `continents` (
`continent_id` INT( 3 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`continent_name` VARCHAR( 15 ) NOT NULL
- CREATE TABLE `countries` (
- `country_id` INT( 3 ) NOT NULL AUTO_INCREMENT PRIMARY KEY , `continent_id` INT( 3 ) NOT NULL,
`country_name` VARCHAR( 15 ) NOT NULL
Populate the tables by entering values in both the tables.
Continents table has the fields - continent_id, continent_name
Countries table has the fields - country_id, continent_id, country_name
They are joined or related together by a common field called continent_id which is the primary key in the table continents and a field in the countries table.
Now both the tables are joined with the following statement.
SELECT continents.continent_name, countries.country_name FROM continents INNER JOIN countries ON continents.continent_id=countries.continent_id ORDER BY continents.continent_name;