What is the difference between MySQL subquery and join 01/31 Update SLTechnology News&Howtos

What is the difference between MySQL subquery and join

2025-01-31 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Shulou(Shulou.com)06/01 Report--

The following mainly brings you the difference between MySQL subquery and join. I hope these contents can bring you practical use, which is also the main purpose of this article. All right, don't talk too much nonsense, let's just read the following.

Make the client enter the gbk encoding mode to display:

Mysql > SET NAMES gbk

1. Subquery

Definition of subquery:

A Subquery is a SELECT clause that appears in other SQL statements.

For example:

SELECT * FROM T1 WHERE col1 = (SELECT col2 FROM T2)

Where SELECT * FROM T1 is called Outer Query / Outer Statement (external query)

SELECT col2 FROM T2, called SubQuery (subquery)

Conditions of the subquery:

A subquery is nested inside a query and must always appear in parentheses.

A subquery can contain multiple keywords or conditions

Such as: DISTINCT,GROUP BY,ORDER BY,LIMIT function and so on.

The external query of a subquery can be: SELECT, INSERT, UPDATE, SET or DO

Return value of subquery:

Subqueries can return scalars, rows, columns, or subqueries

two。 Subqueries using comparison operators

Subqueries that use the comparison operator:

=, >, =, SELECT ROUND (avg (goods_price), 2) AS avg_price FROM tdb_goods;+-+ | avg_price | +-+ | 5391.30 | + +

Query the goods above the average price:

Mysql > SELECT * FROM tdb_goods WHERE goods_price > 5391.30 * MySQL > SELECT * FROM tdb_goods WHERE goods_price > (SELECT ROUND (AVG (goods_price), 2) FROM tdb_goods)

Query the list of ultrabooks in ascending order of price:

Mysql > SELECT * FROM tdb_goods WHERE goods_cate = 'ultrabook' ORDER BY goods_price ASC

Query the list that is higher than the lowest price of ultrabooks, sorted in descending order of price:

Mysql > SELECT * FROM tdb_goods WHERE goods_price > ANY (SELECT goods_price FROM tdb_goods WHERE goods_cate = 'ultrabook') ORDER BY goods_price DESC

Query the list that is higher than the highest price of the ultrabook, in descending order of price:

Mysql > SELECT * FROM tdb_goods WHERE goods_price > ALL (SELECT goods_price FROM tdb_goods WHERE goods_cate = 'ultrabook') ORDER BY goods_price DESC

3. Subqueries raised using [NOT] IN/EXISTS

The form of subquery:

1. Subqueries that use IN

two。 Subqueries using [NOT] IN

Syntax structure: operand comparison_operator [NOT] IN (subquery)

The = ANY operator is equivalent to IN, and the! = ALL or ALL operator is equivalent to NOT IN

3. Subqueries that use [NOT] EXISTS (relatively few)

If the subquery returns any rows, EXISTS will return TRUE, otherwise FALSE

For example, find a list of goods that are not ultrabooks:

Mysql > SELECT * FROM tdb_goods WHERE goods_cate NOT IN (SELECT goods_cate FROM tdb_goods WHERE goods_cate = 'ultrabook')

4. Insert a record using INSERT...SELECT

Example: insert the goods_cate classification from the tdb_ goods table in the tdb_goods_ cates table

Mysql > INSERT tdb_goods_cates (cate_name) SELECT goods_cate FROM tdb_goods GROUP BY goods_cate;mysql > SELECT * FROM tdb_goods_cates +-+-+ | cate_id | cate_name | +-+-+ | 1 | Desktop | | 2 | tablet | | 3 | Server / worker Make Station | | 4 | Game Book | | 5 | Notebook | | 6 | Notebook Accessories | | 7 | Ultrabook | +-+-+

5. Multi-table update

Multi-table update:

UPDATE table_references SET col_name1= {expr1 | DEFAULT} [, col_name2= {expr2 | DEFAULT}]... [WHERE where_condition]

Where table_references is the grammatical structure of the connection

Connection type:

INNER JOIN, internal connection

In MySQL, JOIN,CROSS JOIN and INNER JOIN are equivalent

LEFT [OUTER] JOIN, left outer connection

RIGHT [OUTER] JOIN, right outside connection

Connection-grammatical structure

Table_reference {[INNER | CROSS] JOIN | {LEFT | RIGHT} [OUTER] JOIN} table_referenceON conditional_expr

