SQL LEFT OUTER JOIN – Joining tables in SQL With Explanation in Excel

Last modified date:

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 (this article), RIGHT JOIN, CROSS JOIN, FULL JOIN

Hint: Commands LEFT OUTER JOIN and LEFT JOIN are equivalent

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

LEFT OUTER JOIN Syntax

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

The result is 10 rows:

SQL LEFT 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 use football teams as an example in all of them.

LEFT JOIN example with 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

We will 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]
     LEFT JOIN [Mesto]
       ON [Fotbalove_tymy].[Id_Mesto] = [Mesto.Id];

We create a relation between the tables. The requirement by which both records from the table will get connected lies in ON clause.

Left-join-result

Apart from INNER JOIN here we have all football teams mentioned in one table as a result. Brno team is shown (left table) but since there is no record of it in table of towns [město_id] = 0 the value [mesto] = NULL – take a look at the difference between this solution and Inner join solution.

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.