SQL statement Multi-table join query syntax

One, outer connection

1. Left-side connection to the Ieft join or to the outer join

SQL statement:SELECT * FROM student left joins score on student. Num=score. stu_id;

2. Right-connect to the join on either or outer join

SQL statement:SELECT * FROM student right joins score on student. Num=score. stu_id;

3. Fully out-of-band join or full outer join

SQL statement:SELECT * FROM student full join score on student. Num=score. stu_id;

The above three methods can be used to connect the different tables together, into a large table, then the query operation is simple.

For select * from Student,score, the result is too cumbersome to use this statement as much as possible.

Second, internal connection

Join or INNER JOIN

SQL statement:SELECT * FROM student inner joins score on student. Num=score. stu_id;

The statement at this point is equivalent to: SELECT * from Student,score where student.id=course.id;

Three, cross-linking

Cross join, where no where specifies the clause of the query condition, will result in a Cartesian product of two tables.

SQL statement:SELECT * FROM student cross join score;

Iv. table connections with different structures

When the two tables are many-to-many relationships, we need to create an intermediate table Student_score, with at least two tables of primary keys in the intermediate table.

SQL statements: Select S.name,c.cname from Student_score as SC left joins student as S on S.SNO=SC. Sno left joins score as C on C.CNO=SC. Cno

Select  from student left joins score on student. Num=score. stu_idwhere name=' Lee 51 ';

The red part, the intermediate table , is a summary table of all the contents of the two tables.

The union operator is used to combine the result set of two or more SELECT statements.

The SELECT statement inside the Union must have the same number of columns, and each column must have a similar data type , and the order of the columns in each SELECT statement must be the same.

Select  from Union Select  from score;

The Union operator is the default check, and if duplicate values are allowed, the union all can be used. For two tables with the same structure, union can also merge them into a single table:

Select *  from Union Select *  from Student2;

After studying so much for the time being, there is a better way to add it later.

SQL statement Multi-table join query syntax