1.about MySQL Join

Introduction: MySQL Join connection, summed up under a total of the following several common ways, with a picture more intuitive to display:

1.inner join: That is, the portion of B in the query graph, which is the data shared by both A and B tables, expressed in SQL:

Select *  from Inner Join  on = S.emp_no

This statement will only query the titles and salaries two tables have the same Emp_no data, the popular is that two tables have the same emp_no number of data to form a series of data, the following is the data show:

2.left join: That is, the part of A and B in the query graph, which is divided into all the data of a table and a common part of table A and B, which is represented by SQL:

Select * from titles t  left  Join dept_manager s on t. Emp_no = s. Emp_no               

This statement queries all the data for table A, and if there is data in table B and a match in Table A (via Emp_no), then the B table displays the matching data, otherwise the display is null, and the following is the data display:

3.right join: Instead of the left join, the query is the B and C parts of the diagram, which are all the data of table B and a common part of table A and B, denoted by sql:

Select * from dept_manager s right join< Span class= "Sql1-space" > titles t on  t. Emp_no = s. Emp_no               

This statement queries all the data in table B, if there is data in table A and matches in B table (via Emp_no), then a table shows the matching data, otherwise null is shown, the following is the data display:

The above is the most common Join association Query method, the following for the above join to add conditions

4. Assuming that our needs are a part of the query graph, that is, the query is a, B table in the unique part of a table, we first make the following query:

Select *  from  Left Join  on = S.emp_no

The results of the query are shown below:

So obviously from the data can be seen, we want to query is emp_no from 10015~10018 these data, SQL as follows:

Select *  from  Left Join  on =  whereisnull

To give a simple example is: When the month of wages, there are staff tables and payroll, not all employees do not necessarily pay the same day, such as system problems, then how can finance know who has not paid? If the employee ID in the employee table exists in the payroll, then it is already paid, and if the employee ID does not exist in the payroll, is the non-existent ID just not paid? Here is the data show:

5. Assuming that our needs are in the section C of the query graph, that is, the query is a, B table in the unique part B, we first make the following query:

Select *  from  Right Join  on = S.emp_no

The results of the query are shown below:

So obviously from the data can be seen, we want to query is emp_no from 10015~10018 these data, SQL as follows:

Select *  from  Right Join  on =  whereisnull

or employee and payroll as an example, table A is the employee table, B is the payroll, the Association of the query after the payroll employees and unpaid employees, now want to know which employees are not paid, as long as the associated query results add conditions to EMP_NO in the payroll table is null data, Naturally you get the unpaid data in the B table, and here's The data show:

The above is a case of adding conditions to the left and right connections, the following is a case of full connectivity

6. Full connectivity: Queries for data in a, B, c

SQL is as follows:

Select *  from  Full Join  on = S.emp_no

The query results report is abnormal:

Not to be continued ...

1.about MySQL Join