Master MySQL Joins, Inserts, Updates, and Deletes: A Complete Guide

Query

We have a student table and a class table that are stored separately, so the class_id column in the student table is a numeric foreign key.

Data example:

Join Queries

Method 1: WHERE Join

Syntax:

SELECT * FROM table1, table2 WHERE table1.foreign_key = table2.foreign_key;

Show all columns:

SELECT * FROM student, class WHERE student.class_id = class.id;

Result:

Show specific columns:

SELECT student.id, student.`name`, class.title FROM student, class WHERE student.class_id = class.id;

Result:

Method 2: LEFT Join

Using WHERE for joins mixes filtering conditions with join logic, which can be confusing. LEFT join separates the join condition from other filters.

Syntax:

SELECT * FROM table1 LEFT JOIN table2 ON table1.foreign_key = table2.foreign_key;

Show all columns:

SELECT * FROM student LEFT JOIN class ON student.class_id = class.id;

Result:

Show specific columns:

SELECT student.id, student.`name`, class.title FROM student LEFT JOIN class ON student.class_id = class.id;

Result:

Method 3: INNER Join

INNER and LEFT joins are similar; LEFT is a forward (outer) join, while INNER is a reverse (inner) join. The difference lies in the order of tables.

Other Operations

Insert (Add)

Single Row

Syntax:

INSERT INTO table(column1, column2, ...) VALUES (value1, value2, ...);

Example – add a student:

INSERT into student(name, age, gender, class_id) VALUES ("吴彦祖", 22, "男", 1);

Result:

Table content after insertion:

Multiple Rows

Syntax:

INSERT INTO table(column1, column2, ...) VALUES (value1, value2, ...), (value1, value2, ...);

Example – batch add students:

INSERT INTO student (NAME, age, gender, class_id) VALUES ("范冰冰", 18, "女", 2), ("成龙", 24, "男", 3);

Result:

Table content after batch insert:

Update

Syntax:

UPDATE <table> SET column = value WHERE <condition>;

Example – change a student's age:

UPDATE student set age = 88 where name = "张三";

Result:

Delete

Syntax:

DELETE FROM <table>;

DELETE FROM <table> WHERE <condition>;

Example – delete a student named 张三: DELETE from student where name = "张三"; Result:

Summary

This chapter concludes the discussion of join queries and adds a comprehensive overview of MySQL CRUD operations, emphasizing the distinction between LEFT and INNER joins and demonstrating how to insert both single and multiple rows.