• 5. 11. 2019
  • Ing. Jan Zedníček - Data & Finance
  • 0

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:

SQL RIGHT JOIN Example

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])

fotbalove-tymy-priklad

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).

Right-join-result-e1444499913824

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
Rate this post
Tags:

Ing. Jan Zedníček - Data & Finance

My name is Jan Zedníček and I work as a freelancer. I have been working as a freelancer for many companies for more than 10 years.
I used to work as a financial controller, analyst and manager at many different companies in field of banking and manufacturing. When I am not at work, I like playing volleyball, chess, doing a workout in the gym.

Leave a Reply

Your email address will not be published. Required fields are marked *