What are the internal and external queries of mysql?
Mysql's inner query uses conditional expressions to eliminate certain data rows in cross-connections by setting connection conditions. The query results are all records that meet the connection conditions; while the outer query will first connect the The table is divided into a base table and a reference table, and records that meet or do not meet the conditions are returned based on the base table.
(Recommended tutorial: mysql video tutorial)
MySQL INNER JOIN: inner join Query
Inner JOIN mainly removes cross-connections of certain data rows in the query results by setting connection conditions. To put it simply, conditional expressions are used to eliminate certain data rows in cross-connections.
Inner join uses the INNER JOIN keyword to connect two tables, and uses the ON clause to set the connection conditions. Without join conditions, INNER JOIN and CROSS JOIN are syntactically equivalent and interchangeable.
The syntax format of inner join is as follows:
SELECT <字段名> FROM <表1> INNER JOIN <表2> [ON子句]
The syntax description is as follows:
Field name : The name of the field to be queried.
: The name of the table that requires inner join.
INNER JOIN: The INNER keyword can be omitted in inner joins, and only the JOIN keyword is used.
ON clause: used to set the connection conditions of the inner join.
INNER JOIN You can also use the WHERE clause to specify connection conditions, but the INNER JOIN ... ON syntax is the official standard writing method, and the WHERE clause will affect the query at some point. performance.
When connecting multiple tables, just use INNER JOIN or JOIN continuously after FROM.
Inner joins can query two or more tables. In order to give everyone a better understanding, we will only explain the connection query between two tables for the time being.
Example
Between the tb_students_info table and the tb_course table, use inner joins to query student names and corresponding course names. The SQL statement and running results are as follows.
mysql> SELECT s.name,c.course_name FROM tb_students_info s INNER JOIN tb_course c -> ON s.course_id = c.id; +--------+-------------+ | name | course_name | +--------+-------------+ | Dany | Java | | Green | MySQL | | Henry | Java | | Jane | Python | | Jim | MySQL | | John | Go | | Lily | Go | | Susan | C++ | | Thomas | C++ | | Tom | C++ | +--------+-------------+ 10 rows in set (0.00 sec)Copy after loginIn the query statement here, the relationship between the two tables is specified through INNER JOIN, and the conditions for the connection are given using the ON clause.
Note: When querying multiple tables, you must specify which table the fields come from after the SELECT statement. Therefore, when querying multiple tables, the writing method after the SELECT statement is table name.column name. In addition, if the table name is very long, you can also set an alias for the table, so that you can write the table's alias and column name directly after the SELECT statement.
MySQL LEFT/RIGHT JOIN: Outer join query
The query results of the inner join are all records that meet the connection conditions, and the outer join will First, the connected table is divided into a base table and a reference table, and then the records that meet and do not meet the conditions are returned based on the base table.
Outer joins can be divided into left outer joins and right outer joins. The following describes left outer joins and right outer joins respectively based on examples.
Left join
Left outer join, also known as left join, uses the LEFT OUTER JOIN keyword to connect two tables, and uses the ON child Sentence to set the connection conditions.
The syntax format of left join is as follows:
SELECT <字段名> FROM <表1> LEFT OUTER JOIN <表2> <ON子句>
Copy after loginThe syntax description is as follows.
Field name: The name of the field to be queried.
: The name of the table that requires a left join.
LEFT OUTER JOIN: The OUTER keyword can be omitted in the left join, and only the keyword LEFT JOIN is used.
ON clause: used to set the connection condition of the left join and cannot be omitted.
In the above syntax, "Table 1" is the base table and "Table 2" is the reference table. When querying with a left join, you can query all the records in "Table 1" and the records matching the join conditions in "Table 2". If a row in "Table 1" does not have a matching row in "Table 2", then in the returned result, the field values of "Table 2" will be null (NULL).
Example 1
Before performing the left join query, we first check the data in the tb_course and tb_students_info tables. The SQL statements and running results are as follows.
mysql> SELECT * FROM tb_course; +----+-------------+ | id | course_name | +----+-------------+ | 1 | Java | | 2 | MySQL | | 3 | Python | | 4 | Go | | 5 | C++ | | 6 | HTML | +----+-------------+ 6 rows in set (0.00 sec) mysql> SELECT * FROM tb_students_info; +----+--------+------+------+--------+-----------+ | id | name | age | sex | height | course_id | +----+--------+------+------+--------+-----------+ | 1 | Dany | 25 | 男 | 160 | 1 | | 2 | Green | 23 | 男 | 158 | 2 | | 3 | Henry | 23 | 女 | 185 | 1 | | 4 | Jane | 22 | 男 | 162 | 3 | | 5 | Jim | 24 | 女 | 175 | 2 | | 6 | John | 21 | 女 | 172 | 4 | | 7 | Lily | 22 | 男 | 165 | 4 | | 8 | Susan | 23 | 男 | 170 | 5 | | 9 | Thomas | 22 | 女 | 178 | 5 | | 10 | Tom | 23 | 女 | 165 | 5 | | 11 | LiMing | 22 | 男 | 180 | 7 | +----+--------+------+------+--------+-----------+ 11 rows in set (0.00 sec)
Copy after loginQuery all student names and corresponding course names in the tb_students_info table and tb_course table, including students without courses, the SQL statement and running results are as follows.
mysql> SELECT s.name,c.course_name FROM tb_students_info s LEFT OUTER JOIN tb_course c -> ON s.`course_id`=c.`id`; +--------+-------------+ | name | course_name | +--------+-------------+ | Dany | Java | | Henry | Java | | NULL | Java | | Green | MySQL | | Jim | MySQL | | Jane | Python | | John | Go | | Lily | Go | | Susan | C++ | | Thomas | C++ | | Tom | C++ | | LiMing | NULL | +--------+-------------+ 12 rows in set (0.00 sec)Copy after loginAs you can see, the running result shows 12 records. The student named LiMing currently has no courses. Because there is no course information for the student in the corresponding tb_course table, this record is only taken out of the tb_students_info table. The corresponding value in the tb_course table is NULL.
Right join
Right outer join is also called right join, and right join is the reverse join of left join. Use the RIGHT OUTER JOIN keyword to join two tables, and use the ON clause to set the join conditions.
The syntax format of right join is as follows:
SELECT <字段名> FROM <表1> RIGHT OUTER JOIN <表2> <ON子句>
Copy after loginThe syntax description is as follows.
Field name: The name of the field to be queried.
<表1><表2>：需要右连接的表名。
RIGHT OUTER JOIN：右连接中可以省略 OUTER 关键字，只使用关键字 RIGHT JOIN。
ON 子句：用来设置右连接的连接条件，不能省略。
与左连接相反，右连接以“表2”为基表，“表1”为参考表。右连接查询时，可以查询出“表2”中的所有记录和“表1”中匹配连接条件的记录。如果“表2”的某行在“表1”中没有匹配行，那么在返回结果中，“表1”的字段值均为空值（NULL）。
例 2
在 tb_students_info 表和 tb_course 表中查询所有课程，包括没有学生的课程，SQL 语句和运行结果如下。
mysql> SELECT s.name,c.course_name FROM tb_students_info s RIGHT OUTER JOIN tb_course c -> ON s.`course_id`=c.`id`; +--------+-------------+ | name | course_name | +--------+-------------+ | Dany | Java | | Green | MySQL | | Henry | Java | | Jane | Python | | Jim | MySQL | | John | Go | | Lily | Go | | Susan | C++ | | Thomas | C++ | | Tom | C++ | | NULL | HTML | +--------+-------------+ 11 rows in set (0.00 sec)Copy after login可以看到，结果显示了 11 条记录，名称为 HTML 的课程目前没有学生，因为对应的 tb_students_info 表中并没有该学生的信息，所以该条记录只取出了 tb_course 表中相应的值，而从 tb_students_info 表中取出的值为 NULL。
多个表左/右连接时，在 ON 子句后连续使用 LEFT/RIGHT OUTER JOIN 或 LEFT/RIGHT JOIN 即可。
使用外连接查询时，一定要分清需要查询的结果，是需要显示左表的全部记录还是右表的全部记录，然后选择相应的左连接和右连接。
相关推荐：php培训
The above is the detailed content of What are the internal and external queries of mysql?. For more information, please follow other related articles on the PHP Chinese website!
Statement of this WebsiteThe content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
How to fix KB5055523 fails to install in Windows 11?3 weeks ago By DDDHow to fix KB5055518 fails to install in Windows 10?3 weeks ago By DDDRoblox: Dead Rails - How To Tame Wolves1 months ago By DDDStrength Levels for Every Enemy & Monster in R.E.P.O.4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌Roblox: Grow A Garden - Complete Mutation Guide2 weeks ago By DDD
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
Java Tutorial
1662
14
CakePHP Tutorial
1418
52
Laravel Tutorial
1311
25
PHP Tutorial
1261
29
C# Tutorial
1234
24
See all articles
MySQL's Role: Databases in Web Applications
Apr 17, 2025 am 12:23 AM
The main role of MySQL in web applications is to store and manage data. 1.MySQL efficiently processes user information, product catalogs, transaction records and other data. 2. Through SQL query, developers can extract information from the database to generate dynamic content. 3.MySQL works based on the client-server model to ensure acceptable query speed.
Laravel Introduction Example
Apr 18, 2025 pm 12:45 PM
Laravel is a PHP framework for easy building of web applications. It provides a range of powerful features including: Installation: Install the Laravel CLI globally with Composer and create applications in the project directory. Routing: Define the relationship between the URL and the handler in routes/web.php. View: Create a view in resources/views to render the application's interface. Database Integration: Provides out-of-the-box integration with databases such as MySQL and uses migration to create and modify tables. Model and Controller: The model represents the database entity and the controller processes HTTP requests.
How to start mysql by docker
Apr 15, 2025 pm 12:09 PM
The process of starting MySQL in Docker consists of the following steps: Pull the MySQL image to create and start the container, set the root user password, and map the port verification connection Create the database and the user grants all permissions to the database
MySQL and phpMyAdmin: Core Features and Functions
Apr 22, 2025 am 12:12 AM
MySQL and phpMyAdmin are powerful database management tools. 1) MySQL is used to create databases and tables, and to execute DML and SQL queries. 2) phpMyAdmin provides an intuitive interface for database management, table structure management, data operations and user permission management.
Solve database connection problem: a practical case of using minii/db library
Apr 18, 2025 am 07:09 AM
I encountered a tricky problem when developing a small application: the need to quickly integrate a lightweight database operation library. After trying multiple libraries, I found that they either have too much functionality or are not very compatible. Eventually, I found minii/db, a simplified version based on Yii2 that solved my problem perfectly.
MySQL vs. Other Programming Languages: A Comparison
Apr 19, 2025 am 12:22 AM
Compared with other programming languages, MySQL is mainly used to store and manage data, while other languages such as Python, Java, and C are used for logical processing and application development. MySQL is known for its high performance, scalability and cross-platform support, suitable for data management needs, while other languages have advantages in their respective fields such as data analytics, enterprise applications, and system programming.
Laravel framework installation method
Apr 18, 2025 pm 12:54 PM
Article summary: This article provides detailed step-by-step instructions to guide readers on how to easily install the Laravel framework. Laravel is a powerful PHP framework that speeds up the development process of web applications. This tutorial covers the installation process from system requirements to configuring databases and setting up routing. By following these steps, readers can quickly and efficiently lay a solid foundation for their Laravel project.
MySQL for Beginners: Getting Started with Database Management
Apr 18, 2025 am 12:10 AM
The basic operations of MySQL include creating databases, tables, and using SQL to perform CRUD operations on data. 1. Create a database: CREATEDATABASEmy_first_db; 2. Create a table: CREATETABLEbooks(idINTAUTO_INCREMENTPRIMARYKEY, titleVARCHAR(100)NOTNULL, authorVARCHAR(100)NOTNULL, published_yearINT); 3. Insert data: INSERTINTObooks(title, author, published_year)VA
