Shulou(Shulou.com)06/03 Report--

1.Union: use union to combine two tables and eliminate duplicate rows in the table. The query results of the two tables have the same number of columns and column types are similar; UNION ALL, do not eliminate duplicate rows

Teacher schedule:

IDName101Mrs Lee102Lucy

Student form:

IDNameAgeCityMajorID101Tom20BeiJing10102Lucy18ShangHai11

SELECT Name FROM Students

UNION ALL

SELECT Name FROM Teachers

The result is:

IDName101Tom102Lucy101Mrs Lee102Lucy

2.INNER JOIN (internal connection): internal connection, check only matching lines

Majors table:

IDName10English12Computer

Example: query student information, including ID, name, major name

SELECT Students.ID,Students.Name,Majors.Name AS MajorName

FROM Students INNER JOIN Majors

ON Students.MajorID = Majors.ID

Query results:

IDNameMajorName101TomEnglish

3. External connection: left outer connection, right outer connection and full external connection, corresponding to LEFT/RIGHT/FULL OUTER JOIN

Important: at least one party retains the complete set, and no matching lines are replaced by NULL

1) LEFT OUTER JOIN: the result set retains all rows of the left table, but contains only the rows of the second table that match the first table. The corresponding blank row of the second table is put into the null value

SELECT Students.ID,Students.Name,Majors.Name AS MajorName

FROM Students LEFT JOIN Majors

ON Students.MajorID = Majors.ID

Results:

IDNameMajorName101TomEnglish102LucyNULL

2) RIGHT OUTER JOIN: the right outer join retains all the rows of the second table, but contains only the rows that match the second table. The corresponding blank row of the first table is entered into the NULL value

SELECT Students.ID,Students.Name,Majors.Name AS MajorName

FROM Students RIGHT JOIN Majors

ON Students.MajorID = Majors.ID

Results:

IDNameMajorName101TomEnglishNullNULLComputer

3) FULL OUTER JOIN: displays all rows of the two tables in the result table

SELECT Students.ID,Students.Name,Majors.Name AS MajorName

FROM Students FULL JOIN Majors

ON Students.MajorID = Majors.ID

Results:

IDNameMajorName101TomEnglish102LucyNULLNULLNULLComputer

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.