Database series queries (5)

Inner Connection 1 syntax

SELECT < column name >
From table 1
INNER JOIN table 2 on table 1. Column Name condition operator table 2. Column Name
[WHERE condition]
[ORDER by row sequence]
where "on table 1. Column Name condition operator table 2. Column Name", the conditional operator is commonly used: =, <>.
Table 1. Column names and table 2. Column names, respectively, are the public columns of the two tables.
An inner join query is a record that is common to the public columns of two tables.

2 Example

(1) Books and Authors connection SQL statements

SELECT  as  as  as Author's name  from  as b INNER JOIN  as  on = A.authorid

(2) Inner connection Another way of writing

SELECT  as  as  as Author's name  from Books B,  Authors aWHERE= A.authorid

3 Multiple in-table connection syntax

SELECT < column name >
From table 1
INNER JOIN table 2 on table 1. Column Name condition operator table 2. Column Name
INNER JOIN table 3 on table 1. Column Name condition operator table 3. Column Name
......
[WHERE condition]
[ORDER by row sequence]

4 Example

SELECT  as  as Publisher name,                 as  as Author's name  from  INNER JOIN   on B.authorid=INNERJOIN on B.publisherid= C.publisherid

Outer JOIN LEFT OUTER JOIN syntax

SELECT < column name >
From left table
Left [OUTER] JOIN right table on the right table. The column name condition operator is in the table. Column Name
[WHERE condition]
[ORDER by row sequence]

Example

Querying data in books and authors tables using a LEFT OUTER join

SELECT  as  as  as Author's name  from  leftJOINon  b.authorid=A.authorid

The left outer join is the left table table, which relates to the right table (from the table), the result set contains all the data rows of the main table, and if a row of the primary table does not have a matching row from the table, the selection from the table is listed as a null value.

Right outer join syntax

SELECT < column name >
From the left table right [OUTER] JOIN to table
On the left table. Column name condition operator right table. Column Name
[WHERE condition]
[ORDER by row sequence]

Example

Querying data in books and authors tables using a right outer join
SELECT B.bookcode as book number, b.bookname as book name, A.authorname as author name
From Books b
Right JOIN Authors A on B.authorid=a.authorid
The right outer join is the right table, which relates to the left table (from the table), the result set contains all the data rows of the main table, and if a row of the primary table does not have a matching row from the table, the selection from the table is listed as a null value.

Full connection syntax

SELECT < column name >
From left table
Full [OUTER] JOIN right table on left table. Column name condition operator right table. Column Name
[WHERE condition]
[ORDER by row sequence]
All rows in the left and right tables are fully connected, and when a row of data does not match in another table, the selection column value of the other table is null

Example

Querying data in books and authors tables using a full connection

SELECT  as  as  as Author's name  from  fullJOINon  b.authorid=A.authorid

Cross Join

Concept: A cross join without a WHERE clause will result in the number of rows in the first table of the Cartesian product involved in the connection, multiplied by the number of rows in the second table equal to the Cartesian product and the size of the result set

Cross-Joins: crosses join (without a condition where if the band Returns or shows the number of matching rows)

SQL syntax: SELECT * from Books Cross join Books

If there is a condition (where)Select*from crossjoinwhere Books.authorid=  authors.authorid equivalent to select*from-- (without where)

Database series queries (5)