What are the connection queries for mysql tables?
The connection queries for mysql tables include: 1. Cross connection, which is generally used to return the Cartesian product of the connected table; 2. Inner join, which mainly removes certain parts of the query results by setting connection conditions. Cross-connection of data rows; 3. Outer connection, first divide the connected table into a base table and a reference table, and then return records that meet and do not meet the conditions based on the base table.
(Recommended tutorial: mysql video tutorial)
In a relational database, the relationship between tables is There is a relationship, so in practical applications, multi-table queries are often used. Multi-table query is to query two or more tables at the same time.
In MySQL, multi-table queries mainly include cross joins, inner joins and outer joins.
Cross join
Cross join (CROSS JOIN) is generally used to return the Cartesian product of the join table.
The syntax format of cross-connection is as follows:
SELECT <字段名> FROM <表1> CROSS JOIN <表2> [WHERE子句]
or
SELECT <字段名> FROM <表1>, <表2> [WHERE子句]
The syntax description is as follows:
Field name: what needs to be queried Field Name.
: The name of the table that requires cross-connection.
WHERE clause: used to set the query conditions for cross connections.
Note: When multiple tables are cross-connected, CROSS JOIN or , can be used continuously after FROM. The return results of the above two syntaxes are the same, but the first syntax is the officially recommended standard writing method.
When there is no relationship between the connected tables, we will omit the WHERE clause. At this time, the returned result is the Cartesian product of the two tables, and the number of returned results is the multiplication of the data rows of the two tables. It should be noted that if each table has 1000 rows, then the number of returned results will be 1000×1000 = 1000000 rows, and the amount of data is very huge.
Inner JOIN
Inner JOIN mainly removes certain data rows from the query results by setting connection conditions. of cross-connection. 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 connection is as follows:
SELECT <字段名> FROM <表1> INNER 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 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.
Outer join
The query results of the inner join are all records that meet the connection conditions, and the outer join will first divide the connected table into Base table and reference table, and then return records that meet and do not meet the conditions 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 clause 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 OUTER JOIN 关键字连接两个表，并使用 ON 子句来设置连接条件。
右连接的语法格式如下：
SELECT <字段名> FROM <表1> RIGHT OUTER JOIN <表2> <ON子句>
Copy after login语法说明如下。
字段名：需要查询的字段名称。
<表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 即可。
使用外连接查询时，一定要分清需要查询的结果，是需要显示左表的全部记录还是右表的全部记录，然后选择相应的左连接和右连接。
The above is the detailed content of What are the connection queries for mysql tables?. 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
AI Hentai Generator
Generate AI Hentai for free.
Hot Article
R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)3 weeks ago By 尊渡假赌尊渡假赌尊渡假赌R.E.P.O. Best Graphic Settings3 weeks ago By 尊渡假赌尊渡假赌尊渡假赌Assassin's Creed Shadows: Seashell Riddle Solution2 weeks ago By DDDR.E.P.O. How to Fix Audio if You Can't Hear Anyone3 weeks ago By 尊渡假赌尊渡假赌尊渡假赌WWE 2K25: How To Unlock Everything In MyRise4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
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
CakePHP Tutorial
1377
52
See all articles
MySQL: The Ease of Data Management for Beginners
Apr 09, 2025 am 12:07 AM
MySQL is suitable for beginners because it is simple to install, powerful and easy to manage data. 1. Simple installation and configuration, suitable for a variety of operating systems. 2. Support basic operations such as creating databases and tables, inserting, querying, updating and deleting data. 3. Provide advanced functions such as JOIN operations and subqueries. 4. Performance can be improved through indexing, query optimization and table partitioning. 5. Support backup, recovery and security measures to ensure data security and consistency.
Can I retrieve the database password in Navicat?
Apr 08, 2025 pm 09:51 PM
Navicat itself does not store the database password, and can only retrieve the encrypted password. Solution: 1. Check the password manager; 2. Check Navicat's "Remember Password" function; 3. Reset the database password; 4. Contact the database administrator.
How to create navicat premium
Apr 09, 2025 am 07:09 AM
Create a database using Navicat Premium: Connect to the database server and enter the connection parameters. Right-click on the server and select Create Database. Enter the name of the new database and the specified character set and collation. Connect to the new database and create the table in the Object Browser. Right-click on the table and select Insert Data to insert the data.
MySQL: Simple Concepts for Easy Learning
Apr 10, 2025 am 09:29 AM
MySQL is an open source relational database management system. 1) Create database and tables: Use the CREATEDATABASE and CREATETABLE commands. 2) Basic operations: INSERT, UPDATE, DELETE and SELECT. 3) Advanced operations: JOIN, subquery and transaction processing. 4) Debugging skills: Check syntax, data type and permissions. 5) Optimization suggestions: Use indexes, avoid SELECT* and use transactions.
How to view database password in Navicat for MariaDB?
Apr 08, 2025 pm 09:18 PM
Navicat for MariaDB cannot view the database password directly because the password is stored in encrypted form. To ensure the database security, there are three ways to reset your password: reset your password through Navicat and set a complex password. View the configuration file (not recommended, high risk). Use system command line tools (not recommended, you need to be proficient in command line tools).
How to execute sql in navicat
Apr 08, 2025 pm 11:42 PM
Steps to perform SQL in Navicat: Connect to the database. Create a SQL Editor window. Write SQL queries or scripts. Click the Run button to execute a query or script. View the results (if the query is executed).
How to create a new connection to mysql in navicat
Apr 09, 2025 am 07:21 AM
You can create a new MySQL connection in Navicat by following the steps: Open the application and select New Connection (Ctrl N). Select "MySQL" as the connection type. Enter the hostname/IP address, port, username, and password. (Optional) Configure advanced options. Save the connection and enter the connection name.
MySQL and SQL: Essential Skills for Developers
Apr 10, 2025 am 09:30 AM
MySQL and SQL are essential skills for developers. 1.MySQL is an open source relational database management system, and SQL is the standard language used to manage and operate databases. 2.MySQL supports multiple storage engines through efficient data storage and retrieval functions, and SQL completes complex data operations through simple statements. 3. Examples of usage include basic queries and advanced queries, such as filtering and sorting by condition. 4. Common errors include syntax errors and performance issues, which can be optimized by checking SQL statements and using EXPLAIN commands. 5. Performance optimization techniques include using indexes, avoiding full table scanning, optimizing JOIN operations and improving code readability.
