Make writing a habit together! This is the 9th day of my participation in the “Gold Digging Day New Plan · April More text Challenge”. Click here for more details.

Write at the front 👀

Multi-table query process: calculate the Cartesian product of multiple tables, filter the records in the cartesian product based on some criteria

Note: When querying multiple tables, use the fields associated with the two tables as conditions

1. Data preparation ✨

Create a person identity table
CREATE TABLE identity(
id INT PRIMARY KEY AUTO_INCREMENT,# key
 name varchar(20)                 # Identity name
);
Add department table data
INSERT INTO identity (name) VALUES('pirates'), ('navy'), ('Revolutionary Army'), ('Dragon Man');

Create op character table
CREATE TABLE op (
	id INT PRIMARY KEY AUTO_INCREMENT,# key
	name VARCHAR(20),                # the name
	gender enum('male'.'woman'.'人妖'),# gender
	fruit VARCHAR(30), # Fruit power
    reward BIGINT.# Reward money, unit: Bailey
	ity_id INT.Outside the # key
	FOREIGN KEY (ity_id) REFERENCES identity (id) # op table associated with identity table
);

-- Add character data
INSERT INTO op (name,gender,fruit,reward,ity_id) VALUES ('luffy'.1.'Fruit of Man - Phantom Beast - Nika Form'.1500000000.1);
INSERT INTO op (name,gender,fruit,reward,ity_id) VALUES ('sauron'.1.null.320000000.1);
INSERT INTO op (name,gender,fruit,reward,ity_id) VALUES ('yellow apes'.1.'Sparkling Fruit'.null.2);
INSERT INTO op (name,gender,fruit,reward,ity_id) VALUES (alita.2.'Slippery fruit'.5000000.1);
INSERT INTO op (name,gender,fruit,reward,ity_id) VALUES ('Ivankov'.3.'Hormonal fruit'.null.3);
INSERT INTO op (name,gender,fruit,reward,ity_id) VALUES ('saab'.1.'Burn the fruit'.602000000.3);
INSERT INTO op (name,gender,fruit,reward,ity_id) VALUES ('XiaoFeng'.3.'Imitation fruit'.32000000.1);
INSERT INTO op (name,gender,fruit,reward,ity_id) VALUES ('ace'.1.'Burn the fruit'.550000000.1);
INSERT INTO op (name,gender,fruit,reward,ity_id) VALUES ('Dusky'.2.null.null.2);
INSERT INTO op (name,gender,fruit,reward,ity_id) VALUES ('Little Sadie'.2.null.null.2);
INSERT INTO op (name,gender,fruit,reward,ity_id) VALUES ('the warring states'.1.'Fruit of All - Phantom Animal Species - Buddha Form'.null.2);
INSERT INTO op (name,gender,fruit,reward,ity_id) VALUES ('bear'.1.'Meatball fruit'.296000000.3);
INSERT INTO op (name,gender,fruit,reward,ity_id) VALUES ('red'.1.'Face fruit'.4028900000.1);
INSERT INTO op (name,gender,fruit,reward,ity_id) VALUES ('robin'.2.'Flower flower fruit'.130000000.1);
INSERT INTO op (name,gender,fruit,reward,ity_id) VALUES ('Joker'.1.'Thread fruit'.340000000.null);
Copy the code

Cartesian product phenomenon 🎃

The essence of cartesian product: permutations and combinations

1️ Cartesian product 🎭

Cartesian product formula: Number of data in table A * number of data in table B = Cartesian product

-- Requirement: Query all people and their identities
SELECT * FROM op,identity; This query will display 15*4=60 records
Copy the code

If you query directly without conditions, the following results occur, which we callCartesian product phenomenon👇

2 How to remove the effect of cartesian product phenomenon 👨🔧

We find that not all combinations of data are useful, so we need to filter out useless data through conditions.

/* Set the filter criteria */ 
Select * from op where ity_id = id
SELECT * FROM op,identity WHERE op.ity_id=identity.id;
SQL > alter table L (R); SQL > alter table L (R)
SELECT * FROM op AS L,identity AS R WHERE L.ity_id=R.id;
Copy the code

The demo results are as follows: 👇

Three, internal connection query 🌹

An inner join query displays only the data that meets the criteria. There are two types of inner join: implicit inner join and outer join

