How to join three tables in SQL
- How-Tos FAQs
- December 17, 2018
In SQL, we can join many tables in a single query. Also, we can have different joins among different tables in a query like INNER JOIN, LEFT JOIN, CROSS JOIN, etc. Joining 3 or more tables is basically the same as joining 2 tables.
Let us consider below 3 tables below:
CREATE TABLE continent (
continent_id INT,
continent_name VARCHAR(50)
);
CREATE TABLE country (
country_id INT,
country_name VARCHAR(50),
continent_id INT
);
CREATE TABLE city (x
city_id INT,
city_name VARCHAR(50),
country_id INT
);
INSERT INTO continent(continent_id, continent_name)
SELECT 1, 'Europe' UNION ALL
SELECT 2, 'Asia' UNION ALL
SELECT 3, 'North America';
INSERT INTO country(country_id, country_name, continent_id)
SELECT 1, 'France', 1 UNION ALL
SELECT 2, 'USA', 3 UNION ALL
SELECT 3, 'China', 2;
INSERT INTO city(city_id, city_name, country_id)
SELECT 1, 'Paris', 1 UNION ALL
SELECT 2, 'New York', 2 UNION ALL
SELECT 3, 'Beijing', 3;We can join the above three tables using INNER JOIN as below:
SELECT con.continent_name, c.country_name, ct.city_name
FROM continent con
INNER JOIN country c
ON con.continent_id = c.continent_id
INNER JOIN city ct
ON ct.country_id = c.country_id
-- Output
# continent_name country_name city_name
-----------------------------------------------------
Europe France Paris
North America USA New York
Asia China BeijingWe can also use both INNER JOIN and LEFT JOIN in a single query as below:
SELECT con.continent_name, c.country_name, ct.city_name
FROM continent con
INNER JOIN country c
ON con.continent_id = c.continent_id
LEFT JOIN city ct
ON ct.country_id = c.country_id
-- Output
# continent_name country_name city_name
-----------------------------------------------------
Europe France Paris
North America USA New York
Asia China BeijingAnd that is how to join three tables in SQL!
Up Next:
Read: How to SELECT data from a table that is not present in another table with SQL
