Unlike in relational algebra “ Connect ” It is expressed by a special symbol , stay SQL in “ Connect ” Yes, it is “ Connection condition ” To express the .

Join condition or join predicate ： The condition used to join two tables  

General format ：

[< Table name 1>.]< Name 1>< Comparison operator >[< Table name 2>.]< Name 2>

Join fields ： Column names in join predicates

The connection field types in a join condition must be comparable , But the names don’t have to be the same

1、 Equivalent and non equivalent join queries

Equivalent connection ： concatenation operator ：“=”

Used in select How to get rid of duplicate fields

The execution of the join operation

   （1） Nested loop method （NESTED-LOOP）

           a. First of all, in the table 1 Find the first tuple in , And then scan the table from scratch 2, Find the tuples that satisfy the connector one by one , When you find it, you’ll find it 1 The first tuple in is concatenated with the tuple , Form a tuple in the result table .

           b. surface 2 When you’re done searching all of them , Look for the watch again 1 The second tuple in , And then scan the table from scratch 2, Find the tuples that satisfy the join condition one by one , When you find it, you’ll find it 1 The second tuple in is concatenated with the tuple , Form a tuple in the result table .

            c. Repeat the above operation , Until the watch 1 All tuples in are processed

   （2） Sort and merge （SORT-MERGE）

            It is often used in equivalent connection

          a. First, the table is aligned according to the connection properties 1 And table 2 Sort

          b. Set the pointer , Point to the table respectively 1 And table 2 The first tuple of . If the two tuples satisfy the join condition , Then concatenate tuples （ If more than one consecutive tuple satisfies the join condition , We need to splice them one by one ）, And move the two pointers backward by one principle . otherwise , Move the pointer with a smaller value back one tuple .

          c. Repeat the above operation , Until the watch 1 Or table 2 Until all tuples in are processed

     （3） Index join （INDEX-JOIN）

           a. Counter table 2 Index by linked fields

           b. Counter table 1 Each tuple in , Query the table according to its connected field values in turn 2 The index of , Find the tuple that satisfies the condition , When you find it, you’ll find it 1 The first tuple in is concatenated with the tuple , Form a tuple in the result table

           c.（ This method can also be regarded as a variant of nested loop method ）

2、 Self connect

A table joins itself , It’s a special connection

You need to alias the table to show the difference

Because all attribute names have the same name , So you have to use the alias prefix

3、 External connection

The difference between an external connection and a normal connection

1. The normal join operation only outputs tuples that satisfy the join condition
2. The outer join operation takes the specified table as the join body , Output all tuples in the body table that do not meet the join condition
3. The left outer join

             List all tuples in the left relation

     4. Right connection

            List all tuples in the right-hand relationship

4、 Multiple table joins

         Join two or more tables

thank you ！