SQL Summary (ii) List query

---restore content starts---

SQL Summary (ii) List query

Connection queries include merges, inner joins, outer joins, and Cross joins, and it is important to understand the characteristics of these connections if multiple table queries are involved.

Only if you really understand the difference between them, can they be used correctly.

1. Union

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

The UNION operator derives a result table by combining the other two result tables (for example, TABLE1 and TABLE2) and eliminating any duplicate rows in the table.

When all is used with the Union (that is , union ALL), duplicate rows are not eliminated. In both cases, each row of the derived table is either from TABLE1 or from TABLE2.

Note: When you use union, the results of the two table queries have the same number of columns, and the column types are similar.

Student table information (Students):

Id	Name	Age	City	Majorid
101	Tom	20	Beijing	10
102	Lucy	18	Shanghai	11

Teacher table Information (Teachers):

Id	Name
101	Mrs Lee
102	Lucy

Preset script:

Insert into Students (Id,name,age,city,majorid) VALUES (101, ' Tom ', ' Beijing ', ' ten ') insert into Students (Id,name,age, City,majorid) VALUES (102, ' Lucy ', ' Shanghai ', ' all ') insert into Teachers (Id,name) VALUES (101, ' Mrs Lee ') insert INTO Teachers (Id,name) VALUES (102, ' Lucy ')

1) Basic union query, query the school teacher, student's general information table, including ID and name

SELECT Id,name from Studentsunionselect id,name from Teachers

Query Result:

Id	Name
101	Mrs Lee
101	Tom
102	Lucy

2) with conditional union query, you can also query the same table, check the age of 18, 23 years old student information

SELECT id,name from Student where Age=18unionselect id,name from Student where age=23

Of course, this can be easily achieved using in or OR, here is just point to, later encountered complex query, I believe you will use.

3) Find all the names of teachers and students

Because union will only choose a different value, if the student has the same name as the teacher, this will require the UNION all

SELECT name from Studentsunion allselect Name from Teachers

Query Result:

Id	Name
101	Tom
102	Lucy
101	Mrs Lee
102	Lucy

2. INNER join (inner connection)

INNER Join (inner connection), also become a natural connection

Effect: Queries data from these tables based on the relationship between the columns in two or more tables.

Note: An inner connection removes all rows from the result that have no matching rows in the joined table, so the inner connection may lose information.

Focus: Internal connection, only matching rows.

Syntax: (inner can be omitted)

SELECT fieldlistfrom table1 [INNER] Join Table2on Table1.column=table2.column

Student table information (Students):

Id	Name	Age	City	Majorid
101	Tom	20	Beijing	10
102	Lucy	18	Shanghai	11

Professional Information Sheet (majors):

Id	Name
10	中文版
12	Computer

Preset script:

DELETE from Studentsinsert to Students (Id,name,age,city,majorid) VALUES (101, ' Tom ', +, ' Beijing ', ten) INSERT into Students (Id,name,age,city,majorid) VALUES (102, ' Lucy ', and ' Shanghai ', one) DELETE from Majorsinsert to majors (id,name) VALUES (' 中文版 ') INSERT into majors (id,name) VALUES (' computer ')

Example: Query student information, including ID, name, professional name

SELECT Students.id,students.name,majors.name as Majornamefrom Students INNER JOIN majorson students.majorid = majors.id

Query Result:

Id	Name	Majorname
101	Tom	中文版

According to the results, it is clear that only matching rows are true. The student Lucy's information was lost.

However, Inner joins also produce duplicate data . If you remove the primary KEY constraint for the majors table, you can insert a duplicate ID, such as:

DELETE from Majorsinsert to majors (id,name) VALUES (' 中文版 ') INSERT into majors (id,name) VALUES (' computer ')

Continue execution of the above associated statement, with the result:

Id	Name	Majorname
101	Tom	中文版
101	Tom	Computer

If the left join also has a duplicate record, the result is:

Id	Name	Majorname
101	Tom	中文版
101	Tom	Computer
102	Lucy	Null

The right join result is the same as inner join.

Later on we will delve into the specific principles of join.

3. External connection

The complete collection of a table is returned, even if there are no matching rows, compared to an inner join.

The outer connection is divided into three kinds: Left outer connection, right outer connection, full outer connection. Corresponds to Sql:left/right/full OUTER JOIN. Usually we omit the outer keyword. Written as: Left/right/full JOIN.

Emphasis: At least one party retains the complete collection, and no matching rows are replaced with null.

1) left OUTER join, referred to as Ieft join, outer join (left connection)

The result set retains all the rows of the left table, but contains only the rows that match the second table with the first table. The corresponding empty row of the second table is put into a null value.

Examples of links that are still in use

(1) Use the left link to query the student's information, including student ID, student name and professional name.

SELECT Students.id,students.name,majors.name as Majornamefrom Students left JOIN majorson students.majorid = majors.id

Results:

Id	Name	Majorname
101	Tom	中文版
102	Lucy	Null

Conclusion:

As a result, we can see that the left connection contains all the information for the first table, and if there is no match in the second table, it is replaced with null.

2) Right-join (right-outer join) on-off connection

