Database-joined knowledge points, easy-to-wrong points

Joins are divided into:

Left join: Returns records that include all records in the left table and the equivalent of the junction fields in the right table. 3

Right join: Returns records that include all records in the right table and the junction fields in the left table. 4

INNER JOIN (equivalent join): Returns only rows that are equal to the junction fields in two tables.

We mainly use left joins.

Example:

1. In the college table, the professional table, inquiries to open 4-year professional school and its professional, Schools (departments) which do not meet the criteria and have not set up a profession are also listed SELECT D.name as Department , M.name as Major From Tb_department as D Left JOIN Tb_major as M on m.departmentno=d.no WHERE

m.length=4; The error in the example is that the outer row retained in the left join is deleted by the WHERE clause. Modified to:

SELECT

D.name as Department

, M.name as Major

From

Tb_department as D

Left joins Tb_major as M on m.departmentno=d.no and m.length=4;

2. In the College (Department) table, the professional table, the class table, inquires all the schools (departments) under all classes, if a college (department) No class, also listed

SELECT

D.name as Department

, Right (CONVERT (Varchar,c.year), 2) +m.shortname+isnull (C.administrationclass, ") as Class

From

Tb_department as D

Left JOIN Tb_major as M on D.no=m.departmentno

JOIN Tb_class as C on M.no=c.majorno;

The error in the example is that the left join can leave an unmatched outer row for the left table, but the property values for the right-hand table in the outer row are NULL, and the null values are never found in the subsequent joins, so the outer rows cannot be persisted.

Modified to:

SELECT

D.name as Department

, Right (CONVERT (Varchar,c.year), 2) +m.shortname+isnull (C.administrationclass, ") as Class

From

Tb_department as D

Left JOIN

(Tb_major as M

JOIN Tb_class as C on M.no=c.majorno) on D.no=m.departmentno;

Database-joined knowledge points, easy-to-wrong points