Detailed explanation of MySQL subqueries (nested queries), join tables, and combined queries

1. Subquery

MySQL 4.1 and above support subqueries

Subquery: A query that is nested within another query.

The role of subquery:

1. Filter:

Example 1: Retrieve the IDs of all customers who ordered item TNT2

= +

Generally, there is no limit to the number of subqueries that can be nested in the WHERE clause. However, in actual use, due to performance limitations, too many subqueries cannot be nested.
Note: Columns must match - using a subquery in a WHERE clause (as shown here) should ensure that the SELECT statement has the same number of columns as in the WHERE clause. Typically, the subquery will return and match on a single column, but multiple columns can be used if necessary.

Example 2: Return a list of customers who ordered product TNT2

A more efficient way to query this example is to use a join:

Note: The details about the connection will be summarized below.

2. Create a calculated field:

Correlated subquery: A subquery that involves an outer query. This syntax must be used when the column name could be ambiguous.
Example: Display the total number of orders for each customer in the customers table

Summarize:
The most common use of subqueries is in the IN operator of a WHERE clause and to populate computed columns. The most reliable way to build (and test) subqueries is to do it incrementally, in much the same way that MySQL handles them. First, build and test the innermost query. Then, build and test the outer query with hard-coded data, and embed the subquery only after you've verified that it works. At this time, test it again. Repeat these steps for each query you want to add. Doing this adds only a tiny bit of time to constructing the query, but saves a lot of time later (figuring out why the query didn't work properly) and greatly increases the likelihood that the query will work properly in the first place.

2. Join Table

Join tables are one of the most powerful features of SQL

1. Some relevant basic knowledge reserves:

Relational table: ensures that information is broken down into multiple tables, one table for each type of data. Tables are related to each other through some common values ​​(relational in relational design). It saves time and storage space, and facilitates the modification and update of data. Therefore, the scalability of relational databases is much better than that of non-relational databases.
Scalability: The ability to adapt to increasing workloads without failing. A well-designed database or application is said to be scalable.
Join: Join is a mechanism used to associate tables in a SELECT statement. It can join multiple tables to return a set of output.

A join is not a physical entity—it does not exist in the actual database table. The join is established by MySQL as needed and exists for the duration of the query execution.
When working with relational tables, it is very important to insert only valid data into relational columns. To prevent this from happening, referential integrity needs to be maintained, which is achieved by specifying primary and foreign keys in the table definition.

2. Basic connection:

Example 1:

The two tables are correctly joined using the WHERE clause: The WHERE clause instructs MySQL to match the vend_id in the vendors table with the vend_id in the products table. Note: When referencing columns that could be ambiguous, you must use fully qualified column names (table and column names separated by a dot).
When you join several tables in a SELECT statement, the corresponding relationship is constructed on the fly. There is nothing in the definition of the database table that can instruct MySQL how to join the tables. When you join two tables, you are actually pairing each row in the first table with each row in the second table. The WHERE clause acts as a filter condition, which includes only those rows that match the given condition (here, the join condition). Without a WHERE clause, every row in the first table will be paired with every row in the second table, regardless of whether they logically go together.

Cartesian product: The result returned by a table relationship without a join condition. The number of rows retrieved will be the number of rows in the first table multiplied by the number of rows in the second table. Sometimes also called a fork junction.

Example 2: Display the items in order number 20005

You should ensure that all joins have a WHERE clause, otherwise MySQL will return much more data than you want.
MySQL processes the join by joining each of the specified tables at run time. This processing can be very resource intensive, so care should be taken not to join unnecessary tables. The more tables that are joined, the more performance will degrade.

Equi-join: Based on an equality test between two tables, also known as an inner join. (The most commonly used connection method)

Examples:

The ANSI SQL specification prefers the INNER JOIN syntax. Furthermore, although it is indeed simpler to define joins using the WHERE clause, using explicit join syntax ensures that you do not forget the join condition, which can sometimes affect performance.

3. Advanced Connection:

Example 1: Give a table an alias (same as giving a column an ​​alias)

Note: Table aliases are only used during query execution. Unlike column aliases, table aliases are not returned to the client.

One of the main reasons for using table aliases is to be able to reference the same table more than once in a single SELECT statement Example 2: Query other items produced by the supplier of the item with production ID DTNTR

