MySQL connection syntax []

MySQL connection Syntax 1. meanings and differences of inner Join, Outer Join, left Join, and right Join: Let's review the Join syntax before talking about the Join Syntax of MySQL, in fact, I forgot about it myself. Let's take a look at it. (if the content is incorrect or you have any questions, you can send me a letter for consulting: Chen Pengyi chenpengyi # gmail.com ).

MySQL connection Syntax 1. meanings and differences of inner Join, Outer Join, left Join, and right Join: Let's review the Join syntax before talking about the Join Syntax of MySQL, in fact, I forgot about it myself. Let's take a look at it. (if the content is incorrect or you have any questions, you can send me a letter for consulting: Chen Pengyi chenpengyi # gmail.com ).

MySQL connection syntax

1. Meanings and differences of inner join, Outer Join, left join, and right join:

Before talking about the Join Syntax of MySQL, I should review the Join syntax. Oh, I forgot about it myself, let's take a look at it together. (if the content is incorrect or you have any questions, you can send a letter to consult: Chen Pengyi chenpengyi # gmail.com). There is very little information about MySQL connection queries in China, I believe that after reading this article, you will have a clear understanding of the MySQL connection syntax and will not be confused by Oracle's external connections ("+.

In the SQL standard, the planned Join is roughly divided into the following four types:

1. Inner join: the records that match the joined fields in the two tables form a join of the record set.

2. Outer Join: outer left join and outer right join.

Table A and table B on the left join means to link all the records in Table A and the joined fields in Table B with those records in Table A that meet the join conditions., note that the final record Assembly includes all records of Table.

The result of right join A and B is the same as that of left join B and A, that is:

Select A. name B. name From A Left Join B On A. id = B. id

The result is the same as that of Select A. name B. name From B Right Join A on B. id = A. id.

3. full join: retrieve all records of fields with a join relationship between the two tables to form a join of the record set (this does not need to be remembered, as long as the fields in the table mentioned in the query are retrieved, whether or not the join conditions are met, it is of little significance ).

4. No join: You don't have to explain it, that is, you don't have to use the join function. You also have a self join statement.

Here I have a simple memory method. The difference between the inner and the inner join is that the inner join removes all non-conforming records, while the outer join retains some of them. The difference between outer left join and outer right join is that if A joins B on the left, all records in A are retained in the result. In this case, only records meeting the join condition in B are retained, on the contrary, the right join will not be confused. In fact, we recall the contents of the chapter on relational algebra (that is, the chapter "Introduction to database systems" published by the Higher Education Press). I believe it is not difficult to understand the connotation of these functions.

2. MySQL Join syntax

MySQL supports the Join syntax in Select and some Update and Delete conditions. The syntax details include:

Table_references:

Table_reference [, table_reference]…

Table_reference:

Table_factor

| Join_table

Table_factor:

Tbl_name [[AS] alias]

[{USE | IGNORE | FORCE} INDEX (key_list)]

| (Table_references)

| {OJ table_reference left outer join table_reference

ON conditional_expr}

Join_table:

Table_reference [INNER | CROSS] JOIN table_factor [join_condition]

| Table_reference STRAIGHT_JOIN table_factor

| Table_reference STRAIGHT_JOIN table_factor ON condition

| Table_reference LEFT [OUTER] JOIN table_reference join_condition

| Table_reference NATURAL [LEFT [OUTER] JOIN table_factor

| Table_reference RIGHT [OUTER] JOIN table_reference join_condition

| Table_reference NATURAL [RIGHT [OUTER] JOIN table_factor

Join_condition:

ON conditional_expr | USING (column_list)

The above usage is taken from authoritative materials, but do you think it is a bit dizzy? What is table_reference and table_factor? Table_reference actually refers to Table reference, because in MySQL, join is a reference to a table. Therefore, the table to be joined is defined as table_reference, this is also true in SQL Standard. Table_factor is the enhancement and expansion of MySQL's reference function, so that the referenced table can be a series of tables in parentheses, as follows:

SELECT * FROM t1 left join (t2, t3, t4) ON (t2.a = t1.a AND t3. B = t1. B AND t4.c = t1.c)

The execution result of this statement is the same as that of the following statement:

SELECT * FROM t1 left join (t2 cross join t3 cross join t4)

ON (t2.a = t1.a AND t3. B = t1. B AND t4.c = t1.c)

These two examples not only show us the meanings of table_factor and table_reference in MySQL, but also understand the usage of cross join, what I want to add is that in the current MySQL version, cross join works the same as inner join (although it is different in SQL Standard, however, in MySQL, the difference is that inner join requires an ON parameter, but cross join does not ).

Since the ON statement is mentioned, Let's explain it. The ON statement is basically equivalent to the WHERE statement function, but the ON statement here is dedicated to join tables, the condition requirements and writing method after the ON statement are the same as those of the WHERE statement. You can basically use the ON statement as the WHERE statement.

You may also see the sentence "OJ table_reference left outer join table_reference". This is not a standard MySQL statement, but is set to be compatible with odbc SQL syntax. It is rarely used, java is not useful, so there is no explanation.

Next, let's talk about the simple JOIN usage. First, assume there are two tables A and B. Their table structure and fields are:

Table:

ID	Name
1	Tim
2	Jimmy
3	John
4	Tom

Table B:

ID	Holobby
1	Football
2	Basketball
2	Tennis
4	Soccer

1. Inner join:

Select A. Name B. Hober from A, B where A. id = B. id, which is an implicit inner join. The query result is:

Name	Holobby
Tim	Football
Jimmy	Basketball
Jimmy	Tennis
Tom	Soccer

It works the same as Select A. Name from a inner join B on a. id = B. id. Here, the inner join can be changed to cross join.

2. outer left join

Select. name from A Left join B ON. id = B. id, A typical outer left join. In this way, the query result will be record of all join fields in Table A. If there is no corresponding field record in Table B, leave it blank, the result is as follows:

Name	Holobby
Tim	Football
Jimmy	Basketball, Tennis
John
Tom	Soccer

Therefore, we can see from the above results that the John Record ID in Table A does not have A corresponding ID in Table B, so it is empty, but the Name column still has A John record.

3. Outer right join

If you change the preceding query to outer Right JOIN: Select A. Name from A Right join B on a. id = B. id, the result will be:

Name	Holobby
Tim	Football
Jimmy	Basketball
Jimmy	Tennis
Tom	Soccer

This result can be guessed from the outer left join result.

Speaking of this, do you know more about join queries? It seems that this profound concept was suddenly understood and suddenly realized (haha, joke )? Finally, let's talk about the functions of some MySQL connection query parameters:

1. USING (column_list): it is used to conveniently write multiple Mappings of the join. In most cases, the USING statement can be replaced by the ON statement, as shown in the following example:

A left join B USING (c1, c2, c3), which is equivalent to the following statement

A left join B ON a. c1 = B. c1 AND a. c2 = B. c2 AND a. c3 = B. c3

It is only troublesome to use ON instead of writing.

2. NATURAL [LEFT] JOIN: This sentence serves as inner join, or contains Left JOIN (left join) of all fields in the joined table in the USING clause ).

3. STRAIGHT_JOIN: by default, MySQL first reads the left table during table join. When this parameter is used, MySQL reads the right table first, this is a built-in Optimization Parameter of MySQL. You should use it in certain circumstances. For example, if you have confirmed that the number of records in the right table is small, the query speed can be greatly improved after filtering.

The final point is that after MySQL5.0, the operation order has been paid attention to. Therefore, join queries for multiple tables may fail to be performed in subjoin queries. For example, if you need to join multiple tables, you enter the following join query:

SELECT t1.id, t2.id, t3.id

FROM t1, t2

Left join t3 ON (t3.id = t1.id)

WHERE t1.id = t2.id;

However, MySQL does not execute this way. The real execution method in the background is the following statement:

SELECT t1.id, t2.id, t3.id

FROM t1, (t2 left join t3 ON (t3.id = t1.id ))

WHERE t1.id = t2.id;

This is not what we want, so we need to input the following:

SELECT t1.id, t2.id, t3.id

FROM (t1, t2)

Left join t3 ON (t3.id = t1.id)

WHERE t1.id = t2.id;