Inner join queries for left and right tablesIntersection part:A studying B, as shown in the following figure 👇

1️ implicit internal connection 🍄

It’s also mentioned aboveWHEREYou can filter redundant data by setting association conditions

/ * * / grammar
SELECTThe field nameFROMLeft table, right tableWHEREconditions/ * sample * /
-- Query all people and their identities
SELECT * FROM op,identity WHERE op.ity_id=identity.id;
Copy the code

2️ display internal connection 🥕

Using INNER JOIN… ON statement, INNER can be omitted

/ * * / grammar
SELECTThe field nameFROMThe left table [INNER] JOINTo the right tableONconditions/ * sample * /
-- Requirement: Query xiao Feng's fruit ability and identity
# 1. Determine which tables to query
SELECT * FROM op AS L INNER JOIN identity AS R;
# 2. Determine the join conditions for the table
SELECT * FROM op AS L INNER JOIN identity AS R ON L.ity_id=R.id;
# 3. Determine the query criteria
SELECT * FROM op AS L INNER JOIN identity AS R ON L.ity_id=R.id WHERE L.name='XiaoFeng';
# 4. Determine the query field
SELECT L.name,L.fruit,R.name FROM op AS L INNER JOIN identity AS R ON L.ity_id=R.id WHERE L.name='XiaoFeng';
# 5. The standard writing method is for reference only, and the line writing is convenient to add notes
SELECT 
	L.name ASName,-- Query field: character name
	L.fruit ASThe fruit,Query field: Character fruit ability
	R.name ASidentity-- Query field: Person identity
FROM 
	op AS L            Select * from 'op'
INNER JOIN 
	identity AS R      Select * from table where id = 'identity'
ON 
	L.ity_id=R.id      Select * from op where ity_id = id
WHERE 
	L.name='XiaoFeng';     -- Query condition: the character whose name is Xiao Feng
Copy the code

The demo results are as follows: 👇
Of course, if you use WHERE AND, you get the same result

SELECT 
	L.name ASName,-- Query field: character name
	L.fruit ASThe fruit,Query field: Character fruit ability
	R.name ASidentity-- Query field: Person identity
FROM 
	op AS L,           Select * from 'op'
	identity AS R      Select * from table where id = 'identity'
WHERE 
	L.ity_id=R.id      Select * from op where ity_id = id
AND 
	L.name='XiaoFeng';     -- Query condition: the character whose name is Xiao Feng
Copy the code

3️ summary of internal connection enquiry procedure 🧅

Determine which tables to query
Determine the table join condition
Determine the query criteria
Determine the query field

Four, external connection query 🍁

It is divided into left outer join and right outer join query

1️ cable 🌻

Returns all records in the left table and displays NULL in the corresponding field if there is no match in the right table.
Left external connection is usedLEFT OUTER JOIN ... ONOUTER can be omitted

/ * * / grammar
SELECTThe field nameFROMThe left tableLEFT [OUTER] JOINTo the right tableONconditions/ * sample * /
-- Use inner join query
SELECT * FROM op AS L INNER JOIN identity AS R ON L.ity_id=R.id;
-- Use the left outer join query
SELECT * FROM op AS L LEFT JOIN identity AS R ON L.ity_id=R.id;
Copy the code

The demo results are as follows: 👇
- Use inner join queries
- Use the left outer join to query

2️ right outer connection 🌼

Returns all records in the right table and displays NULL in the corresponding field if there is no match in the left table.
Right outer connection is usedRIGHT OUTER JOIN ... ONOUTER can be omitted

/ * * / grammar
SELECTThe field nameFROMThe left tableRIGHT [OUTER ]JOINTo the right tableONconditions/ * sample * /
-- Use inner join query
SELECT * FROM op AS L INNER JOIN identity AS R ON L.ity_id=R.id;
Use the right outer join to query
SELECT * FROM op AS L RIGHT JOIN identity AS R ON L.ity_id=R.id;
Copy the code

The demo results are as follows: 👇
- Use inner join queries
- Use the right outer join to query

3 summary of ️ external connection 🌸

Left outer connection and right outer connection is the same truth, the general use of left outer connection more
External join can be understood as: ensure that all data of the left table (or right table) is displayed on the basis of the inner join. If the right table (or left table) does not meet the conditions, null is displayed

5. Sub-query 🌳

Also called nested query, the result of one query can be used as the condition or table name of another query

