Joins are a fundamental operation in relational databases because they allow us to correlate and combine information across multiple tables.

In this notebook, we will see some examples of different types of joins.

%load_ext sql
%sql postgresql://postgres:postgres@localhost/university

The sql extension is already loaded. To reload it, use:
  %reload_ext sql

Create small example¶

%load_ext sql
%config SqlMagic.style = '_DEPRECATED_DEFAULT'
%sql postgresql://postgres:postgres@localhost/university

The sql extension is already loaded. To reload it, use:
  %reload_ext sql

%sql drop table if exists R1;
%sql drop table if exists R2;
%sql drop table if exists R3;

%sql create table R1 (A varchar(10), B int);
%sql insert into R1 values('alpha', 10), ('beta', 20), ('gamma', 20), ('rho', 30), ('psi', 50);

%sql create table R2 (B int, C varchar(10));
%sql insert into R2 values (10, 'one'), (20, 'two'), (40, 'three');

%sql create table R3 (C varchar(10), D varchar(10));
%sql insert into R3 values ('one', 'alpha'), ('two', 'beta');

 * postgresql://postgres:***@localhost/university
   sqlite://
Done.
 * postgresql://postgres:***@localhost/university
   sqlite://
Done.
 * postgresql://postgres:***@localhost/university
   sqlite://
Done.
 * postgresql://postgres:***@localhost/university
   sqlite://
Done.
 * postgresql://postgres:***@localhost/university
   sqlite://
5 rows affected.
 * postgresql://postgres:***@localhost/university
   sqlite://
Done.
 * postgresql://postgres:***@localhost/university
   sqlite://
3 rows affected.
 * postgresql://postgres:***@localhost/university
   sqlite://
Done.
 * postgresql://postgres:***@localhost/university
   sqlite://
2 rows affected.

[]

%sql SELECT * FROM R1;

 * postgresql://postgres:***@localhost/university
   sqlite://
5 rows affected.

%sql SELECT * FROM R2;

 * postgresql://postgres:***@localhost/university
   sqlite://
3 rows affected.

%sql SELECT * FROM R3;

 * postgresql://postgres:***@localhost/university
   sqlite://
2 rows affected.

Cartesian Product (Cross-product)¶

This is the most basic way to combine tuples across two tables.

Every tuple in one relation is concatenated with every tuple from the other relation, so the result contains m * n tuples, where m and n are the number of tuples in the two relations.

You almost never want to do cross product by itself, although there are some use cases.

Trying to do a cross-product with three relations will result in an every larger relation (see example below).

%sql SELECT * FROM R1, R2;

 * postgresql://postgres:***@localhost/university
   sqlite://
15 rows affected.

%sql SELECT * FROM R1, R2, R3;

 * postgresql://postgres:***@localhost/university
   sqlite://
30 rows affected.

Standard Joins (Theta Joins)¶

The standard way to do joins is by adding a selection predicate to the above queries.

The predicate can pretty much be anything you want, although “equality” joins are most common.

%%sql
-- Join R1 and R2 with the same value on B.
SELECT *
    FROM R1, R2
    WHERE R1.B = R2.B;

 * postgresql://postgres:***@localhost/university
   sqlite://
3 rows affected.

%%sql
-- Join R1, R2, R3, with the same values for B and C.
SELECT *
    FROM R1, R2, R3
    WHERE R1.B = R2.B
        AND R2.C = R3.C;

 * postgresql://postgres:***@localhost/university
   sqlite://
3 rows affected.

%%sql
SELECT *
    FROM R1, R2
    WHERE R1.B < R2.B;

 * postgresql://postgres:***@localhost/university
   sqlite://
5 rows affected.

%%sql
SELECT *
    FROM R1, R2
    WHERE R1.B + R2.B = 40;

 * postgresql://postgres:***@localhost/university
   sqlite://
3 rows affected.

Inner Join¶

The following is an alternate way to write a join query, using the keyword “inner join” The only reason to use it is stylistic.

As we will see below, this style of writing queries is essential for outer-joins, and writing inner joins in this fashion may make things look similar.

%%sql
SELECT *
    FROM R1 INNER JOIN R2
    ON R1.B = R2.B;

 * postgresql://postgres:***@localhost/university
   sqlite://
3 rows affected.

%%sql
SELECT *
    FROM (R1 INNER JOIN R2 on R1.B = R2.B)
        INNER JOIN R3 ON R2.C = R3.C;

 * postgresql://postgres:***@localhost/university
   sqlite://
3 rows affected.

Natural Joins¶

A natural join is a type of inner join where the join condition is inferred by identifying common attributes in the two relations, and doing an equality on them.

Because they can lead to unexpected results if you are not careful.

Note: Unlike other types of joins, a natural join removes the extra occurrence of the join attribute (e.g., “b” below).

%%sql
SELECT *
    FROM R1 NATURAL JOIN R2;

 * postgresql://postgres:***@localhost/university
   sqlite://
3 rows affected.

%%sql
SELECT *
    FROM R1 NATURAL JOIN R2
        NATURAL JOIN R3;

 * postgresql://postgres:***@localhost/university
   sqlite://
3 rows affected.

Outer joins¶

In many cases, there is a need to keep all the tuples from one (or both) of the relations in the output, even if there is no match. Outer joins are used for that purpose.

E.g., if I am doing a join between “department” and “instructor” on dept_name. Even if a department does not have any instructor, I might want the tuple to be present in the result output.

There are three types of outerjoins -- left, right, and full.

The left outer join is shown below: any tuple from the left relation that did not have a corresponding tuple in the right relation, is added to the output with “NULLs” in the columns from the right relation (in this case, the tuple “rho 30” which did not appear in the join results above -- attribute b_1 and c which came from R2 are set to NULL).

%%sql
SELECT *
    FROM R1 LEFT OUTER JOIN R2
        ON R1.B = R2.B;

 * postgresql://postgres:***@localhost/university
   sqlite://
5 rows affected.

Right outer join does the opposite, whereas a full outer join includes tuples from both relations that don’t match.

%%sql
SELECT *
    FROM R1 RIGHT OUTER JOIN R2
        ON R1.B = R2.B;

 * postgresql://postgres:***@localhost/university
   sqlite://
4 rows affected.

%%sql
SELECT *
    FROM R1 FULL OUTER JOIN R2
        ON R1.B = R2.B;

 * postgresql://postgres:***@localhost/university
   sqlite://
6 rows affected.

%%sql
SELECT *
    FROM (R1 FULL OUTER JOIN R2
              ON R1.B = R2.B)
        FULL OUTER JOIN R3
            ON R2.C = R3.C;

 * postgresql://postgres:***@localhost/university
   sqlite://
6 rows affected.

Semi-joins¶

Semi-join is not an explicit SQL keyword, but is a common Relational Algebra Operation (and has its own symbol). R1 semi-join R2 is simply the R1 tuples that have a match in R2. The output does not include any attributes from R2.

The way to do this in SQL is through a subquery. As you can see, the tuple “rho, 30” does not appear because it does not have a match in R2.

%sql select * from R1 where R1.B in (select B from R2)

 * postgresql://postgres:***@localhost/university
   sqlite://
3 rows affected.

Anti-join¶

Anti-join is the opposite concept -- it includes tuples from the left relation which DO NOT have a match in the right relation. So in this case, it will only include the “rho, 30” tuple.

Note that: R1 semi-join R2 and R1 anti-join R2 form a disjoint partition of R1.

%sql select * from R1 where R1.B not in (select B from R2)

 * postgresql://postgres:***@localhost/university
   sqlite://
2 rows affected.