1 Introduction

Source: C.L. Moffatt

A topic which must not be missing in the course of SQL are Joins. In this article I will talk about the different types of joins and explain how they work and what differences they have to each other.

2 Preparation

For the following examples, I set up a new database.

SET LANGUAGE ENGLISH


CREATE DATABASE Joins;

USE Joins;

Below I will create 4 example tables that we will continue to work with.

CREATE TABLE Countries_Main
    (Country_ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
     Country_Name VARCHAR(100) NOT NULL)
    ;

INSERT INTO Countries_Main (Country_Name) VALUES ('Egypt')
INSERT INTO Countries_Main (Country_Name) VALUES ('Brazil')
INSERT INTO Countries_Main (Country_Name) VALUES ('Germany')
INSERT INTO Countries_Main (Country_Name) VALUES ('Malta')

SELECT * FROM Countries_Main

CREATE TABLE Countries_Capital
    (ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
     Country_ID INT NOT NULL FOREIGN KEY REFERENCES Countries_Main(Country_ID),
     Capital_Name VARCHAR(100) NOT NULL)
    ;

INSERT INTO Countries_Capital (Country_ID, Capital_Name) VALUES (1, 'Cairo')
INSERT INTO Countries_Capital (Country_ID, Capital_Name) VALUES (2, 'Brasilia')
INSERT INTO Countries_Capital (Country_ID, Capital_Name) VALUES (3, 'Berlin')
INSERT INTO Countries_Capital (Country_ID, Capital_Name) VALUES (4, 'Valletta')

SELECT * FROM Countries_Capital

CREATE TABLE Countries_Inhabitants
    (ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
     Country_ID INT NOT NULL FOREIGN KEY REFERENCES Countries_Main(Country_ID),
     Inhabitants FLOAT NOT NULL)
    ;

INSERT INTO Countries_Inhabitants (Country_ID, Inhabitants) VALUES (1, 93.4)
INSERT INTO Countries_Inhabitants (Country_ID, Inhabitants) VALUES (2, 207.9)
INSERT INTO Countries_Inhabitants (Country_ID, Inhabitants) VALUES (4, 0.44)

SELECT * FROM Countries_Inhabitants

CREATE TABLE Countries_Land_Area
    (ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
     Country_ID INT NOT NULL FOREIGN KEY REFERENCES Countries_Main(Country_ID),
     Land_Area FLOAT NOT NULL)
    ;

INSERT INTO Countries_Land_Area (Country_ID, Land_Area) VALUES (1, 1)
INSERT INTO Countries_Land_Area (Country_ID, Land_Area) VALUES (2, 8.5)
INSERT INTO Countries_Land_Area (Country_ID, Land_Area) VALUES (3, 0.36)

SELECT * FROM Countries_Land_Area

Here again briefly an overview of all 4 created tables:

SELECT * FROM Countries_Main
SELECT * FROM Countries_Capital
SELECT * FROM Countries_Inhabitants
SELECT * FROM Countries_Land_Area

3 Types of Joins

Below I will walk through the different types of joins and explain how they work using the sample tables I have created. Furthermore I will always show at the beginning, with which tables exactly I will execute the following join.

SELECT * FROM Countries_Inhabitants
SELECT * FROM Countries_Land_Area

SELECT t1.Country_ID, 
       t1.Inhabitants, 
       t2.Land_Area
    FROM Countries_Inhabitants AS t1
    INNER JOIN Countries_Land_Area AS t2
        ON t1.Country_ID = t2.Country_ID
    ;

SELECT * FROM Countries_Main
SELECT * FROM Countries_Capital

SELECT t1.Country_ID,
       t1.Country_Name,
       t2.Capital_Name
    FROM Countries_Main AS t1
    LEFT JOIN Countries_Capital AS t2
        ON t1.Country_ID = t2.Country_ID
    ;

SELECT * FROM Countries_Main
SELECT * FROM Countries_Inhabitants

SELECT t1.Country_ID,
       t1.Country_Name,
       t2.Inhabitants
    FROM Countries_Main AS t1
    LEFT JOIN Countries_Inhabitants AS t2
        ON t1.Country_ID = t2.Country_ID
    ;

SELECT * FROM Countries_Main
SELECT * FROM Countries_Inhabitants

SELECT t1.Country_ID,
       t1.Country_Name,
       t2.Inhabitants
    FROM Countries_Main AS t1
    RIGHT JOIN Countries_Inhabitants AS t2
        ON t1.Country_ID = t2.Country_ID
    ;

SELECT t1.Country_ID,
       t2.Country_Name,
       t1.Inhabitants
    FROM Countries_Inhabitants AS t1
    LEFT JOIN Countries_Main AS t2
        ON t1.Country_ID = t2.Country_ID
    ;

...
    FROM Countries_Main AS t1
    LEFT JOIN Countries_Inhabitants AS t2
...

SELECT * FROM Countries_Inhabitants
SELECT * FROM Countries_Land_Area

