How to Link Tables in SQL Databases

How to Link Tables in SQL Databases
Page content

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;

Linked tables

This is what the last query does:

  • SELECT continents.continent_name, countries.country_name - selects the fields continent_name and country_name from the tables continents and countries respectively.
  • FROM continents INNER JOIN countries - uses both the databases and uses the keyword INNER JOIN for joining the countries table or relating the countries table with the continents table.
  • ON continents.continent_id=countries.continent_id - the fields that are to be related in both the tables.
  • ORDER BY continents.continent_name; - this part is used to order the output result set in the order of the continents name. There are other ways of ordering too.

The result shows the list of countries and the continents they are in. Thus instead of saving them in the same database, they are saved in different tables and linked whenever necessary. There are other ways of linking tables too but JOIN is one of the most widely used keyword to join two or more tables.