In today’s article, we will explain the Use of Inner Join, which allows us to see the intersecting data of two tables.
Join and Inner Join mean the same thing, we can get the same result by typing both ways.
Before using Inner Join, we need to find common columns to join the tables.
As seen in the picture below, the common intersection tables are the id and cityid columns, and we can combine them by equating them with each other.
General usage;
|
1 2 |
SELECT*FROM tablo_1 as takma_isim INNER JOIN tablo_2 as takma_isim ON takma_isim.tablo_1=takma_isim.tablo_2 |
Let’s create a database for the inner join below and insert data into it.
|
1 2 3 4 5 6 7 8 9 |
CREATE DATABASE join_ornek; CREATE TABLE Sehirler(id int,SehirIsmi text); CREATE TABLE MeshurYiyecekler(id integer,Sehirid integer,YiyecekIsmi text); INSERT INTO Sehirler(id,SehirIsmi)VALUES(1,'Adana'); INSERT INTO Sehirler(id,SehirIsmi)VALUES(42,'Konya'); INSERT INTO Sehirler(id,SehirIsmi)VALUES(3,'Afyon'); INSERT INTO MeshurYiyecekler(id,Sehirid,YiyecekIsmi)VALUES(1,1,'Kebap'); INSERT INTO MeshurYiyecekler(id,Sehirid,YiyecekIsmi)VALUES(2,42,'Etli Ekmek'); INSERT INTO MeshurYiyecekler(id,Sehirid,YiyecekIsmi)VALUES(3,3,'Kaymak'); |
We have created our Cities and MeshurYiyecekler tables, now let’s write the plgpsql sentence that will show which dishes are well prepared in which cities.
|
1 |
SELECT s.sehirismi,my.yiyecekismi FROM sehirler as s INNER JOIN meshuryiyecekler as my ON s.id=my.sehirid; |
In the example above, we combined the two tables and wrote the columns of these tables that we want to be seen between the words SELECT and FROM, and we saw which products are famous for which dishes.
If we add a record to the Cities table and do not add the famous food of this city to the MeshurYiyecekler table, this record will not come up during the join process.
For testing, let’s add a record to the Cities table, but not add the famous food of the relevant city to the MeshurYiyecekler table.
|
1 |
SELECT s.sehirismi,my.yiyecekismi FROM sehirler as s INNER JOIN meshuryiyecekler as my ON s.id=my.sehirid; |
As seen above, it does not appear despite adding the province of Gaziantep.
This is because there is no equivalent in the MeshurYiyecekler table.
As you can see, when we ran the INNER JOIN script that we just ran, there was no record of Gaziantep.
The “s” at the end of the Cities table and the “my” at the end of the MeshurYiyecekler table are called alias.
In this way, you can write your scripts in a more understandable way.
As you can see in the script below, if we have defined alias, we need to draw the columns we need by using alias in the select part.
If there is a column with the same name in both tables and you write the name of the column in the select section without defining alias, you will get an error like the one below.
|
1 |
select ID,SehirIsmi,YiyecekIsmi from Sehirler as s INNER JOIN MeshurYiyecekler my ON s.ID=my.SehirID |
When we query without using Alias like the query above, we get an error when we try to pull the ID column with the same name.
Msg 209, Level 16, State 1, Line 2
Ambiguous column name ‘ID’.
