Shulou(Shulou.com)05/31 Report--

Editor to share with you the example analysis of join operation in Mysql, I believe that most people do not know much about it, so share this article for your reference, I hope you will learn a lot after reading this article, let's go to know it!

Type of join

1. Inner join: those records that match the join relationship between the fields that exist in the two tables form a join of the recordset.

2. External connection: it is divided into outer left connection and outer right connection.

Case background

Create table java (name varchar); insert into java values ('java1'), (' java2'), ('blue'); create table mysql (name varchar (255)); insert into mysql values (' mysql1'), ('mysql2'), (' blue')

Internal connection

Select * from java,mysql where java.name=mysql.name;SELECT * FROM java JOIN mysql ON java.name=mysql.name;SELECT * FROM java INNER JOIN mysql ON java.name=mysql.name;SELECT * FROM java CROSS JOIN mysql ON java.name=mysql.name;SELECT * FROM java STRAIGHT_JOIN mysql ON java.name=mysql.name

These four statements are all inner joins, and the return result is

+-+ | name | name | +-+-+ | blue | blue | +-+-+

Each comma in a table_reference entry is treated as equivalent to an internal union

The default JOIN is INNER JOIN.

CROSS JOIN is syntactically equivalent to INNER JOIN.

STRAIGHT_JOIN is the same as JOIN. Except for one difference, the left table is read before the right table. STRAIGH_JOIN can be used in situations where joint optimizers are listed in the wrong order.

The syntax for inner joins is as follows:

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

External connection

Left join

SELECT * FROM java LEFT JOIN mysql ON java.name=mysql.name

As a result,

+-+-+ | name | name | +-+-+ | java1 | NULL | | java2 | NULL | | blue | blue | +-+-+

So as you can see from the above results, because the name of the java1 and java2 records in the Java table does not have a corresponding name in the MySQL table, it is empty, but all the columns of the java still have java1 and java2 records, and all the columns of the mysql table are NULL. The remaining blue record is the result of a join between the java table and the mysql table.

If there are no matching records for the right table in the ON or USING section of the LEFT JOIN, a row with all columns set to NULL is used for the right table. If a table does not have a corresponding section in another table, you can use this method to find records in this table:

SELECT * FROM java LEFT JOIN mysql ON java.name=mysql.name WHERE mysql.name IS NULL

The sql finds people in the java, but not in the mysql, where it is obvious that the people 'java1' and' java2' meet the requirements.

Right connection

SELECT * FROM java RIGHT JOIN mysql ON java.name=mysql.name

The returned result is

+-+-+ | name | name | +-+-+ | NULL | mysql1 | | NULL | mysql2 | | blue | blue | +-+-+

The result of the right join is similar to that of the left join, except that this time the mysql table holds all the result sets.

The grammar of external joins

Join_table: | 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

USING (column_list) clause

Used to name a series of columns that must exist in both tables

SELECT java.*,mysql.* FROM java LEFT JOIN mysql USING (name)

Result return

+-+-+ | name | name | +-+-+ | java1 | NULL | | java2 | NULL | | blue | blue | +-+-+

Operation order of joins

SELECT * FROM T1 LEFT JOIN (T2, T3, T4) ON (t2.a=t1.an AND t3.b=t1.b AND t4.c=t1.c);-equivalent to SELECT * FROM T1 LEFT JOIN (T2 CROSS JOIN T3 CROSS JOIN T4) ON (t2.a=t1.an AND t3.b=t1.b AND t4.c=t1.c)

The influence of parentheses on the order of join

SELECT t1.id FROM T1 ON T2 LEFT JOIN T3 ON (t3.id=t1.id) WHERE t1.idquot2.idwashi-in fact, this is how SELECT t1.id ON t2.id FROM T1, (T2 LEFT JOIN T3 ON (t3.id=t1.id)) WHERE t1.idpent 2.id ON (t3.id=t1.id) WHERE t1.id=t2.id-it should be written like this: SELECT t1.idjournal t2.idLet3.id FROM (T1 LEFT JOIN T2) LEFT JOIN T3 ON (t3.id=t1.id) WHERE t1.id=t2.id.

Parentheses are very important here, so when writing such a query in the future, we should not forget to write a few more parentheses, at least this can avoid a lot of mistakes.

The above is all the contents of the article "sample Analysis of join Operations in Mysql". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.