MySQL multi-table connection operation methods (INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN)

1. What is multi-table connection?

A multi-table join is an SQL operation that combines data from two or more tables together. By joining, we can extract the associated data based on the relationship between tables (such as primary and foreign keys). Connection operation is one of the core functions of relational databases and is widely used in data analysis, report generation and other scenarios.

2. Connection types supported by MySQL

Notice:

1、OUTER JOINis a connection type, which includesLEFT JOIN、RIGHT JOINandFULL OUTER JOIN. It's withINNER JOINdifferent,OUTER JOINIt will return not only the matching rows, but also the mismatched rows (usingNULLfilling).OUTER JOINyesLEFT JOIN、RIGHT JOINandFULL OUTER JOINThe general term for .

2. By default,JOINyesINNER JOINabbreviation form of .

3. Syntax of multi-table connection

The basic syntax of multi-table joins is as follows:

SELECT List name
FROM surface1
Connection type JOIN surface2
ON surface1.List = surface2.List;

• Connection type: Can beINNER JOIN、LEFT JOIN、RIGHT JOINwait.
• ON clause: Specify the connection condition, usually the relationship between the primary key and the foreign key.

4. Practical examples Data preparation

Create two tablesPersonandAddress, and insert the test data:

CREATE TABLE Person (
    personId INT PRIMARY KEY,
    firstName VARCHAR(50),
    lastName VARCHAR(50)
);
CREATE TABLE Address (
    addressId INT PRIMARY KEY,
    personId INT,
    city VARCHAR(50),
    state VARCHAR(50)
);
INSERT INTO Person (personId, firstName, lastName) VALUES
(1, 'Allen', 'Wang'),
(2, 'Bob', 'Alice'),
(3, 'Charlie', 'Brown');
INSERT INTO Address (addressId, personId, city, state) VALUES
(1, 2, 'New York City', 'New York'),
(2, 3, 'Leetcode', 'California'),
(3, 4, 'Mountain View', 'California');

(1) INNER JOINExample

returnPersonandAddressMatched rows in the table:

SELECT , , , 
FROM Person p
INNER JOIN Address a ON  = ;

result：

(2) LEFT JOINExample

returnPersonAll rows in the table, andAddressMatched rows in the table:

SELECT , , , 
FROM Person p
LEFT JOIN Address a ON  = ;

result：

(3) RIGHT JOINExample

returnAddressAll rows in the table, andPersonMatched rows in the table:

SELECT , , , 
FROM Person p
RIGHT JOIN Address a ON  = ;

result：

(4) FULL OUTER JOINsimulation

MySQL does not support itFULL OUTER JOIN, but can be passedUNIONSimulation implementation:

SELECT , , , 
FROM Person p
LEFT JOIN Address a ON  = 
UNION
SELECT , , , 
FROM Person p
RIGHT JOIN Address a ON  = ;

result：

5. Connection performance optimization

(1) Use of indexes

In the connection field (e.g.personIdCreate indexes on ) can significantly improve query performance:

CREATE INDEX idx_personId ON Address(personId);

Note: The test data in this article creates the primary key, and the index is created by default.(2) Query optimization skills

• Avoid usingSELECT *, specify the required columns clearly.
• useEXPLAINAnalyze the query plan and make sure the query uses the index.
• Use equal value comparison in connection conditions (=), avoid using complex expressions.

This is the article about MySQL multi-table connection (INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN) here. For more related content on MySQL multi-table connection, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!