Retrieving MySQL Records

3.6 Retrieving records
Unless you finally retrieve them and use them to do something, it's not good to put the records in the database. This is the purpose of the SELECT statement, which is to help get the data out. Select is probably the most commonly used statement in the SQL language, and how to use it is the most exquisite; it can be quite complex to choose a record, and may involve comparisons between columns in many tables. The syntax for the SELECT statement is as follows:

Everything in the syntax is optional except for the column_list part of the word "S e L e C T" and the description of what you want to retrieve. Some databases also require a FROM clause. MySQL differs, which allows you to evaluate an expression without referencing any table:

In the 1th chapter, we make a lot of effort on the SELECT statement, which focuses on the list of column selections and where, GROUP by, order by, and having, and limit clauses. In this chapter, we focus on the most likely confusing aspect of the SELECT statement, that is, the connection (J o i N). We will describe the types of connections that MySQL supports, what they mean, how to specify them, and so on. Doing so will help you to use MySQL more effectively, because in many cases the key to solving how to write a query is to determine how to properly connect the tables together. You should also refer to 3 later in this chapter. Section 8 "solution essay". In that section, you will find solutions to several SQL problems, most of which
Involves a feature such as or such as a SELECT statement.
One problem with select is that when you first encounter a new problem, you don't always know how to write a select query to solve it. However, when a similar problem is encountered in the solution, the experience can be used. A SELECT is probably a statement of past experience that plays a big role in being able to use it effectively because there are so many ways to use it.
With a certain amount of experience, you can apply these experiences to new issues, and you'll find yourself thinking questions like, "Oh yes, it's a left JOIN problem." "Or," aha, this is a three-way connection that is restricted by the index columns. "(to point out this, I actually feel a bit reluctant.) You may be encouraged to hear that experience is helpful. Also, it would be a little surprising to think that you can finally think like that. The following sections describe how to take advantage of the format of the connection operations that MySQL supports, and most examples use the two tables below. They are small, simple enough to clearly see the effect of each connection.


3.6.1 Ordinary Connection
The simplest connection is a trivial connection (trivial join), in which only one table is specified. In this case, the row is selected from the specified table. Such as:

Some authors simply do not consider the form of this select connection, which uses the term "connection" only for SELECT statements that retrieve records from two or more tables. I think it's just a different view.
3.6.2 Full Connection
If more than one table is specified, the individual table names are separated by commas, and the full connection is specified. For example, if you connect two tables, each row from the first table is combined with each row in the second table:

A full connection is also known as a fork connection, because each row of each table crosses each row in the other table to produce all possible combinations. This is called the Cartesian product. This allows the connection table to potentially produce a very large number of rows, because the number of rows that can be obtained is the product of the number of lines per table. The full connection of the three tables containing 10 0, 2 0 0, and 3 0 rows will produce a 0x2 0 0x3 0 0 = 0 rows. Even if the tables are small, the number of rows can be very large. In such cases, you typically use the Where
clause to reduce the result set to manageable size.
If you add a condition in the WHERE clause so that the tables match on some columns, the connection is called an equivalent connection (e q ui-j o i n), because only those rows that have equal values in the specified column are selected. Such as:

The J O I N, CROSS join, and inner join connection types all have the same meaning as the "," join operators. Straight_join are similar to full joins, but the tables are connected in the order specified in the FROM clause. In general, the MySQL optimizer itself does not consider arranging the order of tables in full connection, so that the retrieval of records is faster. In some cases, the optimizer will make an straight_join choice, which ignores the keyword. In the SELECT statement, the s T R A i G H t _ J O I N can be given in two locations. A position is between the SELECT keyword and the select list, and it is placed here as a whole for all the full joins in the statement. The other is in the FROM clause. The following two statements are equivalent:

Qualifying Column References
A reference to a column in a SELECT statement must be unambiguous for each table specified in the FROM clause. If only one table is specified in the FROM clause, there is no ambiguity because all columns must be columns of that table. If you specify more than one table, the names of the columns that appear in only one table are unambiguous. However, if a column name appears in more than one table, the column's reference must be qualified with the table name, using the Tbl_name.col_name syntax to indicate which table it refers to. If the table MY_TBL1 contains columns A and B, and the table my_tbl2 contains columns B and C, the references for columns A and C are unambiguous, but the reference to B must be limited to my_tbl1.b or my _ T B L 2. b, such as:

Sometimes, the table name qualifier does not resolve the column's reference problem. For example, if you use a table more than once in a query, qualifying the column name with the table name is of little use. In this case, you can use aliases to express your ideas. Assign an alias to the table, using this alias to refer to the column with the syntax: alias _ name.col _ name. The following query lists the table with the
itself, assigning an alias to the table to cope with ambiguity when referencing a column:

3.6.3 Left Connection
An equivalent connection gives only two rows that match the table. Left join also gives a matching row, but it also displays rows in the table on the left that do not match in the table on the right. For such rows, the columns selected from the right table are displayed as null. In this way, each row is selected from the table on the left. If there is a matching row in the right table, the row is selected. If it does not match, the row is still selected, but it is a "false" row, where all the columns are set to NULL. In other words, the left JOIN force result set contains rows for each row in the table on the left-hand side, regardless of whether the row in the left table has a matching row in the right table. The match is based on the column given in the on or using () clause. On is available, regardless of whether the connected column has the same name. Such as:

The USING () clause is similar to O N, but the name of the connection column must be the same in each table. The following query connects my_tbl1.b to my _ T B L 2. B:

A left JOIN is extremely useful when you want to find only rows that appear on the left-hand table and do not appear in the right table. You can do this by adding a query where clause of a column with a null value in the right table.

You don't have to worry about a column that's chosen to be null, because it doesn't mean anything. The real concern is the mismatched columns in the left table, such as:

One thing to watch out for when you take advantage of a LEFT join is that if the connected column is not defined as not NULL, some unrelated rows will be drawn in the result.
The left JOIN has several synonyms and variants. The left OUTER join is a synonym for the left join.
The left JOIN also has an ODBC acceptable for MySQL to represent the following ("O J" means "outer JOIN"):

N Atural A LEFT join is similar to a right join; it performs a left-hand join that matches all columns with the same name in the table on the right and the table on the right-hand side.
Some databases also have, right JOIN, but MySQL has not yet.