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 = division of the information 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, LEFT JOIN, RIGHT JOIN (this article), CROSS JOIN, FULL JOIN
Hint: Commands RIGHT OUTER JOIN and RIGHT JOIN are equivalent
Now or later you will need to know how to connect the tables. Let’s learn it!
RIGHT OUTER JOIN Syntax
SELECT [Table_A].[Column 1] ,[Table_A].[Column 2] ,[Table_B].[Column 3]FROM [Table_A] RIGHT OUTER JOIN [Table_B] ON [Table_A].[Id] = [Table_B].[Id]
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]
RIGHT JOIN [Table_B]
ON [Table_A].[Number] = [Table_B].[Number];
The result is 5 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. I always use the example with football teams (see other articles).
RIGHT 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])
- [Mesta] ([ID], [Nazev_mesta])
The task is to display the list of football teams with their hometown. Script will look like this:
SELECT [Fotbalove_tymy].[ID] ,[Fotbalove_tymy].[Tym] ,[Mesto].[Mesto]FROM [Fotbalove_tymy] RIGHT JOIN [Mesto] ON [Fotbalove_tymy].[Id_Mesto] = [Mesto.Id];
What are we doing? We create a relation between the tables. The requirement by which both records from the table will get connected lies in ON clause. (Fotbalove_tymy.Id_Mesto=Mesto.Id).
All the possible towns from “Mesto” table (right) are shown as a result. Football teams are attached to them.
- We cannot find Brno in the table since it is not added in “Mesto” table. Right join will automatically remove the town
- On the other hand Ostrava and Horni Dolni exists in the table with towns. However, we do not have an attachable team for them therefore fields [ID] and [Tym] NULL
