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

Editor to share with you the example analysis of mysql sub-query and join table. I hope you will get something after reading this article. Let's discuss it together.

1. What is a subquery?

List all customers who have ordered items TNT2:

Select cust_idfrom orderswhere order_num IN (SELECT order_numfrom orderitemswhere prod_id = 'TNT2')

SELECT statements that format SQL that contain subqueries are difficult to read and debug, especially when they are complex. Decomposing subqueries into multiple rows and indenting them appropriately, as shown above, greatly simplifies the use of subqueries.

There is no limit to the number of subqueries that can be nested, but in actual use, due to performance constraints, too many subqueries cannot be nested.

Note:

The columns must match the use of subqueries in the WHERE clause (as shown here), and you should ensure that the SELECT statement has the same number of columns as in the WHERE clause. Usually

The subquery returns a single column and matches it, but you can also use multiple columns if necessary.

In addition to subqueries that can be placed in where, they can also be placed in select.

If you need to display the total number of orders for each customer in the customers table.

Select cust_name, cust_state, (SELECT COUNT (*) FROM orders WHERE orders.cust_id = customers.cust_id) as ordersfrom customersORDER BY cust_name

The running process of mysql is to first execute the cust_name,cust_state,cust_id found in customers, and then execute 5 subqueries to find out the results.

Gradually adding subqueries to create queries testing and debugging queries with subqueries is very skillful, especially when the complexity of these statements is increasing. The surest way to build (and test) queries with subqueries is to proceed gradually, much the same way MySQL handles them. First, create and test the innermost query. The outer query is then created and tested with hard-coded data, and the subquery is embedded only after it has been confirmed to be normal. At this point, test it again. Repeat these steps for each query you want to add. This only adds a little time to constructing the query, but saves a lot of time later (figuring out why the query is abnormal) and greatly increases the likelihood that the query will work properly from the start

Here is an introduction to the connection:

SELECT vend_name,prod_name,prod_priceFROM vendors,productsWHERE vendors.vend_id=products.vend_idORDER BY vend_name,prod_name

Note:

Fully qualified column names when referenced columns may be ambiguous, you must use fully qualified column names (table names and column names separated by a dot). If you reference an ambiguous column name that is not restricted by a table name, MySQL returns an error.

Here the where statement is used for the purpose of joining:

It may seem strange to use the WHERE clause to establish a join, but in fact, there is a very good reason. Remember that when you join several tables in a SELECT statement, the corresponding relationships are constructed at run time. There is nothing in the definition of a database table that tells MySQL how to join the table. You must do it yourself. When joining two tables, what you actually do is match each row in the first table with each row in the second table. The WHERE clause, as a filter condition, contains only those rows that match a given condition (in this case, the join condition). Without the WHERE clause, each row in the first table will be paired with each row in the second table, regardless of whether they logically match or not.

Note:

Cartesian product (cartesian product) the result returned by a table relationship without join conditions is a Cartesian product. 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. The joins used so far are called equivalent joins (equijoin), which are based on equality tests between two tables. This kind of connection is also called internal connection. In fact, you can use a slightly different syntax to specify the type of join for this type of join.

The following SELECT statement returns exactly the same data as the previous example:

SELECT vend_name,prod_name,prod_priceFROM vendors INNER JOIN products on vendors.vend_id = products.vend_idORDER BY vend_name,prod_name

Which syntax to use? the ANSI SQL specification prefers INNER JOIN syntax. In addition, although it is easy to define joins using the WHERE clause, use explicit

Join syntax ensures that you don't forget join conditions, and sometimes doing so can affect performance.

Performance considerations consider that MySQL associates each specified table at run time to handle joins. This kind of processing can be very resource-intensive, so you should be careful not to connect

Unnecessary watch. The more tables you join, the worse the performance degradation.

As you can see, there is generally more than one way to perform any given SQL operation. There are few ways to be absolutely right or wrong. Performance possibility

It is affected by the type of operation, the amount of data in the table, the existence of indexes or keys, and other conditions. Therefore, it is necessary to experiment with different selection mechanisms to find

Come up with the method that best suits the specific situation. We can also use the join of multiple tables, but there is a problem, because the table name is used in multiple places, so the table name is very long, so we can use the alias of the table.

Such as:

Here are some special connections.

2. Self-connection

If you find that there are problems with an item (whose ID is DTNTR), you want to know if other items produced by the supplier who produced the item also have these problems. This query requires that you first find the supplier that produces items whose ID is DTNTR, and then find other items produced by this supplier.

Here is one way to solve this problem:

You may use a subquery to do this:

Select prod_id,prod_namefrom productswhere vend_id = (SELECT vend_id from products WHERE prod_id = 'DTNTR')

You can also use self-join.

Select t1.proddistribuidrect. Prodformnamefrom products T1, products t2where t1.vend_id = t2.vend_id and t1.proddistribuidwriting DTNTR'

Using self-join instead of subquery is often used as an external statement to replace the subquery statement used when retrieving data from the same table. Although the end result is

The same, but sometimes dealing with joins is much faster than subqueries. You should try two methods to determine which performs better.

3. Natural connection

Whenever a table is joined, there should be at least one list that is now in more than one table (joined columns). Standard joins (internal joins described in the previous chapter) return all data, and even the same columns appear multiple times. Natural joins exclude multiple occurrences so that each column is returned only once.

How to finish the work? The answer is that the system doesn't finish the job, it's up to you to do it. A natural join is a join in which you can only select the only columns. This is typically done by using the wildcard character (SELECT *) on the table to use an explicit subset of the columns of all other tables.

4. External connection

Many joins associate rows in one table with rows in another table. Sometimes, however, you need to contain rows that are not associated with them. For example, you might need to use joins to do the following:

For example, count the number of orders placed by each customer, including those who have not yet placed an order.

SELECT customers.cust_id,order_numfrom customers LEFT OUTER JOIN orders on customers.cust_id = orders.cust_id

This SELECT statement uses the keyword OUTER JOIN to specify the type of join (not in the WHERE clause). However, unlike the rows in the two tables associated with the internal join, the external join also includes rows that do not have associated rows. When using OUTER JOIN syntax, you must use the RIGHT or LEFT keyword

Specify the table that includes all its rows (RIGHT indicates the table to the right of OUTER JOIN, while LEFT indicates the table to the left of OUTER JOIN).

Use joins with aggregate functions:

To retrieve all customers and the number of orders placed by each customer

SELECT customers.cust_id, COUNT (order_num) as numfrom customers LEFT OUTER JOIN orders on customers.cust_id = orders.cust_idGROUP BY cust_id

Note:

1. Note the type of join used. In general, we use internal joins, but using external joins is also effective.

two。 Make sure you use the correct join condition, otherwise incorrect data will be returned.

3. The connection condition should always be provided, otherwise the Cartesian product will be obtained.

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.

After reading this article, I believe you have some understanding of "sample Analysis of mysql Sub-query and join Table". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for your reading!

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.