The above solution is a self-join, which is usually used as an outer statement to replace the subquery statement used when retrieving data from the same table. This example can also be solved using a subquery. Although the end result is the same, sometimes a join can be processed much faster than a subquery. When solving a problem, you can try two approaches to determine which performs better.

Natural join: Eliminate multiple occurrences so that each column is returned only once. Generally, the internal connections we use are natural connections.

Example 3: Natural Connection

Natural joins are typically done by using a wildcard (SELECT *) for one table and an explicit subset of the columns of all other tables.

Outer join: A join includes rows that have no associated rows in the related table.

Example 4: Retrieve all customers, including those without orders

The usage is similar to the inner join, and the keyword OUTER JOIN is used to specify the type of join. However, unlike an inner join, which relates rows from two tables, an outer join also includes rows that have no related rows.

There are two basic forms of outer joins: left outer join and right outer join. When using the OUTER JOIN syntax, you must use the RIGHT or LEFT keyword to specify the table that includes all its rows (RIGHT refers to the table on the right side of OUTER JOIN, and LEFT refers to the table on the left side of OUTER JOIN). The above example uses LEFT OUTER JOIN to select all rows from the table on the left side of the FROM clause (the customers table).
Note: MySQL does not support the use of the abbreviation characters *= and =*, although these two operators are popular in other DBMSs.

Example 5: Retrieve all customers and the number of orders placed by each customer (including customers who have not placed any orders)

Aggregate functions can be conveniently used with various join types.

Using joins and join conditions:

1. 1. Pay attention to the type of connection used. Generally we use inner joins, but outer joins are also effective.
2. 2. Ensure that the correct join conditions are used, otherwise incorrect data will be returned.
3. 3. A join condition should always be provided, otherwise a Cartesian product will result.
4. 4. Multiple tables can be included in a join, and even different join types can be used for each join. While this is legal and generally useful, you should test each connection separately before testing them together. This will make troubleshooting easier.

3. Combined Query

Combined query: Execute multiple queries (multiple SELECT statements) and return the results as a single query result set. These combined queries are often called union or compound queries.

Why do we need combined queries?

1. Return similarly structured data from different tables in a single query;
2. Execute multiple queries on a single table, returning data as a single query;
3. Using combined queries can greatly simplify complex WHERE clauses and simplify the task of retrieving data from multiple tables.

1. Create a combined query

Keywords: UNION operator

Example 1: Get a list of all items whose price is less than or equal to 5, and include all items produced by suppliers 1001 and 1002 (regardless of price).

UNION instructs MySQL to execute two SELECT statements and combine the output into a single query result set. This solution is equivalent to where prod_price<=5 OR vend_id in(1001,1002); and the rules to be noted are:

1. 1. UNION must consist of two or more SELECT statements separated by the keyword UNION (so if you combine 4 SELECT statements, you will use 3 UNION keywords).
2. 2. Each query in a UNION must contain the same columns, expressions, or aggregate functions (but the columns do not need to be listed in the same order).
3. 3. Column data types must be compatible: the types do not have to be exactly the same, but they must be types that the DBMS can implicitly convert (for example, different numeric types or different date types)
4. 4. Combined queries using UNION can apply different tables

In some simple cases, using UNION may be more complex than using a WHERE clause. But for more complex filtering conditions, or when retrieving data from multiple tables (rather than a single table), using UNION may make the process simpler.
By default, UNION automatically removes duplicate rows from the query result set. If you want to return all matching rows, use UNION ALL instead of UNION.

Note: UNION almost always accomplishes the same job as multiple WHERE conditions. UNION ALL is a form of UNION that completes the work that the WHERE clause cannot complete. If you really need all matching rows for each condition to appear (including duplicate rows), you must use UNION ALL instead of WHERE.

Example 2: Sorting combined query results

When using UNION to combine queries, only one ORDER BY clause can be used, and it must appear after the last SELECT statement. For the result set, there is no situation where one part is sorted in one way and another part is sorted in another way, so multiple ORDER BY clauses are not allowed. The ORDER BY clause sorts all results returned by all SELECT statements.

The above is the detailed integration of MySQL subqueries (nested queries), join tables, and combined queries introduced by the editor. I hope it will be helpful to everyone. If you have any questions, please leave me a message and the editor will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!