Sql connected table query
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