The right outer join retains all the rows of the second table, but only the rows that match the second table with the first table. The first table is given a null value for the corresponding empty row.

The right connection is similar to the idea of left join. It's just the second one. If there is no match in the first table, use NULL instead

Still follow the example of internal links, but instead of connecting to right

(1) Use the right link to query the student's information, including student ID, student name and professional name.

SELECT Students.id,students.name,majors.name as Majornamefrom Students right JOIN majorson Students.majorid = majors.id

Query Result:

Id	Name	Majorname
101	Tom	中文版
Null	Null	Computer

As you can see from the results, the complete collection of the second table majors is included, and computer does not have a match in the students table, it is replaced with null.

3) Full join (fully OUTER join, all-out connection)

Full outer connection, abbreviation: Full connection. Displays all rows from two tables in the results table

1) Use the full connection to query the student's information, including student ID, student name and professional name.

SELECT Students.id,students.name,majors.name as Majornamefrom Students full JOIN majorson Students.majorid = majors.id

Query Result:

Id	Name	Majorname
101	Tom	中文版
102	Lucy	Null
Null	Null	Computer

Contains all the records for both tables, no records are lost, and no matching rows are replaced with null.

4. Cross join (crossover connection)

Cross Connect. The cross join returns all the rows in the left table, and each row in the left table is combined with all the rows in the right table. Cross joins are also called Cartesian product.

Simple query Two table combination, this is to seek Cartesian product, the lowest efficiency.

Cartesian product: Cartesian product, also known as direct product. Assuming collection a={a,b}, set b={0,1,2}, the Cartesian product of two sets is {(a,0), (a,1), (a,2), (b,0), (b,1), (b,2)}. Can be extended to multiple collections of cases. Similarly, if a represents a collection of students in a school and b represents a collection of all courses in the school, the Cartesian product of A and B represents all possible elective courses.

1) Cross-connect to query the student's information, including student ID, student name and professional name.

SELECT Students.id,students.name,majors.name as Majornamefrom Students cross JOIN Majors

Query Result:

id	Name	majorname
101	Tom	中文版
102	Lucy	中文版
101	Tom	computer< /p>
102	Lu Cy	computer

2) query multiple tables, in fact, is a Cartesian product, and cross join equivalent, the following query is the same as the above results.

This may be common, but it is important to note that you are querying the complete set of all the combinations in both tables.

SELECT Students.id,students.name,majors.name as Majornamefrom students,majors

3) query conditions are added

Note: When using the CROSS join keyword to intersect a table, because a Cartesian product of two tables is generated, you cannot use the ON keyword, and you can define search conditions only in the WHERE clause.

SELECT Students.id,students.name,majors.name as Majornamefrom Students cross JOIN majorswhere Students.majorid = Majors.id

Query Result:

Id	Name	Majorname
101	Tom	中文版

The query results are the same as inner joins, but their efficiency is much slower.

5. All Instance Scripts

Create DATABASE testdbuse TestDB--------------------------------------------Creating related Tables If object_id (' Students ', ' U ') is not Nulldrop Table students--Student Information Form CREATE TABLE Students (ID int primary key not null,name nvarchar (a), age int,city nvarchar (50 ), Majorid Int)--Professional information table if OBJECT_ID (' majors ', ' U ') is not nulldrop table majorscreate table majors (ID int primary key not nul L,name nvarchar (50))--Teacher Information sheet if object_id (' Teachers ', ' U ') is not nulldrop table Teacherscreate table Teachers (ID int pri Mary key is not null, Name nvarchar (a) NOT null)--preset data delete from Studentsinsert to Students (Id,name,age,city,majorid) VALUES (101, ' Tom ', ' Beijing ', ' ten ') INSERT into Students (Id,name,age,city,majorid) VALUES (102, ' Lucy ', ' Shanghai ', DELETE from Majorsinsert to majors (id,name) VALUES (' 中文版 ') INSERT into majors (id,name) VALUES (' computer ') DELETE from Teachersinsert to Teachers (id,name) VALUES (101, ' Mrs Lee ') INSERT into Teachers (id,name) VALUES (102, ' Lucy ') SELECT Id,name from Studentsunionselect Id,namE from Teachersselect id,name to Studentsunion allselect id,name from teachers--connect select Students.id,students.name, Majors.name as Majornamefrom Students INNER join Majorson Students.majorid = majors.id--Left connection Select Students.id, Students.name,majors.name as Majornamefrom Students left join Majorson Students.majorid = majors.id--Right Connection Select Students.id,students.name,majors.name as Majornamefrom Students left JOIN majorson Students.majorid = majors.id--fully connected Select Students.id,students.name,majors.name as Majornamefrom Students full join Majorson Students.majorid = majors.id--Cross Connect select Students.id,students.name,majors.name as Majornamefrom Students crosses JOIN majors--Cross Connect Select Students.id,students.name,majors.name as Majornamefrom Students crosses JOIN majorswhere Students.majorid = majors.id--Query Multiple table Select Students.id,students.name,majors.name as Majornamefrom Students,Majors

　　

SQL Summary (ii) List query