mysql subquery and join table details

1. What is a subquery?

List all customers who ordered item TNT2:

select cust_id
from orders
where order_num IN (SELECT order_num

from orderitems
where prod_id = 'TNT2'
)

Formatting SQL SELECT statements containing subqueries are difficult to read and debug, especially when they are complex. Breaking the subquery into multiple lines as shown above and indenting them appropriately can greatly simplify the use of subqueries.

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

Note:

Columns must match When using a subquery in the WHERE clause (as shown here), you should ensure that the SELECT statement has the same number of columns as in the WHERE clause. generally,
The subquery will return and match a single column, but can use multiple columns if desired.

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

Suppose 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 orders
from customers
ORDER BY cust_name

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

Building up queries by incrementally adding subqueries Testing and debugging queries that use subqueries can be tricky, especially as the complexity of these statements increases. The most reliable way to build (and test) queries with subqueries is to do it incrementally, which is very much the same as how 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 on figuring out why the query didn't work, and greatly increases the likelihood that the query will work in the first place.

Here is the connection:

SELECT vend_name,prod_name,prod_price
FROM vendors,products
WHERE vendors.vend_id = products.vend_id
ORDER BY vend_name, prod_name

Note:

Fully qualified column names Fully qualified column names (table and column names separated by a dot) must be used when there is potential ambiguity in the reference to the column. If you reference an ambiguous column name without qualifying it with a table name, MySQL returns an error.

Here is the role of using the where statement to join:

It may seem a little strange to use a WHERE clause to establish a join relationship, but there is actually a very good reason for it. Remember that when you join several tables in a SELECT statement, the corresponding relations are constructed on the fly. There is nothing in the database table definition that tells MySQL how to join the tables. You have to do this yourself. When you join two tables, what you are actually doing is 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.

Note:

cartesian product product The result returned by a table relationship without a join condition is the 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 equijoin , which are based on an equality test between two tables. This type of join is also called an inner join. Actually, you can use a slightly different syntax for this kind of join to explicitly specify the type of join.

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

SELECT vend_name,prod_name,prod_price
FROM vendors INNER JOIN products on vendors.vend_id = products.vend_id
ORDER BY vend_name, prod_name

Which syntax to use? 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.

Performance considerations MySQL joins each table specified at runtime to process the join. 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.

Experiment a lot As you can see, there is usually more than one way to perform any given SQL operation. There are rarely absolutely right or wrong ways to do things. Performance can be affected by the type of operation, the amount of data in the table, the presence of indexes or keys, and other conditions. Therefore, it is necessary to experiment with different selection mechanisms to find the one that works best for a specific situation. We can also use multiple table joins, but there is a problem, because the table name is used in multiple places, so the table name is very long, then you can use the table alias.

like:

Several special connections are introduced below.

2. Self-join

Suppose you discover that an item (whose ID is DTNTR ) has problems and you want to know if other items produced by the supplier that produces this item also have these problems. This query requires first finding the supplier that produces the item with ID DTNTR , and then finding out other items produced by this supplier.

Here is one way to solve this problem:

You could use a subquery, like this:

select prod_id,prod_name
from products
where vend_id = (SELECT vend_id from products WHERE prod_id ='DTNTR')

Self-joins can also be used.

select t1.prod_id,t2.prod_name
from products t1, products t2
where t1.vend_id = t2.vend_id and t1.prod_id='DTNTR'

Using self-joins instead of subqueries Self-joins are often used as outer statements to replace subqueries used when retrieving data from the same table. Although the end result is the same, sometimes a join can be processed much faster than a subquery. You should try both approaches to determine which performs better.

3. Natural Join

Whenever you join tables, there should be at least one column that appears in more than one table (the column being joined). A standard join (the inner join described in the previous chapter) returns all data, even if the same columns appear multiple times. The natural join eliminates multiple occurrences so that each column is returned only once.

How to complete this work? The answer is, the system doesn't do the job, you do it yourself. A natural join is a join where you select only columns that are unique. This is typically done by using a wildcard ( SELECT * ) on the table and an explicit subset of the columns of all other tables.

4. External connections

Many joins relate rows from one table to rows from another table. But sometimes you need to include rows that have no associated rows. For example, you might want to use a join to do the following:

For example: Count how many orders each customer has placed, including those who have not yet placed an order;

SELECT customers.cust_id,order_num
from customers LEFT OUTER JOIN orders on customers.cust_id = orders.cust_id

This SELECT statement uses the keywords OUTER JOIN to specify the type of join (rather than specifying it in the WHERE clause). However, unlike an inner join, which relates rows from two tables, an outer join also includes rows that have no related rows. 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 ).

Using a join with an aggregate function:

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

SELECT customers.cust_id, COUNT(order_num) as num
from customers LEFT OUTER JOIN orders on customers.cust_id = orders.cust_id
GROUP BY cust_id

Note:
1. Pay attention to the type of connection used. Generally we use inner joins, but outer joins are also effective.
2. Ensure that the correct join conditions are used, otherwise incorrect data will be returned.
3. The join condition should always be provided, otherwise a 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.

This is the end of this article about MySQL subqueries and join tables. For more relevant MySQL subqueries and join tables, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!