Mysql join query syntax and examples

Connection query:

It is the result of connecting each row of two queries (or tables) in pairs, that is, a row in one table with a row in another table. Perform "horizontal docking" to get a new row.

Connection queries include the following different forms and connection methods:

Cross join, inner join, outer join (divided into: left outer join, right outer join)

Join query syntax:

select * from table name [connection method] join table name [on connection condition] where ...;

Test data:

mysql> select * from test;
+----+--------+------+------+
| id | name | sex | age |
+----+--------+------+------+
| 1 | name1 | Female | 15 |
| 2 | name1 | Female | 15 |
| 4 | name2 | Male | 30 |
| 5 | name50 | Male | 12 |
+----+--------+------+------+

mysql> select * from user;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | name1 | 18 |
| 2 | name2 | 15 |
| 3 | name3 | 20 |
| 4 | name4 | 30 |
+----+-------+------+

Cross join

1. A cross join can actually be considered as a "full version" of a join query, that is, all rows are unconditionally connected.

2. The keyword "cross" can be omitted;

3. Cross join is also called "Cartesian product", which is usually of little application value

Syntax:

select * from table name, table name;
select * from table name join table name;
select * from table name cross join table name;

Standard cross-connection writing

mysql> select * from test cross join user;
+----+--------+------+------+----+-------+------+
| id | name | sex | age | id | name | age |
+----+--------+------+------+----+-------+------+
| 1 | name1 | Female | 15 | 1 | name1 | 18 |
| 2 | name1 | Female | 15 | 1 | name1 | 18 |
| 4 | name2 | Male | 30 | 1 | name1 | 18 |
| 5 | name50 | Male | 12 | 1 | name1 | 18 |
| 1 | name1 | Female | 15 | 2 | name2 | 15 |
| 2 | name1 | Female | 15 | 2 | name2 | 15 |
| 4 | name2 | Male | 30 | 2 | name2 | 15 |
| 5 | name50 | Male | 12 | 2 | name2 | 15 |
| 1 | name1 | Female | 15 | 3 | name3 | 20 |
| 2 | name1 | Female | 15 | 3 | name3 | 20 |
| 4 | name2 | Male | 30 | 3 | name3 | 20 |
| 5 | name50 | Male | 12 | 3 | name3 | 20 |
| 1 | name1 | Female | 15 | 4 | name4 | 30 |
| 2 | name1 | Female | 15 | 4 | name4 | 30 |
| 4 | name2 | Male | 30 | 4 | name4 | 30 |
| 5 | name50 | Male | 12 | 4 | name4 | 30 |
+----+--------+------+------+----+-------+------+

Use join directly to connect, which is equivalent to cross join. The default is cross join.

mysql> select * from test join user;

The query results are consistent with the above!!!

You can also directly from table, table... It is also equivalent to a cross connection

mysql> select * from test, user;

The query results are consistent with the above!!!

Inner join

1. Inner join is actually based on cross join, and some data is filtered out through on condition.

2. The keyword "inner" can be omitted, but it is recommended to include it. The on join condition is what really matters.

3. Inner join is the most widely used join query, and its essence is to filter out "meaningful data" based on conditions.

Syntax:

select * from table name join table name on join condition;
select * from table name inner join table name on join condition;

Find the data with the same name field in two tables

mysql> select * from test inner join user on test.name=user.name;
+----+-------+------+------+----+-------+------+
| id | name | sex | age | id | name | age |
+----+-------+------+------+----+-------+------+
| 1 | name1 | Female | 15 | 1 | name1 | 18 |
| 2 | name1 | Female | 15 | 1 | name1 | 18 |
| 4 | name2 | Male | 30 | 2 | name2 | 15 |
+----+-------+------+------+----+-------+------+

Find the data with the same name field in two tables, and use as to give the table a different name for use

mysql> select * from test as t inner join user as u on t.name=u.name;

The query results are consistent with the above!!!

Find the data where the name field in the two tables is equal, age>15 in the test table, and age>10 in the user table

If there are repeated fields in the table, you need to use [table name.field name] to use it, for example, age:

mysql> select * from test inner join user on test.name=user.name where test.age>15 and user.age>10;
+----+-------+------+------+----+-------+------+
| id | name | sex | age | id | name | age |
+----+-------+------+------+----+-------+------+
| 4 | name2 | Male | 30 | 2 | name2 | 15 |
+----+-------+------+------+----+-------+------+

Find the data in the two tables where the name field is equal and sex = 'male'.

If there are no duplicate fields in the table, you can use [table name.field name] or directly use [field name], for example, sex:

mysql> select * from test as t inner join user as u on t.name=u.name where t.sex='男';
mysql> select * from test as t inner join user as u on t.name=u.name where sex='男';
+----+-------+------+------+----+-------+------+
| id | name | sex | age | id | name | age |
+----+-------+------+------+----+-------+------+
| 4 | name2 | Male | 30 | 2 | name2 | 15 |
+----+-------+------+------+----+-------+------+

Outer join - left outer join

1. The left outer join is actually a kind of join that ensures that all data in the left table can be retrieved.

2. The left outer join is actually based on the inner join, plus all the data in the left table that does not meet the conditions

3. The keyword "outer" can be omitted.

Syntax:

select * from table name left [outer] join on join condition where ...;

Take the table on the left as the benchmark to ensure that all data in the table on the left is displayed, and any unmatched data is NULL

mysql> select * from test left join user on test.name=user.name;
+----+--------+------+------+------+-------+------+
| id | name | sex | age | id | name | age |
+----+--------+------+------+------+-------+------+
| 1 | name1 | Female | 15 | 1 | name1 | 18 |
| 2 | name1 | Female | 15 | 1 | name1 | 18 |
| 4 | name2 | Male | 30 | 2 | name2 | 15 |
| 5 | name50 | Male | 12 | NULL | NULL | NULL |
+----+--------+------+------+------+-------+------+

Outer join - right outer join

1. The right outer join is actually a kind of join that ensures that all data in the right table can be retrieved.

2. Right outer join is actually based on inner join, plus all the data in the right table that does not meet the conditions

3. The keyword "outer" can be omitted.

Syntax:

select * from table name right [outer] join on join condition where ...;

Take the table on the right as the benchmark to ensure that all data in the table on the right is displayed, and any unmatched data is NULL

mysql> select * from test right join user on test.name=user.name;
+------+-------+------+------+----+-------+------+
| id | name | sex | age | id | name | age |
+------+-------+------+------+----+-------+------+
| 1 | name1 | Female | 15 | 1 | name1 | 18 |
| 2 | name1 | Female | 15 | 1 | name1 | 18 |
| 4 | name2 | Male | 30 | 2 | name2 | 15 |
| NULL | NULL | NULL | NULL | 3 | name3 | 20 |
| NULL | NULL | NULL | NULL | 4 | name4 | 30 |
+------+-------+------+------+----+-------+------+

Summarize

This is the end of this article about the syntax and examples of MySQL join query. For more relevant MySQL join query content, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!