SQL FULL JOIN – Joining Tables in SQL With Explanation in Excel

Last modified date:

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)

My name is Jan Zedníček and I work as a BI Developer at Kentico Software in Brno. Mostly you can see me there working in the office, but I also work partly as a freelancer. I have been working as a freelancer for many companies for more than 5 years, but Kentico is the matter of my heart. I used to work as a financial controller at companies like Aero Vodochody or Sberbank and I also used to be a bond program manager in Unicapital Investment group. When I am not at work, I like playing volleyball, chess, doing a workout in the gym and I enjoy tasting of best quality rums. I am trying to summarize all my knowledge on this website not to forget them (because of the rum effect, you know =) and to put them forward to anyone. Don´t worry about asking for help or writing some comments.

Category: SQL Basics Tags:

About Ing. Jan Zedníček - BI Developer, Finance controller

My name is Jan Zedníček and I work as a BI Developer at Kentico Software in Brno. Mostly you can see me there working in the office, but I also work partly as a freelancer. I have been working as a freelancer for many companies for more than 5 years, but Kentico is the matter of my heart. I used to work as a financial controller at companies like Aero Vodochody or Sberbank and I also used to be a bond program manager in Unicapital Investment group. When I am not at work, I like playing volleyball, chess, doing a workout in the gym and I enjoy tasting of best quality rums. I am trying to summarize all my knowledge on this website not to forget them (because of the rum effect, you know =) and to put them forward to anyone. Don´t worry about asking for help or writing some comments.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.