(SQL) Types of JOIN

Sep 2, 2022·

6 min read

Introduction

INNER, LEFT, RIGHT, CROSS are covered.

I used MySQL.

Terms

Parent-Child Relationship

Tables that have foreign keys are called 'child tables', while tables that have data about the foreign keys are called 'parent tables'.

Settings

Please execute this code.

-- creating the database
DROP DATABASE IF EXISTS market;
CREATE DATABASE market;

-- using the database
USE market;

-- making the tables
CREATE TABLE product (
  id INT PRIMARY KEY,
  name VARCHAR(10) NOT NULL,
  description TEXT
);

CREATE TABLE member (
  id INT PRIMARY KEY,
  name VARCHAR(10) NOT NULL,
  address VARCHAR(100) NOT NULL
);

CREATE TABLE purchase (
  id INT PRIMARY KEY,
  prod_id INT NOT NULL,
  mem_id INT NOT NULL
);

-- inserting the data
INSERT INTO product VALUES(1, 'product1', 'It does function1.');
INSERT INTO product VALUES(2, 'product2', 'It does function2.');

INSERT INTO member VALUES(1, 'member1', 'address1');
INSERT INTO member VALUES(2, 'member2', 'address2');
INSERT INTO member VALUES(3, 'member3', 'address3');

INSERT INTO purchase VALUES(1, 1, 1);
INSERT INTO purchase VALUES(2, 1, 2);
INSERT INTO purchase VALUES(3, 2, 1);
INSERT INTO purchase VALUES(4, 2, 4);

-- checking
SELECT * FROM product;
SELECT * FROM member;
SELECT * FROM purchase;

The tables look like these.

'member' and 'product' are the parent tables, and 'purchase' is the child table.

|member|

idnameaddress
1member1address1
2member2address2
3member3address3

|product|

idnamedescription
1product1It does function1.
2product2It does function2.

|purchase|

idprod_idmem_id
111
212
321
424

INNER

When Parent Tables Come after 'FROM'

SELECT purchase.id AS pur_id, prod_id, mem_id, member.name, member.address
    FROM member
    INNER JOIN purchase
    ON purchase.mem_id = member.id;
pur_idprod_idmem_idnameaddress
111member1address1
212member2address2
321member1address1

'purchase' table, which is the child table, brings the data from 'member' table, which is the parent table.

If a row in a child table has no corresponding data in the parent table, it is not reflected. In this case, it is [pur_id=4].

When Child Tables Come after 'FROM'

SELECT purchase.id AS pur_id, prod_id, mem_id, member.name, member.address
    FROM purchase
    INNER JOIN member
    ON purchase.mem_id = member.id;

The result is the same as the above.

LEFT, RIGHT

Case 1

There are two sub-cases for this case.

  1. LEFT JOIN: Parent tables come after 'FROM'.

  2. RIGHT JOIN: Child tables come after 'FROM'.

This is an example of LEFT JOIN.

SELECT purchase.id AS pur_id, prod_id, mem_id, member.name, member.address
    FROM member
    LEFT JOIN purchase
    ON purchase.mem_id = member.id;

This is an example of RIGHT JOIN.

SELECT purchase.id AS pur_id, prod_id, mem_id, member.name, member.address
    FROM purchase
    RIGHT JOIN member
    ON purchase.mem_id = member.id;
pur_idprod_idmem_idnameaddress
321member1address1
111member1address1
212member2address2
NULLNULLNULLmember3address3

'member' table, which is the parent table brings the data from 'purchase' table, which is the child table.

If the child table has multiple corresponding data, then multiple rows are generated. In this case, 'member1'.

If the child table has no corresponding data, then 'null' is filled for the data. In this case, [name=member3].

Case 2

There are two sub-cases for this case.

  1. LEFT JOIN: Child tables come after 'FROM'.

  2. RIGHT JOIN: Parent tables come after 'FROM'.

This is an example of LEFT JOIN.

SELECT purchase.id AS pur_id, prod_id, mem_id, member.name, member.address
    FROM purchase
    LEFT JOIN member
    ON purchase.mem_id = member.id;

This is an example of RIGHT JOIN.

SELECT purchase.id AS pur_id, prod_id, mem_id, member.name, member.address
    FROM member
    RIGHT JOIN purchase
    ON purchase.mem_id = member.id;
pur_idprod_idmem_idnameaddress
111member1address1
212member2address2
321member1address1
424NULLNULL

'purchase' table, which is the child table, brings the data from 'member' table, which is the parent table.

Even if a row in a child table has no corresponding data in the parent table, a new row is made for the row. In this case, it is [pur_id=4].

CROSS

SELECT * FROM member CROSS JOIN product;
idnameaddressidnamedescription
3member3address31product1It does function1.
2member2address21product1It does function1.
1member1address11product1It does function1.
3member3address32product2It does function2.
2member2address22product2It does function2.
1member1address12product2It does function2.
SELECT * FROM product CROSS JOIN member;
idnamedescriptionidnameaddress
2product2It does function2.1member1address1
1product1It does function1.1member1address1
2product2It does function2.2member2address2
1product1It does function1.2member2address2
2product2It does function2.3member3address3
1product1It does function1.3member3address3

Every row in a table after CROSS JOIN brings the whole rows of a table after FROM.

'parent-child relationship' is not used. So, foreign keys are not needed and ON keyword is not used.

The number of rows is calculated by multiplying the number of rows of each table. In the above example, it is 3('member') × 2('product') = 6.