SQL statement Summary (SAGA)--table join and join query

How can we query the information of different tables together when we talk about the inefficiency of the related sub-queries in the previous blog post? This requires a table join.

Unlike the previous union query, the Union combines different tables, that is, vertical joins, which are spelled upright.

A table join is a horizontal join of a table through a Cartesian product, and the so-called Cartesian product simply means that the rows of two tables are sequentially coupled and added together. To want more detailed understanding can Baidu under, after all, this article is mainly a summary of SQL statements.

Now there are two tables:

This is the teacher assigned a job, I stole a lazy will not change the data. But the true God-level characters to the name of the "experiment" always feel that the heart is very empty, not to mention that most of the IT industry. If there is any disrespect, I apologize first, don't stoop with me.

Well, it's far away. How do you join these two tables? Standard notation:

As a result, only a small portion of the graph is truncated, because the number of rows after the Cartesian product equals the number of rows of two tables.

Here we can understand the principle of table joins, and then add together. Of course many of them are invalid lines, in order to remove invalid rows we will use the foreign key to constrain. The _FK in the student table is associated with the _infor in the class table:

Results:

Here we get a perfect join table after the foreign key match, it can be seen as a new table, and any data can be queried from this table, which is the strength of the table join.

Classification of table joins:

Inner joins:

An inner join is a row that appears in a table when a row related to a column value match in a two table. As in the example above, S._FK and C._infor will travel to the same row, and the other rows are excluded.

The syntax is inner JOIN where inner can be omitted.

Shorthand for inner joins:

* This is the most we use.

Outer joins:

It is divided into left outer join and right place join.

An outer join is a table data that is defined as an outer join, regardless of whether there is a match, to appear in the result. For example, if the left outer join, then the table on the left side of the join is defined as an outer join, then all the data in this table will appear in the query results. Note that the class table in the four classes are no students, so after the inside of the connection is taken for granted. Now outside of the joins do the example:

The table T_class in SQL above is written on the right side of the join, so let's do an outer join with right join.

The bottom of the line is more than four classes of information

For example, we want to find out the class information that has not been entered by students:

This is the use of outer joins, which are often used when the data we want is not matched.

Self-joins:

A self-join is a special case of an inner join or outer join in which the table joined by the self-join IS from the same sheet, and the use of personal feeling is rather ingenious.

The existing table is as follows:

Table, 6 per capita belong to a company's employees. The difference is John Doe for Zhang San and Harry leaders, Zhang Ba for Zhao Liu and Sun Qi leaders. LEADER_ID is associated with work_id.

A table can now be neatly divided into employee and leader sections by self-joins:

SELECT w.work_name,l.work_name leader name from T_emp w,t_emp l WHERE w.leader_id=l.work_id;

Note the use of aliases

Results:

Isn't it a little convenient?

The knowledge points are listed here, do the Question Time to:

1. Search for the class in which Fengjie is located

SELECT _cname from T_student s,t_class c WHERE c._infor = s._fk and s._name = ' chicken sister ';

2. Check with Zhu June students in the same class

Select S._name from t_student s where s._fk = (        SELECT cc._infor from T_class cc,t_student ss WHERE SS._FK = cc._infor and ss._name = ' Zhu June ') and s._name! = ' Zhu June ';

In this case, the parentheses are the joined table, which returns the _infor of the class where the ' Zhu June ' is located, and the main query matches the rows in the student table that are equal to _infor _FK, and then rejects the ' Zhu June ' itself from the row after the successful match.

3. Check the number of people in each class

Select D._cname,count (_name) from (select Ss.*,cc._cname to T_class cc left joins t_student ss on SS._FK = cc._infor) d G Roup by D._cname;

In this case, the parentheses are the table after the class table joins, and the table after the join to alias D, according to the class name of D D._cname Group after the statistics of the class number. The reason why we use outer joins is because four classes have no students but still need to count.

4. Find the class with the largest number of classes

SELECT Cc._cname,count (_name) from T_class cc,t_student ss WHERE cc._infor = SS._FK GROUP by Cc._cname have COUNT (_name) >=all (    SELECT COUNT (_name) from T_class c,t_student s WHERE c._infor = S._FK GROUP by c._cname);

This is a bit ferocious, with two-time table joins. The number of people in each class is returned in parentheses:

It then uses a table join, compares the number of each class to the return value in parentheses, gets the maximum value, and then finds the class with the largest value. This shows how important it is to understand the sequence of SQL execution, the sequencing of joins, groupings, filters, and so on.

Results:

5. Check the lowest-age person in each class

Select Cc._cname,ss._name,ss._age from T_student ss,t_class cc WHERE SS._FK = cc._infor and Ss._age <=all (    Select M In (S._age) from t_student s WHERE ss._fk = S._FK);

In this case, the parentheses internally return the minimum age in a student table, and the outer table joins the age column to compare the return value, if it is less than or equal to the minimum value returned.

If the brackets are inside without judging the condition where SS._FK = S._FK, then only one of the youngest data will be queried, and the minimum value of each class is not given according to the query we want.

Such as:

Some people will ask, since according to the class division, with the group is not good? But it is important to note that the minimum age is more than one person, and each class after grouping will only show one person. So here's the associative condition where SS._FK = S._FK to correlate the inner surface, so we can count all the values we want.

Results: