Multi-table Join query parsing

/*******************************************************************/
> Multi-table Join query parsing
/*******************************************************************/

0. Why multiple table Join queries
/*******************************************************************/
> Reasons:
In order to achieve the independence of the data sheet we are not able to design two tables, the student table and the score table, as a single table.
It needs to be split into two tables, and if I want student information and student grade information, I don't know what to
What to do, you need to use a multi-table connection query, multi-table connection query is usually associated with the primary foreign key relationship,
Joins the rows in the specified column in two tables with the same values to form a row, forming a new table.
> Principle:
A multi-table join query is actually querying data through the association of common columns between tables, which is a relational data query
The most important feature.
/*******************************************************************/

1. INNER JOIN (inner join)
/*******************************************************************/
> Introduction:
An inner join is the most typical, most commonly used join query, which matches according to a common column in a table.
In particular, when a primary foreign key relationship exists between two tables, an inner JOIN query is typically used.
Attention:
The two tables in the join are equal in status only when the values of the two specified columns are equal.
The records in two tables are connected or they will be discarded.
Query instance:
Select St.name,st.age,st.id,sc.scocre from Student St
INNER JOIN score SC
On st.id=sc.id;//conditional peering joins up as a row

You can also use a Cartesian product to make an inner join.
Select St.name,st.age,st.id,sc.score from Student St,score SC
where St.id=sc.id;

/*******************************************************************/

2. Outer Joins
/*******************************************************************/
The main difference between >//outer joins and inner joins is that the position of the two tables is equal while the inner joins
When you are out of the way, it is only when the table is the benchmark, not the two sides, that you decide whether
Join, but as long as the main side satisfies the condition, if the condition is not satisfied from the drop, it will be filled in null form.
> Left outer joins
On the left is the main table
Select S.name,c.coreseid,c.score from student s
Left OUTER JOIN score C
On s.sid=c.sid;//returns the rows of information for all student tables and has columns that have the same column values as the information of the score table to join otherwise the behavior of the score table is null
> Right outer Joins
Right outer joins and left outer joins are relative
> Full Join
/*******************************************************************/


3.union and UNION All
/*******************************************************************/
>select * FROM EMP UNION ALL SELECT * from EMP;
Combine the result set of two queries into one table (upper and lower relationships)
>select * FROM EMP Union SELECT * FROM EMP;
Merging the result set of two queries into one table will remove duplicate rows
/*******************************************************************/


4. Sub-query
/*******************************************************************/
>select * from Stuinfo where stuage> (select Stuage from Stuinfo where stuinfo.name= ' Zhangsan ');
The subquery first executes the query structure and then executes the perimeter query

In/not in
Exists/not exists
/*******************************************************************/

Multi-table Join query parsing