SELECT t1.Country_ID AS Country_ID_t1,
       t1.Inhabitants,
       t2.Country_ID AS Country_ID_t2,
       t2.Land_Area
    FROM Countries_Inhabitants AS t1
    FULL OUTER JOIN Countries_Land_Area AS t2
        ON t1.Country_ID = t2.Country_ID
    ;

SELECT t1.Country_ID, 
       t1.Inhabitants, 
       t2.Land_Area
    FROM Countries_Inhabitants AS t1
    INNER JOIN Countries_Land_Area AS t2
        ON t1.Country_ID = t2.Country_ID
    ;

SELECT * FROM Countries_Inhabitants
SELECT * FROM Countries_Land_Area

SELECT t1.Country_ID AS Country_ID_t1,
       t1.Inhabitants,
       t2.Country_ID AS Country_ID_t2,
       t2.Land_Area
    FROM Countries_Inhabitants AS t1
    LEFT JOIN Countries_Land_Area AS t2
        ON t1.Country_ID = t2.Country_ID
        WHERE t2.Country_ID IS NULL
    ;

SELECT * FROM Countries_Inhabitants
SELECT * FROM Countries_Land_Area

SELECT t1.Country_ID AS Country_ID_t1,
       t1.Inhabitants,
       t2.Country_ID AS Country_ID_t2,
       t2.Land_Area
    FROM Countries_Inhabitants AS t1
    RIGHT JOIN Countries_Land_Area AS t2
        ON t1.Country_ID = t2.Country_ID
        WHERE t1.Country_ID IS NULL
    ;

SELECT * FROM Countries_Inhabitants
SELECT * FROM Countries_Land_Area

SELECT t1.Country_ID AS Country_ID_t1,
       t1.Inhabitants,
       t2.Country_ID AS Country_ID_t2,
       t2.Land_Area
    FROM Countries_Inhabitants AS t1
    FULL OUTER JOIN Countries_Land_Area AS t2
        ON t1.Country_ID = t2.Country_ID
        WHERE t1.Country_ID IS NULL OR t2.Country_ID IS NULL
    ;

SELECT t1.Country_ID AS Country_ID_t1,
       t1.Inhabitants,
       t2.Country_ID AS Country_ID_t2,
       t2.Land_Area
    FROM Countries_Inhabitants AS t1
    FULL OUTER JOIN Countries_Land_Area AS t2
        ON t1.Country_ID = t2.Country_ID
    ;

4 Multiple Joins

Of course, you also have the possibility to combine several joins with each other.

SELECT * FROM Countries_Main
SELECT * FROM Countries_Capital
SELECT * FROM Countries_Inhabitants
SELECT * FROM Countries_Land_Area

In each of the tables the primary key (‘Country_ID’) from the main table ‘Countries_Main’ is stored as the foreing key. After that we join all tables together:

SELECT t1.Country_ID,
       t1.Country_Name,
       t2.Capital_Name,
       t3.Inhabitants,
       t4.Land_Area
    FROM Countries_Main AS t1
    LEFT JOIN Countries_Capital AS t2
        ON t1.Country_ID = t2.Country_ID
    LEFT JOIN Countries_Inhabitants AS t3
        ON t1.Country_ID = t3.Country_ID
    LEFT JOIN Countries_Land_Area AS t4
        ON t1.Country_ID = t4.Country_ID
    ;

5 Join Tables from 2 different Databases

Furthermore, it is also possible to join different tables from 2 different databases. Let’s take another look at our main table in advance:

Now I create another database called ‘Joins2’ and add another sample table (‘Countries_Currency’) there.

CREATE DATABASE Joins2;

USE Joins2;

Note: A foreign key relationship between two databases is not possible with SQL Server. Therefore I will leave out this definition when defining the columns.

CREATE TABLE Countries_Currency
    (ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
     Country_ID INT NOT NULL,
     Currency VARCHAR(100) NOT NULL)
    ;

INSERT INTO Countries_Currency (Country_ID, Currency) VALUES (1, 'Egyptian Pound')
INSERT INTO Countries_Currency (Country_ID, Currency) VALUES (2, 'Brazilian Real')
INSERT INTO Countries_Currency (Country_ID, Currency) VALUES (3, 'Euro')
INSERT INTO Countries_Currency (Country_ID, Currency) VALUES (4, 'Maltese Lira')

SELECT * FROM Countries_Currency

The two tables (‘Countries_Capital’ & ‘Countries_Currency’) from the two databases (‘Joins’ & ‘Joins2’) can be joined together using the following syntax:

SELECT table_1.*,
       table_2.*
    FROM [Database_1].[Table_Schema].[Table_Name_1] table_1
    JOIN [Database_2].[Table_Schema].[Table_Name_2] table_2 
        ON table_1.id = table_2.id
    ;

And here is the syntax applied to our present case:

SELECT t1.Country_ID,
       t1.Country_Name,
       t2.Currency
    FROM [Joins].[dbo].[Countries_Main] AS t1
    LEFT JOIN [Joins2].[dbo].Countries_Currency AS t2
        ON t1.Country_ID = t2.Country_ID
    ;

6 Conclusion

In this post I have shown what different types of joins exist and how to use them.