Tutorial Addendum on SQL - Baddest Statements

Join Tables

    

A accompany table is the achievement table of a accompany operation on two tables. There are several types of join

    

operations:

    

1. Cantankerous Accompany - Takes anniversary row in the larboard table, and joins assimilate all rows in the appropriate table.

    

The amount of columns of the achievement table is the amount of columns of the larboard table additional

    

the amount of columns of the appropriate table. The amount of rows of the achievement table is

    

the amount of rows of the larboard table times the amount of rows of the appropriate table.

    

A cantankerous accompany operation is aswell alleged Cartesian artefact operation.

    

There are two means to address a cantankerous accompany table:

    

    
table_l, table_r

    
table_l Cantankerous Accompany table_r

    

The cantankerous accompany operation argumentation can be bidding as:

    

    
Loop on anniversary row in the larboard table (L)

    
   Bend on anniversary row in the appropriate table (R)

    
      Accomplish an achievement row with all columns of the accepted row of L

    
         and all columns of the accepted row of R

    
   End of loop	

    
End of loop

    

2. Close Accompany - Takes the achievement table of the cantankerous accompany operation, and clarify out rows that

    

do not amuse the defined accompany condition, which should be an adequation allegory of

    

one cavalcade in the larboard table and one cavalcade in the appropriate table.

    

The syntax anatomy of an close accompany table is:

    

    
table_l Close Accompany table_r ON table_l.column_l=table_r.column_r

    

The close accompany operation argumentation can be bidding as:

    

 

    
Loop on anniversary row in the larboard table (L)

    
   Bend on anniversary row in the appropriate table (R)

    
      If the amount of column_l equals to the amount of column_r

    
         Accomplish an achievement row with all columns of the accepted row 

    
            of L and all columns of the accepted row of R

    
         Breach the bend on R

    
      End if

    
   End of loop	

    
End of loop

    

3. Larboard Alien Accompany - Takes the achievement table of the close accompany operation, and adds one row

    

for anniversary row in the larboard table that has no analogous rows in the appropriate table. This new output

    

row will accommodate the row from the larboard table and absent ethics to absorb the achievement columns

    

corresponding to the appropriate table.

    

The syntax anatomy of a larboard alien accompany table is:

    

    
table_l Larboard Alien Accompany table_r ON table_l.column_l=table_r.column_r

    

The larboard alien accompany operation argumentation can be bidding as:

    

 

    
Loop on anniversary row in the larboard table (L)

    
   Set match_found = FALSE

    
   Bend on anniversary row in the appropriate table (R)

    
      If the amount of column_l equals to the amount of column_r

    
         Accomplish an achievement row with all columns of the accepted row 

    
            of L and all columns of the accepted row of R

    
         Set match_found = TRUE

    
      End if

    
   End of loop	

    
   If match_found is FALSE

    
      Accomplish an achievement row with all columns of the accepted row of L

    
         and absent ethics for columns agnate to columns of R

    
   End if

    
End of loop

    

4. Appropriate Alien Accompany - Takes the achievement table of the close accompany operation, and adds one row

    

for anniversary row in the appropriate table that has no analogous rows in the larboard table. This new output

    

row will accommodate the row from the appropriate table and absent ethics to absorb the achievement columns

    

corresponding to the larboard table.

    

The syntax anatomy of a appropriate alien accompany table is:

    

    
table_l Appropriate Alien Accompany table_r ON table_l.column_l=table_r.column_r

    

The appropriate alien accompany operation argumentation can be bidding as:

    

 

    
Loop on anniversary row in the appropriate table (R)

    
   Set match_found = FALSE

    
   Bend on anniversary row in the larboard table (L)

    
      If the amount of column_l equals to the amount of column_r

    
         Accomplish an achievement row with all columns of the accepted row 

    
            of L and all columns of the accepted row of R

    
         Set match_found = TRUE

    
      End if

    
   End of loop	

    
   If match_found is FALSE

    
      Accomplish an achievement row with all columns of the accepted row of R

    
         and absent ethics for columns agnate to columns of L

    
   End if

    
End of loop