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

Type of table join:

1. Inner join: the most commonly used join method, which returns only rows that match between two data sets, joining rows within the overlap of two intersecting data sets.

  example: use inner joins in tables An and B to query student names, schools, and occupations.

Implementation method 1:

  select A. Name, A. School, B. Professional from A Magi B where A. Name = B. Name

  meaning: the results show the name and school of Table A, the occupation of Table B, and the query condition is that the name of Table An is equal to the name of Table B.

The second way of implementation:

  select A. Name, A. School, B. Professional from An inner join B on A. Name = B. Name

  meaning: table An is connected to Table B. the condition is that the name of Table An is equal to the name of Table B.

  note: the result of the inner join only shows the rows of names shared by Table An and Table B, and discards with different names.

two。 External connection: is an extension of the internal connection, showing mismatched data.

1)。 Left outer join (left join or left outer join): the result retains all rows of the left table. If a row of the left table does not match in the right table, a null value will be returned for the right table, otherwise the corresponding value will be returned.

  example: use the left outer join in tables An and B to query the student's name, school and occupation.

  select A. Name, A. School, B. Name, B. Professional from A left join B on A. Name = B. Name

  meaning: the result shows the number of all name rows in table An on the left. If there are no matching names in table B, the name and occupation are shown as null values (null).

2)。 Right outer join (right join or right outer join): contrary to the left outer join, the result returns all rows of the right table. If a row of the right table does not match in the left table, a null value will be returned for the left table, otherwise the corresponding value will be returned.

  example: use the right outer connection in tables An and B to query the student's name, school and occupation.

  select A. Name, A. School, B. Name, B. Professional from A right join B on A. Name = B. Name

  meaning: the result shows the number of all name rows in table B on the right. If there is no matching name in table A, the name and school will be shown as a null value (null).

3)。 Full join / full outer join (full join or full outer join): the result returns all rows of the left and right tables. When one row does not have a matching row in another table, the return value of the other table is null, otherwise the corresponding value is returned.

  example: use the right outer connection in tables An and B to query the student's name, school and occupation.

  select A. Name, A. School, B. Name, B. Professional from A full join B on A. Name = B. Name

  meaning: the result shows the number of all name rows in tables An and B. if the name in table A does not match the name in table B, the name and school of An are shown as null values (null). If the name in table B does not match the name in table A, the name and occupation of table B are displayed as a null value (null).

3. Cross join: there is no association between tables, and the result of the query is to return a join combination of each row of the left table and each row of the right table, which is equivalent to multiplication. The application of the occasion is less, as an understanding.

  example: use cross-joins in tables An and B to query student names, schools, and occupations.

  select A. Name, A. School, B. Name, B. Professional from A full join B

  meaning: each row in table An is combined with each row in table B to form a new row, and the total number of rows is equal to the number of rows of A multiplied by the number of rows of B.

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.