How to use WHERE clause to join multiple tables in MySQL

The Importance of the WHERE Clause

It may seem a bit strange to use the WHERE clause to establish a join relationship, but in fact, there is a very good reason. Remember that when joining several tables in a SELECT statement, the corresponding relationships are constructed on the fly. There is nothing in the definition of a database table that tells MySQL how to join the tables. You have to do this yourself. When you join two tables, what you're actually doing is pairing every row in the first table with every row in the second table. The WHERE clause acts as a filter, which includes only those rows that match the given condition (here, the join condition). Without a WHERE clause, every row in the first table is paired with every row in the second table, regardless of whether they logically fit together.

Cartesian product The result returned by a table relationship without join conditions 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.

Input:

select vend_name,prod_name,prod_price from vendors,products order by vend_name,prod_name;

Analysis: The corresponding Cartesian product is not what we want. The data returned here matches each product with each supplier, and it includes incorrect products from

suppliers. In fact, some suppliers have no products at all.

Don’t forget the WHERE clause. You should ensure that all joins have a WHERE clause, otherwise MySQL will return much more data than you want. In the same way, the correctness of the WHERE clause should be ensured. Incorrect filter conditions will cause MySQL to return incorrect data.

Cross Join Sometimes we hear join types that return a Cartesian product called a cross join.

Inner Join

The join used so far is called an equijoin and is based on an equality test between two tables. This connection is also called an internal connection. Actually, a slightly different syntax can be used 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:

Input:

select vend_name,prod_name,prod_price from vendors inner join products on vendors.vend_id = products.vend_id;

Analysis: The SELECT in this statement is the same as the previous SELECT statement, but the FROM clause is different. Here, the relationship between the two tables is part of the FROM clause, specified with INNERJOIN. When using this syntax, the join condition is given with a specific ON clause instead of a WHERE clause. The actual condition passed to ON is the same as that passed to WHERE.

Which syntax to use? The ANSI SQL specification prefers INNER JOIN syntax. Additionally, while it is indeed simpler to define joins using a WHERE clause, using explicit join syntax ensures that join conditions are not forgotten, which can sometimes impact performance.

Join multiple tables

SQL has no idea about the number of tables that can be joined in a SELECT statement limit. The basic rules for creating joins are the same. First list all the tables and then define the relationships between the tables. For example:

Input:

select prod_name,vend_name,prod_price,quantity from orderitems,products,vendors where products.vend_id = vendors.vend_id and orderitems.prod_id = products.products.prod_id and order_num = 20005;

Output:

Analysis: This example shows the items in order number 20005. Order items are stored in the orderitems table. Each product is stored by its product ID, which references the product in the products table. These products are linked to the corresponding vendors in the vendors table via the vendor ID, which is stored in each product's record. The FROM clause here lists three tables, and the WHERE clause defines these two join conditions, and the third join condition is used to filter out the items in order 20005.

Performance Considerations MySQL associates each table specified to handle the join at runtime. This processing can be very resource intensive, so care should be taken not to join unnecessary tables. The more tables are joined, the greater the performance degradation.

Experiment More As you can see, there is generally more than one way to perform any given SQL operation. There is rarely an absolutely right or absolutely wrong approach. Performance can be affected by the type of operation, the amount of data in the table, the presence of indexes or keys, and several other conditions. Therefore, it is necessary to experiment with different selection mechanisms to find out what works best for specific situations.

[Related recommendations]

1. What is a mysql subquery? How to filter using subqueries?

2.mysql creates calculated fields using subqueries

3.What are joins and relational tables in mysql?

4. Why use joins and how to create joins

The above is the detailed content of How to use WHERE clause to join multiple tables in MySQL. For more information, please follow other related articles on the PHP Chinese website!