SQL statement for MySQL multi-table joint Query

This article will introduce you to the Summary of SQL statements for the Combined Query of multiple tables in PHP and MySQL. For more information, see.

The simplest kind of joint queries

The Code is as follows:	Copy code
SELECT * FROM table1 n, table2 I WHERE n. itemid = I. itemid

Example

Promotion location table promotion_addr field: name, id

The unicom_record field in the ticket table is date and sheet_id (associated with the sheet table id)

Sheet field: id, promotion_addr_id (associated with promotion_addr table id)

It is required to count the number of promotions that have been reported to each promotion site in a certain month;

 

The Code is as follows:	Copy code
SELECT P. name, count (*) FROM Unicom_record AS ur Left Join sheet AS s ON ur. sheet_id = s. id Left Join promotion_addr AS p ON s. promotion_addr_id = p. id WHERE Ur. status =? 'Complete' AND Ur. 'date'> =? '2017-03-01' AND Ur. 'date' <? '2017-04-01 ′ GROUP P. id

(Or use the between and Statement)

INNER JOIN

Join_table: table_reference [INNER | CROSS] JOIN table_factor [join_condition]

External Connection in MySQL

Select id, name, action from user as u left join user_action a on u. id = a. user_id

Create table first

Create table emp (
Id int not null primary key,
Name varchar (10)
);

Create table emp_dept (
Dept_id varchar (4) not null,
Emp_id int not null,
Emp_name varchar (10 ),
Primary key (dept_id, emp_id ));

Insert into emp () values
(1, "Dennis-1 ″),
(2, "Dennis-2 ″),
(3, "Dennis-3 ″),
(4, "Dennis-4 ″),
(5, "Dennis-5 ″),
(6, "Dennis-6 ″),
(7, "Dennis-7 ″),
(8, "Dennis-8 ″),
(9, "Dennis-9 ″),
(10, "Dennis-10 ″);

Insert into emp_dept () values
("R & D", 1, "Dennis-1 ″),
("DEv", 2, "Dennis-2 ″),
("R & D", 3, "Dennis-3 ″),
("Test", 4, "Dennis-4 ″),
("Test", 5, "Dennis-5 ″);

> Left join
-----

The Code is as follows:	Copy code
Select a. id, a. name, B. dept_id From emp a left join emp_dept B on (a. id = B. emp_id );

# Pick out all the data in table emp on the left, even if there is no data in emp_dept, and if there is no data, use NULL to display it,
# That is, the data displayed is based on the data in table emp on the left.

The Code is as follows:	Copy code
Mysql> select a. id, a. name, B. dept_id -> From emp a left join emp_dept B on (a. id = B. emp_id );

+ -- + ---- + --- +
| Id | name | dept_id |
+ -- + ---- + --- +
| 1 | Dennis-1 | R & D |
| 2 | Dennis-2 | DEv |
| 3 | Dennis-3 | R & D |
| 4 | Dennis-4 | Test |
| 5 | Dennis-5 | Test |
| 6 | Dennis-6 | NULL |
| 7 | Dennis-7 | NULL |
| 8 | Dennis-8 | NULL |
| 9 | Dennis-9 | NULL |
| 10 | Dennis-10 | NULL |
+ -- + ---- + --- +
# Picking out the personnel information in table emp but not in table emp_dept

The Code is as follows:	Copy code
Select a. id, a. name, B. dept_id From emp a left join emp_dept B on (a. id = B. emp_id) Where B. dept_id IS NULL; Mysql> select a. id, a. name, B. dept_id -> From emp a left join emp_dept B on (a. id = B. emp_id) -> Where B. dept_id IS NULL;

+ -- + ---- + --- +
| Id | name | dept_id |
+ -- + ---- + --- +
| 6 | Dennis-6 | NULL |
| 7 | Dennis-7 | NULL |
| 8 | Dennis-8 | NULL |
| 9 | Dennis-9 | NULL |
| 10 | Dennis-10 | NULL |
+ -- + ---- + --- +

# Put table emp_dept on the left (of course, the information is displayed based on the data in emp_dept, and more information in emp than emp_dept will not be displayed ):

The Code is as follows:	Copy code
Select a. id, a. name, B. dept_id From emp_dept B left join emp a on (a. id = B. emp_id ); Mysql> select a. id, a. name, B. dept_id -> From emp_dept B left join emp a on (a. id = B. emp_id );

+ -- + ---- + --- +
| Id | name | dept_id |
+ -- + ---- + --- +
| 2 | Dennis-2 | DEv |
| 1 | Dennis-1 | R & D |
| 3 | Dennis-3 | R & D |
| 4 | Dennis-4 | Test |
| 5 | Dennis-5 | Test |
+ -- + ---- + --- +

> Right join
-----

The Code is as follows:	Copy code
Select a. id, a. name, B. dept_id From emp a right join emp_dept B on (a. id = B. emp_id );

# Display data based on the data in table emp_dept on the right when selecting data

The Code is as follows:	Copy code
Mysql> select a. id, a. name, B. dept_id -> From emp a right join emp_dept B on (a. id = B. emp_id );

+ -- + ---- + --- +
| Id | name | dept_id |
+ -- + ---- + --- +
| 2 | Dennis-2 | DEv |
| 1 | Dennis-1 | R & D |
| 3 | Dennis-3 | R & D |
| 4 | Dennis-4 | Test |
| 5 | Dennis-5 | Test |
+ -- + ---- + --- +
5 rows in set (0.00 sec)

# Let's switch the position of the table and use right join.

The Code is as follows:	Copy code
Select a. id, a. name, B. dept_id From emp_dept B right join emp a on (a. id = B. emp_id ); Mysql> select a. id, a. name, B. dept_id -> From emp_dept B right join emp a on (a. id = B. emp_id );

+ -- + ---- + --- +
| Id | name | dept_id |
+ -- + ---- + --- +
| 1 | Dennis-1 | R & D |
| 2 | Dennis-2 | DEv |
| 3 | Dennis-3 | R & D |
| 4 | Dennis-4 | Test |
| 5 | Dennis-5 | Test |
| 6 | Dennis-6 | NULL |
| 7 | Dennis-7 | NULL |
| 8 | Dennis-8 | NULL |
| 9 | Dennis-9 | NULL |
| 10 | Dennis-10 | NULL |
+ -- + ---- + --- +

# Is it the same as left join?

> Direct join
-----
# If right join is used and data is directly selected without Join, the following commands are introduced:

The Code is as follows:	Copy code
Select a. id, a. name, B. dept_id From emp a, emp_dept B Where a. id = B. emp_id; Mysql> select a. id, a. name, B. dept_id -> From emp a, emp_dept B -> Where a. id = B. emp_id;

+ -- + ---- + --- +
| Id | name | dept_id |
+ -- + ---- + --- +
| 2 | Dennis-2 | DEv |
| 1 | Dennis-1 | R & D |
| 3 | Dennis-3 | R & D |
| 4 | Dennis-4 | Test |
| 5 | Dennis-5 | Test |
+ -- + ---- + --- +

What do you do?

Enjoy it!