Working with Joins in MySQL

Intro

Often when working with SQL we have multiple tables that are related. For example, we have have a user and their preferences in two separate tables. We can combine the information in both tables using SQL joins. In this article we will learn how to use MySQL joins.

The Syntax

The basic syntax of using a Join is as follows:

select [columns] from table1
	JOIN table2 ON table1.column = table2.column.

Here we join two tables. We use the ON clause to determine how the two table match. For example, we may have a user_id column in both tables to join on.

There are four types of joins:

• Inner Join
• Left Join
• Right Join
• Cross Join

We can also join with another table, multiple tables, or on the same table, called a Self Join.

Getting Setup

We will be using docker in this article, but feel free to install your database locally instead. Once you have docker installed, create a new file called docker-compose.yml and add the following.

version: '3'
 
services:
  db:
    image: mysql:latest
    container_name: db
    environment:
      MYSQL_ROOT_PASSWORD: root_pass
      MYSQL_DATABASE: app_db
      MYSQL_USER: db_user
      MYSQL_PASSWORD: db_user_pass
    ports:
      - "6033:3306"
    volumes:
      - dbdata:/var/lib/mysql
  phpmyadmin:
    image: phpmyadmin/phpmyadmin
    container_name: pma
    links:
      - db
    environment:
      PMA_HOST: db
      PMA_PORT: 3306
      PMA_ARBITRARY: 1
    restart: always
    ports:
      - 8081:80

volumes:
  dbdata:

Next, run docker-compose up.

Now, navigate to http://localhost:8081/ to access phpMyAdmin. Then log in with the username root and pass root_pass.

Click the SQL tab and you are ready to go.

Creating a DB

CREATE TABLE members (
    member_id INT AUTO_INCREMENT,
    name VARCHAR(100),
    PRIMARY KEY (member_id)
);

CREATE TABLE committees (
    committee_id INT AUTO_INCREMENT,
    name VARCHAR(100),
    PRIMARY KEY (committee_id)
);

INSERT INTO members(name)
VALUES('John'),('Jane'),('Mary'),('David'),('Amelia');

INSERT INTO committees(name)
VALUES('John'),('Mary'),('Amelia'),('Joe');

Inner Join Example

Let’s begin with an inner join. The inner join well select all rows that have a match between two tables and join them together. Using our example tables, if we have a member that is not in a committee, or a committee that does not have members, they will be ignored.

SELECT 
    m.member_id, 
    m.name as member_name, 
    c.committee_id, 
    c.name as committee_name
FROM
    members m
INNER JOIN committees c ON c.name = m.name;

Left Join

In a left join, the first table rows will all be kept. Using our example below, members is the first table. So any member that is not in a committee will still be kept and the joined column values will be null.

SELECT 
    m.member_id, 
    m.name as member_name, 
    c.committee_id, 
    c.name as committee_name
FROM
    members m
LEFT JOIN committees c ON c.name = m.name;

Right Join

The right join is the opposite of the left join. In our example, we will keep all committees that have no members.

SELECT 
    m.member_id, 
    m.name as member_name, 
    c.committee_id, 
    c.name as committee_name
FROM
    members m
RIGHT JOIN committees c ON c.name = m.name;