MYSQL database-connection query

Join query

Joining is the most common way to join records from different tables. A misconception is that MySQL is not good at connecting because of its simplicity and open source code. This concept is wrong. MySQL supports joins well from the start and now boasts support for standard SQL2 join statements that combine table records in a variety of advanced ways.

Internal join query

Inner joins are the most common type of join and the most symmetrical, because they require a match for each table that makes up each part of the join, and mismatched rows are excluded.

Example:

SELECT * FROM user_fuelcard WHERE user_id = (SELECT row_id

FROM md_user WHERE phone = '13782843517')

Query results:

External join query

Unlike inner joins, outer joins refer to joining two tables together using the OUTER JOIN keyword. The result set generated by an outer join contains not only rows of data that meet the join criteria, but also all rows of data in the left table (the table when the left outer join), the right table (the table when the right outer join), or the two-sided join table (the table when all outer joins).

Example:

SELECT phone phone number, ua.balance account balance FROM md_user mu

JOIN user_account ua ON ua.user_id = mu.row_id WHERE mu.row_id IN (

SELECT user_id FROM user_account WHERE ua.balance > = 100)

Query results:

Compound conditional join query

When you join a query, you can also add other restrictions. Through the compound query of multiple conditions, the query results can be made more accurate.

Example:

SELECT * FROM user_account ua, md_user mu

WHERE ua.user_id = mu.row_id AND ua.balance > = 1000000

Query results: