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

When we work with SQL database we usually need to work with 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, LEFT JOIN, RIGHT JOIN, CROSS JOIN, FULL JOIN (this article)

Hint: Commands FULL OUTER JOIN and  FULL JOIN are equivalent

Now or later you will need to know how to connect the tables. Let’s learn it!

FULL OUTER JOIN Syntax

SELECT
  [Table_A].[Column 1]
  ,[Table_A].[Column 2]
  ,[Table_B].[Column 3]
FROM [Table_A]
     FULL 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]
     FULL JOIN [Table_B]
       ON [Table_A].[Number] = [Table_B].[Number];

The result is 12 records:

sql full join example in excel

Hint: Take a look at the examples of all the possible kinds of table joins to better understand the differences between each kind (see other articles). I always use the same example – football teams.

FULL 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

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]
     FULL 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.

full-join-vysledek

All records from both tables are displayed. On places where the ON clause requirement was not met (“Fotbalove_tymy.Id_Mesto=Mesto.Id”) we see NULL.

  • Football team Brno has ID_mesto=0. This ID does not exist in right (B) table “Mesto” and therefore we do not see any town connected to team Brno
  • On the other hand, Ostrava and Horni Dolni do not have any football team in their table and therefore fields fotbalove_tymy.[ID] and fotbalove_tymy.[Tym] have NULL values (not found)
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 *