Join operation in Mysql

Types of joins

1. Inner join: The fields in the two tables that have a join relationship form a record set that joins those records that meet the join relationship.

2. Outer join: divided into outer left join and outer right join.

Case Background

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

Inner Join

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 returned results are

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

• Each comma in a table_reference entry is treated as an inner join.
• The default JOIN is INNER JOIN
• CROSS JOIN is syntactically equivalent to INNER JOIN
• STRAIGHT_JOIN is the same as JOIN. Except for one thing, the left table is read before the right table. STRAIGH_JOIN can be used in situations where the join optimizer would arrange the tables in the wrong order.

The syntax for an inner join 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

Outer Join

Left Join

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

turn out

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

Therefore, from the above results, we can see that because the name of the java1 and java2 records in the Java table has no corresponding name in the MySQL table, it is empty, but all the columns of 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 the inner join between the Java table and the MySQL table.

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

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

This sql finds people who are in java but not in mysql. Obviously, people 'java1' and 'java2' meet the requirement.

Right Join

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

The result returned is

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

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

Outer Join Syntax

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);

Results

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

Order of operations for joins

SELECT * FROM t1 LEFT JOIN (t2, t3, t4) ON (t2.a=t1.a 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.a AND t3.b=t1.b AND t4.c=t1.c)

Effect of parentheses on join order

SELECT t1.id,t2.id,t3.id FROM t1,t2 LEFT JOIN t3 ON (t3.id=t1.id) WHERE t1.id=t2.id;
--Actually, the execution is: SELECT t1.id, t2.id, t3.id FROM t1, ( t2 LEFT JOIN t3 ON (t3.id=t1.id) ) WHERE t1.id=t2.id;
--Should be written like this: SELECT t1.id,t2.id,t3.id FROM (t1,t2) LEFT JOIN t3 ON (t3.id=t1.id) WHERE t1.id=t2.id;

The brackets are very important here, so when writing such queries in the future, don't forget to write a few more brackets, at least this can avoid many errors

The above is the Mysql join operation introduced by the editor. I hope it will be helpful to everyone. If you have any questions, please leave me a message and the editor will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!