SQL connection (internal connection/external connection/Cross Connection)

SQL join

Table titles (Book info table) as shown in

If you have a table Publishers (publisher ),

Run the left Outer Join code:

Select titles. titles_id, titles. Title, publishers. pub_name from titles left Outer Join publishers on titles. pub_id = publishers. pub_id result is as follows:

Do you understand? Now I want to talk about the theory of left Outer Join! Don't go to bed!

 

I. Join)

Through join, you can retrieve data from two or more tables based on the logical relationship between tables. Join indicates how to use data in one table to select rows in another table.

The join condition defines the join mode of two tables in the query using the following method:

Specify the columns to be joined in each table. A typical join condition specifies a foreign key in one table and a key associated with it in another table. Specifies the logical operators (=, <>, and so on) used to compare the values of each column ).

 

Ii. Connection Type

When a table is joined, the created join type affects the rows in the result set. Join type:

Iii. Inner join

An inner join is also called a natural join. It is a common method for combining two tables. The natural connection compares the columns in the two tables and combines the rows that meet the connection conditions in the two tables as a result. There are two forms of natural concatenation syntax.

Syntax 1:

Select column from table 1 [insert] jion table 2 on table 1. Column = TABLE 2. Column

Syntax 2:

Select column from table 1, Table 2 where Table 1. Column = TABLE 2. Column

[Example] query the book number, title, author number, and author name in the titles, authors, and titleauthor tables.

Select titles. title_id, title, authors. au_id, au_lname from titles join titleauthor on titles. title_id = titleauthor. title_id join authors on authors. au_id = titleauthor. au_id

 

4. Outer Join)

In a natural join, only matching rows in two tables can appear in the result set. In Outer Join, only one table can be restricted, but other tables are not limited (that is, all rows appear in the result set ).

External connections include left outer connections, right outer connections, and full outer connections. The left Outer Join Operation does not limit the table on the left of the connection condition. The right outer join operation does not limit the table on the right. The full outer join operation does not limit both tables, all rows in the two tables are included in the result set.

 

V. Outer Join syntax

Syntax for left Outer Join: Select column from table 1 left [outer] Join table 2 on table 1. Column 1 = TABLE 2. Column 2

The right Outer Join syntax is: Select select_list from table 1 right [outer] Join table 2 on table 1. Column 1 = TABLE 2. Column 2

The syntax of the full outer join (full outer join) is: Select select_list from table 1 full [outer] Join table 2 on table 1. Column 1 = TABLE 2. Column 2

 

6. left Outer Join

Includes all rows in the first named table (left table, which appears on the far left of the join clause. Does not include unmatched rows in the right table.

[Example 35] the following SQL statement shows that the left Outer Join between the titles table and the publishers table includes all the titles, and even the titles without Publisher Information: Use pubs select titles. title_id, titles. title, publishers. pub_name from titles left Outer Join publishers on titles. pub_id = publishers. pub_id

 

 

 

7. Right Outer Join

Includes all rows in the second named table ("right" table that appears on the rightmost side of the join clause. Does not include unmatched rows in the left table.

[Example 36] the right outer join between the titles and publishers tables includes all publishers, even those who have no titles in the titles table.

Select titles. title_id, titles. Title, publishers. pub_name from titles right Outer Join publishers on titles. pub_id = publishers. pub_id

 

 

 

 

8,Complete External Connection

Includes all rows in all join tables, whether or not they match.

[Example 37] The Complete External Connection Between the titles table and the publishers table shows all the titles and publishers, and even the names and publishers of those tables that do not have matching values.

Select titles. title_id, titles. Title, publishers. pub_name from titles full outer join publishers on titles. pub_id = publishers. pub_id

 

 

 

IX. Cross join

A cross join without a where clause will generate the Cartesian product of the table involved in the join. The number of rows in the first table multiplied by the number of rows in the second table is equal to the size of the Cartesian result set.

[Example 39] Read the following procedure:

Use pubs

Select au_fname, au_lname, pub_name from authors cross join publishers order by au_lname DESC

Note: The result set contains 184 rows (authors has 23 rows, publishers has 8 rows, and 23 multiplied by 8 equals to 184 ). However, if you add a where clause, the cross join function is the same as that of the inner join.

 

 

Example: (Oracle)

Select
A. kaisen_no, -- return the nickname
Decode (B. kaisen_kbn, 1, 'fix', 2, 'mobilization', ''), -- differentiate callback
Decode (B. chaku_sen_kbn, 0, 'normal', 1, 'hangzhou', ''), -- tell
A. keihi_busho_cd, -- deployment code
D. busho_mei, -- deployment name of the worker
A. riyo_busho_cd, -- use the deployment code
E. busho_mei, -- use the deployment name
C. riyosha_cd, -- User CD
C. riyosha_mei, -- caller name
B. mikaiyaku_riyu -- reason for unagreement

From
T_uchiwake_meisai
Left Outer Join m_tel_number B on A. kaisen_no = B. kaisen_no -- return response packet loss
Left Outer Join m_riyosha C on A. riyosha_naibu_no = C. riyosha_naibu_cd -- the caller has already created
Left Outer Join m_busho D on A. keihi_busho_cd = D. busho_cd -- deploy zookeeper
Left Outer Join m_busho e on A. riyo_busho_cd = E. busho_cd -- deploy zookeeper

Where
A. seikyu_kingaku! = 0 and
A. waribiki_mae_ryokin = 0 and
A. hosei_go_seikyu_kingaku = 0 and
Substr (to_char (A. riyo_nen_tsuki), 1, 4) = 'Please Year' and
Substr (to_char (A. riyo_nen_tsuki), 5, 2) = 'Please month' and
B. kaisen_kbn = 'callback species 'and
B. chaku_sen_kbn = 'callback differentiation'

Order
A. kaisen_no