SQL join--Primary article

Written in the previous words:

The following is the simplest join principle, for the later large data distributed join to do concept review and knowledge foreshadowing:

Sometimes in order to get the complete result, we need to get the results from two or more tables. We need to execute the join.

• JOIN: Returns a row if there is at least one match in the table
• Left JOIN: Returns all rows from the table, even if there is no match in the right table
• Right JOIN: Returns all rows from the correct table even if there is no match in the left table
• Full JOIN: Returns a row if there is a match in one of the tables

Reprint a simple example to illustrate:

Table A
name Sex
Zhang San male
Li Shiju

B Table
name Age
Lee 430
Wang 523

1\ Full External connection
Select A.name,a.sex,b.name,b.age
From a full outer join B on A.name=b.name
the results are as follows
name Sex name age
Zhang San male null null
li si li 430
null NULL King 523
The associated field name, the left table has and the right table does not have, such as Zhang San, b.name,b.age are null, plus left and right both sides are the result of the Zuo connection, and the left table has no, such as Harry, A.name, A.sex are null, plus left and right both sides are the result of the connection, and both sides have as, John Doe, this is the inner connection. Meet as follows
2\ left
Select A.name,a.sex,b.name,b.age
From a LEFT outer join B on A.name=b.name
The results are as follows
name Sex name age
Zhang San male null null
li si li 430
2\ Right
Select A.name,a.sex,b.name,b.age
From a right outer join B on A.name=b.name
The results are as follows
name Sex name age
li si li 430
null NULL King 523
3\ Inline
Select A.name,a.sex,b.name,b.age
From a INNER join B on A.name=b.name
The results are as follows
name Sex name age
li si li 430
4\ Crossover
Select A.name,a.sex,b.name,b.age
From a cross join B on A.name=b.name
The results are as follows
name Sex name age
Zhang San male lee 430
John Doe Queen 523
Zhang San male king 523Li si li 430

Summary:

INNER join within joins--displays only the number of record records that exist for both tables <= any table
Left join--shows the number of records that exist on the left table = left table
Right Join--show the right table all the records that exist = Right table
Full join outer Joins--Displays the number of record records that exist for a table in two tables >= any table
Cross join crossover--each record for left table corresponds to the number of records per record in the right table = Left table * Right table
Here's a place worth exploring:
Seemingly cross joins have little practical significance, and the Cartesian product of the two tables is excessively redundant, and the result of the join does not work.
However, the Internet to find such a statement, some reason

For example:

There is an employee table, a Salary type table (hourly wage, piece rate, pay for work, overtime allowance)

The Employee table cross join wage type table again left joins the result of the payroll table

It's very intuitive to see how many different types of salaries each employee has.

My view is that if the above design is good, the Employee table and payroll table directly connected to the left, still can get all the information of each employee, but some types of wage workers do not show the salary, this problem needs to be further explored! ~~

SQL join--Primary article