How to Use Join Structures in SQL
A SQL join is a way to combine rows from two or more tables based on a common column between them. Produces values by merging together rows from different related tables. Use a join most times that you’re asked to find data that involves multiple records.
There are four type join structure.
- Inner Join
- Left Join
- Right Join
- Full Join
I will explain this structures with two tables which names are country and city. country table contains two columns. These are id and name.
country
id | name
1 | Germany
2 | China
3 | Usa
4 | South Africa
5 | TurkeyThe other table city table contains three columns which names are id, name and country_id. The column country_id references to id column in country table
city
id | name | country_id
1 | Munich | 1
2 | Ankara | 5
3 | Cape Town | 4
4 | New York | 3
5 | Shanghai | 2
6 | Istanbul | 5
7 | Seattle | 3
8 | Munich | 1
9 | Boston | NULLInner Join
This join structure will result rows only having matching values from both the tables on selected referenced column or join condition. In simple words, It is intersection of two tables.
Syntax :
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_nameExample :
We want to which city belongs to which country?. Well, the best choice is using Inner Join Structure for this type queries.
SELECT *
FROM country
INNER JOIN city ON city.country_id = country.id;
A temporary table is created for this process in the background then respectively countries are taken and compares with records in city table. If given condition in the query provides, record is added to this temporary table.
Example :
a record which id value is 1 taken from country table and It compared with a record that have name Munich in the city then added to the temporary table because it provides given condition.
id name id name country_id
1 | Germany | 1 | Munich | 1
1 | Germany | 8 | Munich | 1
.....Additionally, city table contains two the same record that city name is Munich. There is no wrong in here cause I want to show that duplication behavior in join structures.
Result :
id name id name country_id
1 | Germany | 1 | Munich | 1
1 | Germany | 8 | Munich | 1
2 | China | 5 | Shanghai | 2
3 | Usa | 4 | New York | 3
3 | Usa | 7 | Seattle | 3
4 | South Africa | 3 | Cape Town | 4
5 | Turkey | 2 | Ankara | 5
5 | Turkey | 6 | Istanbul | 5Left Join
This structure returns all rows from the left table, and the matching rows from the right table.
Syntax :
SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_nameExample:
SELECT *
FROM city
LEFT JOIN country ON country.id = city.country_id;
Result :
id name country_id id name
1 | Munich | 1 | 1 | Germany
8 | Munich | 1 | 1 | Germany
5 | Shanghai | 2 | 2 | China
4 | New York | 3 | 3 | Usa
7 | Seattle | 3 | 3 | Usa
3 | Cape Town | 4 | 4 | South Africa
2 | Ankara | 5 | 5 | Turkey
5 | Istanbul | 5 | 5 | Turkey
9 | Boston | NULL | NULL | NULLRight Join
This structure returns all records from the right table, and the matching records from the left table.
Syntax :
SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_nameExample :
SELECT *
FROM city
RIGHT JOIN country ON country.id = city.country_id;
Result :
id name country_id id name
1 | Munich | 1 | 1 | Germany
8 | Munich | 1 | 1 | Germany
2 | Ankara | 5 | 5 | Turkey
6 | Istanbul | 5 | 5 | Turkey
7 | Cape Town | 4 | 4 | South Africa
4 | New York | 3 | 3 | Usa
7 | Seattle | 3 | 3 | Usa
5 | Shanghai | 2 | 2 | ChinaFull Join
This structure combines the results of both left and right outer joins.
Syntax :
SELECT column_name(s)
FROM table1
FULL JOIN table2
ON table1.column_name = table2.column_nameExample :
SELECT *
FROM city
FULL JOIN country ON country.id = city.country_id;
Result :
id name country_id id name
1 | Munich | 1 | 1 | Germany
8 | Munich | 1 | 1 | Germany
2 | Ankara | 5 | 5 | Turkey
6 | Istanbul | 5 | 5 | Turkey
3 | Cape Town | 4 | 4 | South Africa
4 | New York | 3 | 3 | Usa
7 | Seattle | 3 | 3 | Usa
5 | Shanghai | 2 | 2 | China
9 | Boston | NULL | NULL | NULLI hope everything is clear for you. If you don’t understand any part, please feel free, ask me also if you want to do practice, you can use pg-sql.com with following scripts.
-- 1.
CREATE TABLE country(
id PRIMARY KEY,
name VARCHAR(60)
);-- 2.
CREATE TABLE city(
id PRIMARY KEY,
name VARCHAR(60),
country_id INTEGER REFERENCES country(id) ON DELETE CASCADE
);-- 3.
INSERT INTO country (id, name)
VALUES
(1, 'Germany'),
(2, 'China'),
(3, 'Usa'),
(4, 'South Africa'),
(5, 'Turkey');-- 4.
INSERT INTO city (id, name, country_id)
VALUES
(1, 'Munich', 1),
(2, 'Berlin', 1),
(3, 'Dortmund', 1),
(4, 'Frankfurt', 1),
(5, 'Ankara', 5),
(6, 'Istanbul', 5),
(7,'Izmir', 5),
(8, 'Antalya', 5),
(9, 'Çanakkale', 5),
(10, 'Cape Town', 4),
(11, 'Constantia', 4),
(12, 'Swellendam', 4),
(13, 'Mahikeng', 4),
(14, 'New York', 3),
(15, 'Boston', 3),
(16, 'Seattle', 3),
(17, 'Nevada', 3),
(18, 'Shanghai', 2),
(19, 'Beijing', 2),
(20, 'Chongqing', 2),
(21, 'Wuhan', 2);
Additionally, I used various resources for prepare this essay. I indicated in following. You can check out.
