When you work with SQL database you usually need more than 1 table. Some values are in one table and the others are in a different one = distribution of data into many tables which are connected between each other (via keys) is the principle of relational database.
There are 5 basic kinds of joins: INNER JOIN (this article), LEFT JOIN, RIGHT JOIN, CROSS JOIN, FULL JOIN
Hint: Commands INNER JOIN and JOIN are equivalent
Now or later you will need to know how to connect the tables. Let’s learn it!
INNER JOIN Syntax
SELECT [Table_A].[Column 1] ,[Table_A].[Column 2] ,[Table_B].[Column 3]FROM [Table_A] INNER JOIN [Table_B] ON [Table_A].[Id] = [Table_B].[Id]
To make it easier, imagine 2 tables. Each of the tables will consist of only 1 column [Number]:
- Table A consists of column [Number]: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10
- Table B consists of column [Number]: 1, 2, 3, 100, 150
What will be the result of the script?
SELECT
[Table_A].[Number], [Table_B].[Number]
FROM [Table_A]
INNER JOIN [Table_B]
ON [Table_A].[Number] = [Table_B].[Number];
The result is only 3 rows:
Hint: Take a look at the examples of all the possible kinds of connections of the tables to better understand the differences between each kind (see other articles). I always use the same example – football teams.
INNER JOIN Example – Football teams
We will take a look at the database table joins using football teams. We have 2 tables, take a look at them (sorry, texts in screenshots and object names are in Czech):
- [Fotbalove_tymy] ([ID], [Tym], [Id_mesto]) – table with list of the football teams
- [Mesta] ([ID], [Nazev_mesta]) – table with towns
The task is to display the list of the football teams with their hometown. Script will look like this:
SELECT [Fotbalove_tymy].[ID] ,[Fotbalove_tymy].[Tym] ,[Mesto].[Mesto]FROM [Fotbalove_tymy] INNER JOIN [Mesto] ON [Fotbalove_tymy].[Id_Mesto] = [Mesto.Id];
We create a relation between the tables. The condition by which both records from the table will get connected lies in ON clause ([Fotbalove_tymy].[Id_Mesto] = [Mesto.Id]).
Evaluation: We miss team Brno in the results. This is because in this case, ID=0 was not found in the table of towns. The requirement for right (B) table of towns from ON clause was not fulfilled therefore Brno falls out from the results.