For example, update the goods_cate in tdb_goods to the corresponding cate_id in the tdb_goods_ Cate table.

Mysql > UPDATE tdb_goods INNER JOIN tdb_goods_cates ON goods_cate = cate_name SET goods_cate = cate_id

6. One of the steps of multi-table update

CREATE...SELECT

Create a data table and write the query results to the data table (merging CREATE and INSERT...SELECT)

CREATE TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)] select_statement

Example: create the brand classification data table tdb_goods_brand, and write the brand_name in the tdb_goods table

Mysql > CREATE TABLE tdb_goods_brand (- > brand_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,-> brand_name VARCHAR (40) NOT NULL->)-> SELECT brand_name FROM tdb_goods GROUP BY brand_name

For example, update the brand_name in tdb_goods to the corresponding brand_id in the tdb_goods_ brand table. Incorrect writing (the program can't tell which brand_name belongs to which table):

Mysql > UPDATE tdb_goods INNER JOIN tdb_goods_brand ON brand_name = brand_name SET brand_name = brand_id;ERROR 1052 (23000): Column 'brand_name' in field list is ambiguous

The solution is to alias the table:

Mysql > UPDATE tdb_goods AS an INNER JOIN tdb_goods_brand AS b ON a.brand_name = b.brand_name SET a.brand_name = b.brand_id

Query the data table structure of tdb_goods

Goods_cate and brand_name are still varchar. Now we change the field name from goods_cate to cate_id,brand_name to brand_id. To save space, we change the data type to smallant.

7. The grammatical structure of a connection

Joins support JOIN operations in MySQL SELECT statements, multi-table updates, and multi-table delete statements.

Table_reference {[INNER | CROSS] JOIN | {LEFT | RIGHT} [OUTER] JOIN} table_referenceON conditional_expr

Data table reference

Table_reference

Tbl_name [[AS] alias] | table_subquery [AS] alias

Datasheets can use tbl_name AS alias_name

Or alias given by tbl_name alias_name

Table_subquery can be used as a subquery in the From clause

Such a subquery must be given an alias.

8. Internal connection INNER JOIN

INNER JOIN, internal connection

In MySQL, JOIN,CROSS JOIN and INNER JOIN are equivalent

LEFT [OUTER] JOIN, left outer connection

RIGHT [OUTER] JOIN, right outside connection

Connection conditions:

Use the ON keyword to set connection conditions, or you can use WHERE instead

The ON keyword is usually used to set the connection condition

Filtering result set records using the WHERE keyword

The difference between inner connection and outer connection

Inner join, showing the records of the left table and the right table that meet the join conditions, that is, intersection

For example, insert several records

-insert records in tdb_goods_cates and tdb_goods_brands tables, respectively

INSERT tdb_goods_cates (cate_name) VALUES ('router'), ('switch'), ('network card'); INSERT tdb_goods_brands (brand_name) VALUES ('Haier'), ('Tsinghua Tongfang'), ('Shenzhou')

-- write any notes in the tdb_goods data table

INSERT tdb_goods (goods_name,cate_id,brand_id,goods_price) VALUES ('LaserJet Pro P1606dn Black-and-White Laser Printer','12','4', '1849')

For example, check the records that match the internal join. Only the records of both the commodity table and the brand table appear in the table. This is the internal join (here the commodity table is the left table, and the classification table is the right table):

Mysql > SELECT goods_id,goods_name,cate_name,goods_price FROM tdb_goods- > INNER JOIN tdb_goods_cates ON tdb_goods.cate_id = tdb_goods_cates.cate_id

9. External connection OUTER JOIN

Left outer connection

Displays all the records of the left table and the records of the right table that meet the connection conditions

For example, if you look at the records that match the left outer join, all of the commodity tables and 2 tables have records in the table. This is the left outer join (here the commodity table is the left table, and the classification table is the right table).

Mysql > SELECT goods_id,cate_name,goods_price FROM tdb_goods LEFT JOIN tdb_goods_cates ON tdb_goods.cate_id = tdb_goods_cates.cate_id

Right outer connection

Displays all the records of the right table and the records of the left table that meet the connection conditions

For example, check the records that match the right outer connection. All of the brand tables and 2 tables have records in the table. This is the right outer connection (here the goods table is the left table, and the classification table is the right table).

Mysql > SELECT goods_id,goods_name,cate_name,goods_price FROM tdb_goods RIGHT JOIN tdb_goods_cates ON tdb_goods.cate_id = tdb_goods_cates.cate_id

10. Multi-table connection

/ * *

The connection of multiple tables is the same as the connection of two tables.

The join of a table is essentially the reverse constraint of a foreign key.

, /

For example, if you look at the records that match the internal connection, only the records of both the product table and the brand table appear in the table. This is the multi-table join of the inner join (here the commodity table is the left table, and the brand table and classification table are the right table):

Mysql > SELECT goods_id,goods_name,cate_name,brand_name,goods_price FROM tdb_goods-> INNER JOIN tdb_goods_cates ON tdb_goods.cate_id = tdb_goods_cates.cate_id-> INNER JOIN tdb_goods_brand ON tdb_goods.brand_id = tdb_goods_brand.brand_id

11. Design of infinite level classification table

Self-connection

For example, find all categories and their parent classes:

For example, find all categories and their subcategories:

Example to find the number of all categories and their subclasses

Mysql > SELECT p.typewritten FROM tdb_goods_types AS p LEFT JOIN tdb_goods_types AS s ON s.parent_id. Typewritten name FROM tdb_goods_types AS p LEFT JOIN tdb_goods_types AS s ON s.parent_id = p.type_id GROUP BY p.type_name ORDER BY p.type_id +-+ | type_id | type_name | COUNT (s.type_name) | +-+ | 1 | Home Appliances | 2 | | 2 | computer, Office | 2 | | 3 | Home Appliances | 2 | | 4 | Household Appliances | 2 | | 5 | flat screen TV | 0 | | 6 | Air conditioning | 0 | | 7 | Fan | | 0 | | 8 | Water dispenser | 0 | | 9 | computer | 3 | | 10 | computer Accessories | 2 | | 11 | Notebook | 0 | 12 | Ultrabook | | | 0 | | 13 | Game book | 0 | | 14 | CPU | 0 | | 15 | Host | 0 | +-+ |

Add a child_count field for tdb_goods_types

Mysql > UPDATE tdb_goods_types AS T1 INNER JOIN-> (SELECT p. Typewriter T1 INNER JOIN p-> LEFT JOIN tdb_goods_types AS s ON s.parent_id = p.type_id-> GROUP BY p.type_name-> ORDER BY p.type_id) AS T2-> ON t1.type_id = t2.type_id-> SET t1.child_count = t2.child_count Mysql > SELECT * FROM tdb_goods_types +-+ | type_id | type_name | parent_id | child_count | +- -+ | 1 | Home Appliances | 0 | 2 | | 2 | computer, Office | 0 | 2 | | 3 | Home Appliances | 1 | 2 | | 4 | Household Appliances | 1 | 2 | | 5 | flat screen TV | 3 | 0 | 6 | Air conditioning | 3 | 0 | | 7 | Electric fan | 4 | 0 | | 8 | Water dispenser | 4 | 0 | | 9 | computer | 2 | 3 | | 10 | computer Accessories | 2 | 2 | | 11 | Notes | Book | 9 | 0 | 12 | Super book | 9 | 0 | 13 | Game book | 9 | 0 | 14 | CPU | 10 | 0 | 15 | Host | 10 | 0 | | +-+

11. Multiple table deletion

DELETE tbl_name [. *] [, tbl_name [. *]]... FROM table_ references [where where_condition]

For example, find duplicate records:

Mysql > SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name having count (goods_name) > = 2 +-+-+ | goods_id | goods_name | +-+-+ | 18 | HMZ-T3W headwear display setting Standby | | 19 | Business backpack | +-+-+

Delete duplicate record

Mysql > DELETE T1 FROM tdb_goods AS T1-> LEFT JOIN (SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name having count (goods_name) > = 2) AS T2-> ON t1.goods_name = t2.goods_name-> WHERE t1.goods_id > t2.goods_id

twelve。 Copy record

Copy the two records numbered 19520.

Mysql > SELECT goods_id,goods_name,cate_id,brand_id FROM tdb_goods WHERE goods_id IN (195.20) +-+ | goods_id | goods_name | cate_id | brand_id | +-+- -+ | 19 | Business backpack | 6 | 7 | | 20 | X3250 M4 rack server 2583i14 | 3 | 1 | +-- -- + mysql > INSERT tdb_goods (goods_name Cate_id,brand_id) SELECT goods_name,cate_id,brand_id FROM tdb_goods WHERE goods_id IN (195.20)

For the above about MySQL subquery and join what is the difference, we do not think it is very helpful. If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.