Here are three cases of subqueries 👇

1️ one line and one row 🥄

The result of a subquery is a single row, single column, and the parent query uses comparison operators such as >, <, <>, =, and so on

/ * * / grammar
SELECTQuery fieldFROM 表 WHEREField = (subquery);/ * sample * /
# Demand: Query all information about the man with the highest reward
-- 1. Check the maximum reward in the op table
SELECT MAX(reward) FROM op;
-- 2. According to the highest reward to the OP table query the corresponding character information
SELECT * FROM op WHERE reward = (SELECT MAX(reward) FROM op);
Copy the code

The demo results are as follows: 👇

2️ multi-row and single row 🍴

The result of a child query is a collection of rows and columns used by the parent queryINThe operator

/ * * / grammar
SELECTQuery fieldFROM 表 WHEREfieldIN(subquery);/ * sample * /
# request: query who is a pirate and navy
-- Query the id of pirate and navy first
SELECT id FROM identity WHERE name in('pirates'.'navy');
Select * from user where id = ""
SELECT * FROM op WHERE ity_id in (SELECT id FROM identity WHERE name in('pirates'.'navy'));
Copy the code

The demo results are as follows: 👇

3️ multi-row and multi-row 🎹

Subquery results are multi-row, multi-column, inFROMBehind asVirtual table
Pay attention to: This virtual table needs to be fetchedThe aliasOtherwise, the table does not have a name and the fields in the table cannot be accessed

/ * * / grammar
SELECTQuery fieldFROM(Subquery)ASVirtual table aliasWHEREConditions;/ * sample * /
# request: query all information of characters whose bounty is less than the average value, including basic information and owning identity
-- 1. Find the basic information of the character whose bounty is less than average in the OP table
SELECT * FROM op WHERE reward<(SELECT AVG(reward) FROM op);
-- 2. Query idendity table information, combine it with virtual table information, and set filter criteria
SELECT * FROM (SELECT * FROM op WHERE reward<(SELECT AVG(reward) FROM op)) AS L,identity AS R WHERE L.ity_id=R.id;
Copy the code

The demo results are as follows: 👇

4️ subquery summary 🧊

As long as the subquery result isSingle rowWhile the,WHEREBehind asconditions
As long as the subquery result isMultiple columnsWhile the,FROMBehind asVirtual tablePerform a secondary query

Write it at the back 🍻

Thank you for watching ✨ have any deficiencies, welcome to point out oh 💖 nuggets operation students audit hard 💗

mo4tech.com (Moment For Technology) is a global community with thousands techies from across the global hang out!Passionate technologists, be it gadget freaks, tech enthusiasts, coders, technopreneurs, or CIOs, you would find them all here.

MySQL > select * from user where id = 1

Write at the front 👀

1. Data preparation ✨

Cartesian product phenomenon 🎃

1️ Cartesian product 🎭

2 How to remove the effect of cartesian product phenomenon 👨🔧

Three, internal connection query 🌹

1️ implicit internal connection 🍄

2️ display internal connection 🥕

3️ summary of internal connection enquiry procedure 🧅

Four, external connection query 🍁

1️ cable 🌻

2️ right outer connection 🌼

3 summary of ️ external connection 🌸

5. Sub-query 🌳

1️ one line and one row 🥄

2️ multi-row and single row 🍴

3️ multi-row and multi-row 🎹

4️ subquery summary 🧊

Write it at the back 🍻

MySQL > select * from user where id = 1

Write at the front 👀

1. Data preparation ✨

Cartesian product phenomenon 🎃

1️ Cartesian product 🎭

2 How to remove the effect of cartesian product phenomenon 👨🔧

Three, internal connection query 🌹

1️ implicit internal connection 🍄

2️ display internal connection 🥕

3️ summary of internal connection enquiry procedure 🧅

Four, external connection query 🍁

1️ cable 🌻

2️ right outer connection 🌼

3 summary of ️ external connection 🌸

5. Sub-query 🌳

1️ one line and one row 🥄

2️ multi-row and single row 🍴

3️ multi-row and multi-row 🎹

4️ subquery summary 🧊

Write it at the back 🍻

Related Posts

JavaWeb – what is a Session and how to use it

MyBatis SpringBoot integration – Plus

Accessing Github too slowly? I wrote an open source gadget for one-click faster