SQL various connections-self-connection, internal connection, external connection, use of cross-connect

Two tables (Student and Course) are prepared first, where the c_s_id field in the Student table is the foreign key column associated with the Course primary key column of the c_id table.

INNER JOIN (inner join): satisfies the on conditional expression, which is the intersection of the two tables that satisfy the conditional expression (that is, the data in two tables).

1 Select *  from Student S 2 Inner Join  on s.c_s_id=c.c_id

The outer join (outer join) is divided into : Left outer join (Join/left outer join), right outer join (Starboard join/right outer join) and full outer join Full outer join)

left Join/left outer join: satisfies the on conditional expression, the left outer join is based on left table, returns all data from the left table, matches the right table with a value, and no match is replaced with null (NULL).

1 Select *  from Student S 2  Left Join  on s.c_s_id=c.c_id

right outer join (Join/right outer join): satisfies the on conditional expression, the right outer join is the right table, the right table to return all data, and the left table matches the value, there is no match is empty (null) replaced.

1 Select *  from Student S 2  Right Join  on s.c_s_id=c.c_id

full join/full outer join: satisfies the on condition expression, returns all rows of two tables that match the criteria, the table A is not matched then the column of table a returns the Null,b table does not match the column of table B returns null, That is, the set of left and right joins are returned.

1 Select *  from Student S 2  Full Join  on s.c_s_id=c.c_id

Cross Join: The cross joins will return the Cartesian product of the two connected tables, and the number of rows returned is equal to the product of two table rows.

Returns the product of two table row numbers without conditions:

1 Select *  from Student S 2  Cross Join

Add a condition that returns the rows of the two tables that meet the conditional expression:

1 Select *  from Student S 2  Cross Join  3where s.c_s_id=c.c_id

Ps:cross join conditions can only be used where, not on, which is the same as the subsequent self-join.

self-connect : self-connected, connected two tables are the same table, that is, themselves connected to themselves.

1 --find out more about boys taller than girls2 SelectS1. S_name,s1. S_sex,s1. S_height,s2. S_name,s2. S_sex,s2. S_height3  fromStudent S1,4 Student S25 whereS1. S_birthdate=S2. S_birthdate6  andS1. S_height<S2. S_height7  andS1. S_sex='male' 8  andS2. S_sex='female'

1 -- find students with the same height but not the same student information 2 Select *  from Student s1,student S2 3 where s1. S_height=s2. S_height4 and S1. S_stuno<>S2. S_stuno

Because of the table's data, the self-connected example may not be easy to understand. You can look at the following example to query the table's primary foreign key constraint name based on the table name, as shown in the following example:

1 SelectA.name asTable name, B.xtype asKey type, B.name asKey Name2  fromsysobjects a,sysobjects b3 wherea.ID=B.parent_obj andA.name='Student'4  andB.xtypeinch('F','PK')

If the connection condition is not specified, it is the same as the cross connection of the Cartesian product, but unlike the Cartesian product, the data table of the product of the number of rows of the Cartesian product is not so complicated that the inner connection is more efficient than the cross-connection of the Cartesian product.

Finally, the cross join, if there is a WHERE clause, will tend to be the data table of the row that is the product of the two table row numbers and then select from the Where condition.

Therefore, if the data volume of two tables is too large, it will be very, very slow and not recommended.

SQL various connections-self-connection, internal connection, external connection, use of cross-connect